Ruby-on-Rails：如何从数据库表的有限子集中提取最新的条目 [英] Ruby-on-Rails: How to pull out most recent entries from a limited subset of a database table

问题描述

想象一下，像一个模型的用户有很多朋友，每个人都有很多评论，我试图向他的朋友展示最新的100条评论。

是否可以在单个SQL查询中绘制最新的100个，或者我将不得不使用Ruby应用程序逻辑来解析更大的列表或进行多个查询？

我看到了两种方法：

1. 从User.find开始，并使用一些复杂的组合：join和：限制。这种方法看起来很有希望，但不幸的是，将返回给我的用户，而不是评论，一旦我回来，我会有很多模型占用内存（为每个朋友和用户），很多不必要的领域被转移（一切对于用户来说，以及关于Friends的名字行的所有内容），而且我仍然需要通过某种方式来收集和排序应用程序逻辑中的所有注释。


2. 从评论和使用某种find_by_sql，但我似乎无法弄清楚我需要把什么。我不知道你怎么可以有必要的信息，通过与此限制它只看由朋友提出的评论。
   

编辑：我有些困难让EmFi的解决方案工作，并希望任何洞察任何人都可以提供。

朋友是通过连接表的循环关联。

<$ p $ has_many：友谊
has_many ：朋友，
：through => ：友谊，
：conditions => status =＃{Friendship :: FULL}


这是相关的错误部分：

错误：列users.user_id不存在

  （users.user_id = 1）AND（（status = 2）））$ b：SELECTcomments。* FROMcommentsINNER JOINusersONcomments.user_id =users.id WHERE $ b  

当我输入user.friends，它就起作用了，它就是执行的查询：

$ p $ ：SELECTusers。* FROMusersINNER JOINfriendshipsONusers.id =friendships.friend_id WHERE （friendships.user_id = 1）AND（（status = 2）））


就像它正在改变：通过在一个查询中有两个：通过。

解决方案

给出以下关系： b
$ b

  class User< ActiveRecord :: Base 
 has_many：friends 
 has_many：comments 
 $ b has_many：friends_comments，：through => ：friends，：source => ：comment 
 end 
  

这条语句将执行一条SQL语句。关联本质上是为你创建的命名范围，直到链的末尾才被评估。

  @ user.friends_comments.find（：limit => 100，：order =>'created_at DESC'）
  

如果这是一个常见的查询，find可以简化为它自己的范围。
$ b

  class评论< ActiveRecord :: Base 
 belongs_to：user 
 
 #named_scope在Rails 3.2中被重命名为范围。如果你正在工作
＃如果你在以前的版本工作取消注释下面的行。 
 #named_scope：recent，：limit => 100，：order => 'DESC创建'
范围：最近，：限制=> 100，：order => 'created_at DESC'
 end 
  

现在你可以这样做：

  @ user.friends_comments.recent 
  

注意：用户上的朋友关联可能是通过连接表循环的，但这对于这个解决方案并不重要。只要朋友是用户的工作协会，上述将工作。

Imagine something like a model User who has many Friends, each of who has many Comments, where I'm trying to display to the user the latest 100 comments by his friends.

Is it possible to draw out the latest 100 in a single SQL query, or am I going to have to use Ruby application logic to parse a bigger list or make multiple queries?

I see two ways of going about this:

1. starting at User.find and use some complex combination of :join and :limit. This method seems promising, but unfortunately, would return me users and not comments, and once I get those back, I'd have lots of models taking up memory (for each Friend and the User), lots of unnecessary fields being transferred (everything for the User, and everything about the name row for the Friends), and I'd still have to step through somehow to collect and sort all the comments in application logic.
2. starting at the Comments and using some sort of find_by_sql, but I just can't seem to figure out what I'd need to put in. I don't know how you could have the necessary information to pass in with this to limit it to only looking at comments made by friends.

Edit: I'm having some difficult getting EmFi's solution to work, and would appreciate any insight anyone can provide.

Friends are a cyclic association through a join table.

has_many :friendships
has_many :friends, 
         :through => :friendships,
         :conditions => "status = #{Friendship::FULL}"

This is the error I'm getting in relevant part:

ERROR: column users.user_id does not exist

: SELECT "comments".* FROM "comments"  INNER JOIN "users" ON "comments".user_id = "users".id    WHERE (("users".user_id = 1) AND ((status = 2)))

When I just enter user.friends, and it works, this is the query it executes:

: SELECT "users".* FROM "users" INNER JOIN "friendships" ON "users".id = "friendships".friend_id WHERE (("friendships".user_id = 1) AND ((status = 2)))

So it seems like it's mangling the :through to have two :through's in one query.

解决方案

Given the following relationships:

class User < ActiveRecord::Base
  has_many :friends           
  has_many :comments

  has_many :friends_comments, :through => :friends, :source => :comments
end

This statement will execute a single SQL statement. Associations essentially create named scopes for you that aren't evaluated until the end of the chain.

@user.friends_comments.find(:limit => 100, :order => 'created_at DESC')

If this is a common query, the find can be simplified into its own scope.

class Comments < ActiveRecord::Base
  belongs_to :user

  #named_scope was renamed to scope in Rails 3.2. If you're working
  #if you're working in a previous version uncomment the following line.
  #named_scope :recent, :limit => 100, : order => 'created at DESC'
  scope :recent, :limit => 100, :order => 'created_at DESC'
end

So now you can do:

@user.friends_comments.recent

N.B.: The friends association on user may be a cyclical one through a join table, but that's not important to this solution. As long as friends is a working association on User, the preceding will work.

这篇关于Ruby-on-Rails：如何从数据库表的有限子集中提取最新的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！