如何在ActiveRecord中编写这个查询？

Question

假设我有一个 ActiveRecord::Base 子类 User 和表 users，我不确定如何在 ActiveRecord 中编写此查询语句：

SELECT *
FROM (
    SELECT users.* 
    FROM   follows
    INNER JOIN users ON users.id = follows.following_id
    WHERE  username LIKE 'r%' AND follows.follower_id = 5717
    LIMIT 10

    UNION

    SELECT * 
    FROM   users 
    WHERE  username LIKE 'r%' 
    LIMIT 10
) AS users
LIMIT 10

我正在从一个自定义生成的表中选择。我该如何开始编写这个查询？这是否可能？如果是，怎么做？如果不行，我的替代方案是什么？

谢谢！

- 0xSina

---

你想要什么结果？在你的查询中，你将返回所有用户名以'r%'开头的用户，因为第一部分查询的结果也会出现在第二部分查询中。由于从查询中看不清楚你想要返回什么结果，所以你需要解释一下。 - Tom

只是想指出，查询结果集在那里是不确定的，除非您添加ORDER BY子句来补充每个LIMIT子句。 - David Aldridge

Answer 1

谢谢，这是一个有趣的问题。

基于该联合实现，我认为这应该可以解决问题：

# first sub query, inversed compared to your's, so that we get user fields
followed = User.group( 'users.id, users.username, follows.follower_id' ).having( "users.username like 'r%' and follows.follower_id = 5717" ).joins( :follows )

# second sub query
user = User.where( "username like 'r%'" )

# joining both using union
related_people_query = [ followed, user ].map { |query| query.select( 'users.id' ).to_sql }.join( ' UNION ' )
related_people = User.where( "users.id IN (#{related_people_query)" ).limit(10)

当然，那是一个很大的查询，我无法测试它，所以我想知道它是否适用于您。

编辑：我忘记在第一个子查询上加上#joins

编辑2：忘记了限制

- kik

---

谢谢，我一到我的开发环境，就会测试并让你知道。 - 0xSina

Answer 2

看起来你只想获取一个用户列表，其中username like '%r'（必须），并且follows.follower_id = 5717（优先）。如果是这种情况，请尝试以下操作（为了易读性而拆分；未经测试）：

User
  .joins("LEFT OUTER JOIN follows ON users.id = follows.following_id")
  .where("username like 'r%'")
  .order('follows.follower_id = 5717 desc, rand()')
  .limit(10)
  .uniq

- Jacob Brown

---

谢谢，但这是我之前使用的，速度非常慢。 - 0xSina

Answer 3

如果用户名以r开头的用户只是一小部分记录，我会使用急切加载。

users = User
        # get all users with matching username and all the associated follows
       .where("username LIKE 'r%' ").includes(:follows)
        # arrange the records in ruby  to get users with given follower id first
       .sort{|x|  x.follows.map(&:follower_id).include?(5717) ? 0 : 1 }
        # return first 10 records
       .first(10)

否则我会采用交错的方法

# try to get 10 records with matching username and follower_id using an inner join
users  = User.joins(:follows).where("username LIKE 'r%' AND follows.follower_id = ?", 5717).group("users.id").limit(10)

# if less than 10 records found, run second query to get users with matching records only
users += User.where(" username LIKE 'r%' AND id NOT IN (?)", users.map(&:id)).limit(10-user.count) if users.count < 10

以上两种方法都会生成2个SQL查询。使用单个查询的选项是进行外连接，这似乎对您的情况来说速度较慢。

- tihom

Answer 4

我认为你可以使用find_by_sql来实现这样的复杂查询

User.find_by_sql("SELECT * FROM (SELECT users.* FROM   follows INNER JOIN users ON users.id = follows.following_id WHERE  username LIKE 'r%' AND follows.follower_id = 5717 LIMIT 10 UNION SELECT * FROM   users WHERE  username LIKE 'r%' LIMIT 10) AS users LIMIT 10")

- Vineeth Pradhan

---

但是抱歉，我还没有测试过。我现在的系统中没有安装mysql和rails。请自行解决 :) - Vineeth Pradhan