How to find topics with the most flags

Question

Let's say I have a model called Topic. A Topic has_many :topic_flags (a TopicFlag is used to denote content that has been flagged as inappropriate). My question is this: how can I select the Topics with the most flags (limited at an arbitrary amount, descending)? I've thought about it for a while, and while I can hack together an SQL query, I'd prefer to find a more Rails way of doing it.

Answer 1

class Topic < ActiveRecord::Base
  has_many :topic_flags

  named_scope :most_flags, lambda {|min_flags| {:joins => :topic_flags, 
                                                :group => "topic_flags.topic_id", 
                                                :order => "count(topic_flags.topic_id) desc", 
                                                :having => ["count(topic_flags.topic_id) >= ?", min_flags] }}
end

This uses an inner join, so it won't pick up Topics with zero flags. You'd call it like this.

Topic.most_flags(3)  # returns a sorted list of all topics with at least 3 flags.