Rails - named scope issue (depends on number of children in a many_to_many relationship)
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开发者_如何学Go 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
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...
精彩评论