3

My question is very similar to Rails 3 ActiveRecord: Order by count on association

Given the same scenario where the model

Song has many :listens

I would like to group the songs by the number of listens. My goal is to see a distribution of songs vs listen count. Something like...

song_listen_distribution = {0 => 24, 1 => 43, 2=>11, ... MAX_LISTENS => 1}

so that song_listen_distribution[4] would return the number of songs listened to 4 times.

The accepted answer to the linked question above gets me very close, but I am unable to group by "songs.listens_count"

    Song.select("songs.id, OTHER_ATTRS_YOU_NEED, count(listens.id) AS listens_count").
    joins(:listens).
    group("songs.listens_count").
    order("listens_count DESC")
Community
  • 1
  • 1
wuliwong
  • 4,238
  • 9
  • 41
  • 69
  • when you say you're 'unable to group by `songs.listens_count`', what do you mean? there's an error or it doesn't come out as you'd like? – dax Nov 05 '13 at 20:25
  • I'm doing this at work, and they use pry which I'm a little unfamiliar with. When I run the above query I see `#` as the response. If I switch it to `group("songs.id") then I'm returned an array of `Songs`. I'm also gonna edit my question a little bit. I am removing the scope part because I am only interested in the query. – wuliwong Nov 05 '13 at 20:32
  • The reason I mention "pry" is because I am not familiar with it and I know it manipulates rails console responses a bit. – wuliwong Nov 05 '13 at 20:34

1 Answers1

7

What you are looking for doesn't map well to standard ActiveRecord querying.

You can call straight SQL to get what you are looking for most efficiently:

subquery = Song.joins(:listens).group(:id).select("songs.id, COUNT(*) as listen_count").to_sql
raw = Song.connection.select_rows("SELECT listen_count, COUNT(*) FROM (#{subquery}) t GROUP BY listen_count ORDER BY listen_count DESC")
song_listen_distribution = Hash[raw]

Alternatively, you can use ActiveRecord to find the counts for all songs, and then build up the distribution dictionary in ruby:

song_listens = Song.joins(:listens).group(:id).count
song_listen_distribution = song_listens.group_by{|n| n.last}.
    each_with_object({}){|(k, g), h| h[k] = g.size}
Luke
  • 4,908
  • 1
  • 37
  • 59
PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • 3
    ...i don't don't even know what to say. but that 8sht looks impressive. i don't even know what i was looking for, i'm really working on AngularJs stuff right now but i believe i was searching for help for grouping AR records. In any case i can't even put it all together but both of those queries--the subquery plus the second where you use each_with_object they both look awesome. i'm too tired to figure it out now but that's pretty heavy lifting which can be useful. not just because of the SQL but how it integrates with Rails ActiveRecord. plus One Son – FireDragon May 01 '14 at 07:18
  • @PinnyM can you please help me out here, i have a similar problem...http://stackoverflow.com/questions/23850832/list-all-song-orders-by-month – Serge Pedroza May 25 '14 at 19:01