Sorting by properties of a has_many association

Question

Suppose Songs have_many Comments; How can I:

1. Pull a list of all songs from the database sorted by the number of comments they each have? (I.e., the song with the most comments first, the song with the least comments last?)
2. Same, but sorted by comment creation time? (I.e., the song with the most recently created comment first, the song with the least recently created comment last?)

Answer 1

If you need to sort by number of comments they have - while you can do it directly using SQL - I strongly recommend you use counter_cache - See: http://railscasts.com/episodes/23-counter-cache-column

Ofter that, just set the order by option of find like so:

Song.all(:order => "comments_count DESC");

This is different in Rails 3 so it depends what you're using.

I would also recommend caching the latest comment created thing on the Song model to make your life easier.

You would do this with an after_save callback on the Comment model with something like:

self.song.update_attributes!({:last_comment_added_at => Time.now.to_s(:db)})

Answer 2

1) There are a couple of ways to do this, easiest would be counter cache, you do that my creating a column to maintain the count and rails will keep the count up to speed. the column in this case would be comments_count

songs = Song.all(:order => "comments_count DESC")

OR you could do a swanky query:

songs = Song.all(:joins => "LEFT JOIN comments ON songs.id = comments.song_id",
                 :select => "song.name, count(*)",
                 :group => "song.name",
                 :order => "count(*) DESC")

a few caveats with the second method, anything you want to select in the songs you will need to include in the group by statement. If you only need to pull songs with comments then you can:

songs = Song.all(:joins => :comments, 
                 :select => "song.name, count(*)",
                 :group => "song.name",
                 :order => "count(*) DESC")

Which looks nicer but because it does an inner join you would not get songs that had no comments

2) just an include/joins

songs = Song.all(:include => :comments, :order => "comment.created_at"

I hope this helps!