Ruby-on-Rails: how to extract the most recent entries from a limited subset of a database table

Question

Imagine something like a User model that has many friends, each of which has many comments, where I try to show the user the last 100 comments of his friends.

Is it possible to output the last 100 in a single SQL query, or will I have to use the Ruby application logic to parse a larger list or execute multiple queries?

I see two ways around this:

• starting with User.find and use the complex combination: join and: limit. This method seems promising, but, unfortunately, it will return users to me, not comments, and as soon as I return them, I will have many models occupying memory (for each Friend and User), many unnecessary fields are transferred (all for the user, and all about the name string for friends), and I still have to go through somehow to collect and sort all the comments in the application logic.
• starting with comments and using some kind of find_by_sql, but I just can't figure out what I will need to insert. I do not know how you could get the necessary information for this to limit it to viewing comments made by friends.

Change I had difficulty working with the EmFi solution, and I would appreciate any information that anyone can provide.

Friends represent a cyclic association through a connection table.

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

This is the error I get in the 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 request that 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 looks like it is distorting: in two: in one request.

Answer 1

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 areas for you that are not evaluated until the end of the chain.

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

If this is a general query, the find can be simplified to your area.

 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 

NB: The user friends association can be cycled through the connection table, but this is not important for this solution. As long as friends are a working user association, the previous one will work.