Putting a condition on a many-to-many query with ActiveRecord

Question

I've got the following relationships in my model:

class Show < ActiveRecord::Base
  has_many :service_shows
  has_many :services, :through => :service_shows
end

class Service < ActiveRecord::Base
  has_many :service_shows
  has_many :shows, :through => :service_shows
end

class ServiceShow < ActiveRecord::Base
  belongs_to :show
  belongs_to :service
end

I'd like to query back all shows for a given service that have a rec_status = 'A', but my ActiveRecord skills are only about three days old, so I don't quite have the chops. If I understand correctly, I could simply call service.shows and filter the returned list, but I'd like to only retrieve the records I need from the database--I'd rather not waste processor time and memory on records I don't want.

Thanks!

Answer 1

From your description, it sounds like shows have the :rec_status column. Based on that, I set up some sample data:

Show.create!(:name => 'One', :rec_status => 'A')
Show.create!(:name => 'Two', :rec_status => 'B')

Service.create!(:name => 'Service One')
Service.create!(:name => 'Service Two')

Show.first.services = Service.all
Show.last.services = Service.all

Given a single service you can, as you mention, get back all the shows:

service = Service.first
service.shows

If you want to select a subset of records, you can extend the chain with a finder call:

service.shows.all(:conditions => {:rec_status => 'A'})

Better yet, you can capture this as a named scope on the Show model:

class Show < ActiveRecord::Base
  has_many :service_shows
  has_many :services, :through => :service_shows

  named_scope :for_status, lambda {|status_flag| {:conditions => {:rec_status => status_flag}} }
end

And then use it instead of passing the :conditions hash:

service.shows.for_status('A')