高效地遍历所有用户并返回3个关联项[SQL][Rails]

Question

我正在运行轨道4。我们有一个User模型has_manyPosts。我想创建一个"提要"，最多显示网站中每个用户的三个帖子。

我能得到的最接近的东西

  # GET /users
  # GET /users.json
  def index
    users = User.includes(:posts)
    users.each do |user|
       @feed_items << user.posts.limit(3)
    end
    @comment = Comment.new
  end

我的视图会显示@feed_items中的所有项，但rails不允许连接查询？如果不进行n+1查询，我如何在Rails中做到这一点？我需要自定义sql查询吗？

参考：http://guides.rubyonrails.org/active_record_querying.html#eager-加载关联

Answer 1

includes已经在一个查询中读取了每个用户的所有帖子。因此，您应该能够在ruby中获得前三个，而无需再次查询数据库。

users = User.includes(:posts)
@feed_items = []
users.each do |user|
   @feed_items += user.posts.first(3)
end

这形成了一长串帖子。如果需要按用户列表的列表，请使用<<而不是+=。

我能够测试这个。以下是生成的查询：

User Load (4.4ms)  SELECT "users".* FROM "users" 
Post Load (2.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (4, 5, 6, 7, 8, 9, 10, 11, 12, 13)

结果是：

irb(main):037:0> @feed_items.map &:subject
=> ["User 0's Post 1", "User 0's Post 2", "User 0's Post 3", "User 1's Post 1", 
"User 1's Post 2", "User 1's Post 3", "User 2's Post 1", "User 2's Post 2", 
"User 2's Post 3", "User 3's Post 1", "User 3's Post 2", "User 3's Post 3", 
"User 4's Post 1", "User 4's Post 2", "User 4's Post 3", "User 5's Post 1", 
"User 5's Post 2", "User 5's Post 3", "User 6's Post 1", "User 6's Post 2", 
"User 6's Post 3", "User 7's Post 1", "User 7's Post 2", "User 7's Post 3", 
"User 8's Post 1", "User 8's Post 2", "User 8's Post 3", "User 9's Post 1", 
"User 9's Post 2", "User 9's Post 3"]

includes的缺点是总是，您最终可能会检索不需要的数据。在这里，如果用户有3个以上的帖子，则检索所有帖子并丢弃除3个之外的所有帖子可能比每个用户一个查询更昂贵。

服务器端解决方案需要高级SQL。您可以搜索"组内sql限制"来查看一些建议。