CODEWITHSUNDEEP
ruby-on-rails
Tom Lehman
Tom Lehman

Reputation: 89193

Sorting by properties of a has_many association

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?)

Upvotes: 2

Views: 443

Answers (2)

Geoff Lanotte
Geoff Lanotte

Reputation: 7500

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!

Upvotes: 6

Jamie Wong
Jamie Wong

Reputation: 18350

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)})

Upvotes: 1

Related Questions