Rails - named scope issue (depends on number of children in a many_to_many relationship)

Question

Let's say I have a many-to-many relationship between users and group. A user can be a member of a group, or his application can still be pending.

class User < ActiveRecord::Base
  has_many :applications
  has_many :groups, :through => :applications
end

class Group < ActiveRecord::Base
  has_many :applications
  has_many :users, :through => :applications
end

class Application < ActiveRecord::Base
  belongs_to :user
  belongs_to :group
  attr_accessible :pending # boolean : is the application still pending or not
end

I want to add a scope to my Group class, to select the groups who have more than 10 non-pending users.

I can get those members like this

Group.joins(:applications).where('applications.pending = ?', false)

But I didn't find enough resources to make the scope which counts the numbers of results of this query, and returns the groups where this number is greater than 10

If you have a solution, or ressources on this topic, this would help me greatly

Answer 1

I didn't spec out your models in my own console, but wouldn't something along these lines work?

Group.joins(:applications).group('groups.id').having('COUNT(*) > 10').where(["applications.pending = ?", false])

Basically, once you include GROUP BY conditions in the underlying SQL, you can use HAVING on the aggregate results. The WHERE simply limits it to those results that you're looking for. You can achieve the same result using straight SQL:

Group.find_by_sql(["SELECT * FROM groups INNER JOIN applications ON applications.group_id = groups.id WHERE applications.pending = ? GROUP BY groups.id HAVING COUNT(*) > ?", false, 10])

Kind of a huge query to include in a named scope. You might want to consider breaking this into pieces - it might save lots of headaches later...