CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
igul222
igul222

Reputation: 8637

How to filter by association count?

Let's say I have models that look like this: 

class Foo < ActiveRecord::Base
    has_many :bars, :through => :cakes
    has_many :cakes
end

class Bar < ActiveRecord::Base
    has_many :foos, :through => :cakes
    has_many :cakes
end

class Cake < ActiveRecord::Base
    belongs_to :foo
    belongs_to :bar
end

How would I get all foos which had 10 or more bars (and therefore 10 or more cakes)?

Upvotes: 3

Views: 2307

Answers (2)

fringd
fringd

Reputation: 2528

okay i tried the answer above, but had a problem.

for our purposes Father has_many :sons, ok?

i wanted to find Fathers that had zero sons.

the above did not work, because it produced an inner join... thereby filtering out all fathers without sons.

the following did work for me:

Father.includes(:sons).group('fathers.id').having( 'count(sons.id)=0' )

and it also happens to work for any other filter you'd require

Father.includes(:sons).group('fathers.id').having( 'count(sons.id)=3' )

Upvotes: 1

pjb3
pjb3

Reputation: 5283

Foo.all(:joins => :cakes, 
  :group => "cakes.foo_id", 
  :having => "count(cakes.bar_id) >= 10")

Upvotes: 5

Related Questions