Rails 3 ActiveRecord: Order by count on association

Question

I have a model named Song. I also have a model named Listen. A Listen belongs_to :song, and a song :has_many listens (can be listen to many times).

In my model I want to define a method self.top which should return the top 5 songs listened to the most. How can I achieve that using the has_many relation?

I'm using Rails 3.1.

Thanks!

score 96 · Accepted Answer · edited Jul 12 '13 at 13:31

96

Using named scopes:

class Song
  has_many :listens
  scope :top5,
    select("songs.id, OTHER_ATTRS_YOU_NEED, count(listens.id) AS listens_count").
    joins(:listens).
    group("songs.id").
    order("listens_count DESC").
    limit(5)

Song.top5 # top 5 most listened songs

edited Jul 12 '13 at 13:31

Dennis Hackethal

13,662
12
66
115

answered Jan 01 '12 at 22:44

clyfe

23,695
8
85
109

5

SUre, remove the limit statement from the scope definition, then in controller call like so: `Song.where(:user_id => current_user.id).top.limit(5)` – clyfe Jan 02 '12 at 14:12
11

Thank you. I had to add group("songs.id") between joins and order. :) – Christoffer Reijer Jan 02 '12 at 14:52
Since user_id is an attribute of listens and not songs, I ended up throwing it into a def and having a user parameter. Then I added this after joins(): where(user == nil ? "" : "listens.user_id = #{user.id}"). Thank you so much for your help. :D ` – Christoffer Reijer Jan 02 '12 at 14:57
`where(user.nil? ? '' : *["listens.user_id = ?", user.id])` is better because it escapes – clyfe Jan 02 '12 at 15:36
11

I guess you are using MySQL. In Postgres you need to aggregate all attributes: .group("listens.id,songs.#{Song.column_names.join(",songs.")}") – EricLarch Jan 12 '12 at 14:07
Excellent answer; is there a way to do this where you can receive the association results, AND the rest of the Songs? So that in an Index, for example, the songs with top listens are at the top, but songs without any listens are still listed? – rcd Mar 05 '13 at 19:31
@rcd just remove the `limit(5)` clause – clyfe Mar 06 '13 at 21:13
1

Is there a way to extend this scope by only counting the listens which were created in the last 24 hours? – Daniel May 23 '13 at 14:29
1

Ok, I guess I found a solution: `Work.select('works.name, count(impressions.id) AS impressions_count').joins(:impressions).where('impressions.created_at >= ?', Time.now - 1.day).group('works.name').order('impressions_count DESC')` Is this correct? – Daniel May 23 '13 at 14:39
@clyfe https://stackoverflow.com/questions/56669484/how-to-get-value-using-join-table-with-different-values/56680298#56680298 – Anshul Riyal Jun 20 '19 at 08:49

Neal · Answer 2 · 2015-07-24T18:58:41.877

33

Even better, use counter_cache which will be faster because you'll only because using one table in your query

Here is your song class:

class Song < ActiveRecord::Base
  has_many :listens

  def self.top
    order('listens_count DESC').limit(5)
  end
end

Then, your listen class:

class Listen < ActiveRecord::Base
  belongs_to :song, counter_cache: true
end

Make sure you add a migration:

add_column :comments, :likes_count, :integer, default: 0

Bonus points, add test:

describe '.top' do
  it 'shows most listened songs first' do
    song_one = create(:song)
    song_three = create(:song, listens_count: 3)
    song_two = create(:song, listens_count: 2)

    popular_songs = Song.top

    expect(popular_songs).to eq [song_three, song_two, song_one]
  end
end

Or, if you want to go with the above method, here it is a bit more simply, and using a class method rather than scope

def self.top
    select('comments.*, COUNT(listens.id) AS listens_count').
      joins(:listens).                                                   
      group('comments.id').
      order('listens_count DESC').
      limit(5)
end

edited Jul 24 '15 at 18:58

answered Mar 02 '14 at 04:00

Neal

4,468
36
33

Thanks for telling me about counter_cache. Haven't seen that before. :) But it won't work for me since I also need to only include listens which belong to a specific user, meaning I am stuck with some more complex queries. – Christoffer Reijer Mar 02 '14 at 17:43
are you forgetting a dot at the end of select or is it not needed? – lonewarrior556 Jun 03 '15 at 14:40
7

Did you mean "listens_count" rather than "likes_count" in the migration? – Wemmick Nov 24 '15 at 05:53
Neal, I have a similar problem which is a bit more complex so I just can't figure it out. Could you take a look at it here: http://stackoverflow.com/questions/33929227/rails-complex-order-by-with-argument – Sean Magyar Nov 26 '15 at 03:23

score 0 · Answer 3 · answered Nov 16 '17 at 22:13

For rails 4.x try this if your rows without any association matters:

scope :order_by_my_association, lambda {
    select('comments.*, COUNT(listens.id) AS listens_total')
    .joins("LEFT OUTER JOIN listens ON listens.comment_id = comments.id")
    .group('comments.id')
    .order("listens_total DESC")
  }

Rails 3 ActiveRecord: Order by count on association

3 Answers3

Linked