MySQL Rails 3 ActiveRecord：按关联计数排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8696005/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails 3 ActiveRecord: Order by count on association
提问by Christoffer Reijer
I have a model named Song. I also have a model named Listen. A Listenbelongs_to :song, and a song :has_many listens(can be listen to many times).
我有一个名为Song. 我也有一个名为Listen. A Listenbelongs_to :song，和一首歌:has_many listens（可以听很多次）。
In my model I want to define a method self.topwhich should return the top 5 songs listened to the most. How can I achieve that using the has_manyrelation?
在我的模型中，我想定义一个方法self.top，该方法应该返回最常听的前 5 首歌曲。我怎样才能使用has_many关系实现这一目标？
I'm using Rails 3.1.
我正在使用 Rails 3.1。
Thanks!
谢谢！
回答by clyfe
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
回答by Neal
Even better, use counter_cachewhich will be faster because you'll only because using one table in your query
更好的是，使用counter_cachewhich 会更快，因为您只会因为在查询中使用一个表
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
或者，如果你想使用上面的方法，这里更简单一点，使用类方法而不是 scope
def self.top
select('comments.*, COUNT(listens.id) AS listens_count').
joins(:listens).
group('comments.id').
order('listens_count DESC').
limit(5)
end
回答by Bruno Casali
For rails 4.x try this if your rows without any association matters:
对于 rails 4.x，如果没有任何关联的行很重要，请尝试此操作：
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")
}
