Using named_scope with counts of child models

Question

Hi, I have a simple parent object having many children. I'm trying to figure out how to use a named scope for bringing back just parents with specific numbers of children.

Is this possible?

class Foo < ActiveRecord::Base
    has_many :bars
    named_scope :with_no_bars, ... # count of bars == 0
    named_scope :with_one_bar, ... # count of bars == 1
    named_scope :with_more_than_one_bar, ... # count of bars > 1
end

class Bar < ActiveRecord::Base
    belongs_to :foo
end

I'm hoping to do something like Foo.with_one_bar

I could write methods on the parent class for something like this, but I'd rather have the power of the named scope

Answer 1

class Foo < ActiveRecord::Base
  has_many :bars

  # I don't like having the number be part of the name, but you asked for it.
  named_scope :with_one_bar, :joins => :bars, :group => "bars.foo_id", :having => "count(bars.foo_id) = 1"

  # More generically...
  named_scope :with_n_bars, lambda {|n| {:joins => :bars, :group => "bars.foo_id", :having => ["count(bars.foo_id) = ?", n]}}
  named_scope :with_gt_n_bars, lambda {|n| {:joins => :bars, :group => "bars.foo_id", :having => ["count(bars.foo_id) > ?", n]}}

end

Called like so:

Foo.with_n_bars(2)

Answer 2

I would use the counter cache for this. Therefore you need the following migration:

class AddBarCount < ActiveRecord::Migration
  def self.up  
    add_column :foos, :bars_count, :integer, :default => 0  

    Foo.reset_column_information  
    Foo.all.each do |p|  
      p.update_attribute :bars_count, p.bars.length  
    end  
  end  

  def self.down  
    remove_column :foos, :bars_count  
  end
end

Than you need too change you Bar model like this:

class Bar < ActiveRecord::Base
  belongs_to :foo, :counter_cache => true
end

Now the count of bars is cached in the foo model, that will speed up your queries for the count of bars.

Your named_scopes then have too look like this:

named_scope :with_no_bars, :conditions => { :bars_count => 0 }
named_scope :with_one_bar, :conditions => { :bars_count => 1 }
named_scope :with_more_than_one_bar, :conditions => ["bars_count > 1"]

That way you can save time counting bars for each foo every time you make such a request.

I got this idea watching the railscast about counter cache: http://railscasts.com/episodes/23-counter-cache-column