CODEWITHSUNDEEP
sqlruby-on-rails-3activerecordarel
Jimmy
Jimmy

Reputation: 9815

Rails nested joins with a where containing a count on a nested join table

I've got the following model setup in my rails 3 app:

class User << AR::Base
  has_many :groups
end

class Group << AR::Base
  belongs_to :user
  has_many :memberships
  has_many :contacts, :through => :memberships
end

class Membership << AR::Base
  belongs_to :group
  belongs_to :contact
end

class Contact << AR::Base
  has_many :memberships
  has_many :groups, :through => :memberships
end

I am trying to offload as much of this query to the database as possible but I am trying to get a list of users that have more than x number of contacts through their (potentially many) groups.

I can do this through ruby/rails code without a problem but it loads everything into memory which can be a pain for larger datasets.

x = 4 # or whatever
User.all.select{ |u| u.groups.collect(&:contacts).flatten.uniq.size > x }

I've tried doing something like the following but to no avail:

User.joins(:groups => :contacts).where('count(contacts.id) > ?', x)

Also it would be nice for the contacts to be distinct

any kind of points in the right direction would be awesome, sql isn't one of my strong points and it seems i'm brain dead today.

Upvotes: 2

Views: 3701

Answers (1)

Jimmy
Jimmy

Reputation: 9815

After struggling with this for a bit I got some help and was able to finalize my query like so

User.select("users.*, count(distinct contacts.id) as num").joins(:groups => :contacts).group('users.id').having('num > 10')

Upvotes: 9

Related Questions