Rails 3 ActiveRecord：顺序和按关联计数分组 [英] Rails 3 ActiveRecord: order and group by association count

问题描述

我的问题非常类似于> Rails 3 ActiveRecord：按计数排序

鉴于模型

Song我有很多：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}

，这样 song_listen_distribution [4] 会返回听过4次的歌曲数。

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

上面链接的问题的公认答案使我非常接近，但是我无法按 songs.listens_count分组

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

推荐答案

您所寻找的内容与标准ActiveRecord查询并不完全匹配。

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

您可以调用直接SQL来最有效地获取所需内容：

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]

或者，您可以使用ActiveRecord查找所有歌曲的计数，然后在ruby中建立发行字典。 ：

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}

这篇关于Rails 3 ActiveRecord：顺序和按关联计数分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！