has_and_belongs_to_many query works in one direction, fails in the other

Question

I have:

class Service < ActiveRecord::Base
  has_and_belongs_to_many :staffs

and

class Staff < ActiveRecord::Base
  has_and_belongs_to_many :services

With the intermediate table services_staffs with columns services_id and staffs_id

The following query succeeds:

Staff.find( :all, :conditions => "service_id = #{service_id}" )

But going the other direction fails:

Service.find( :all, :conditions => "staff_id = #{staff_id}" )

with

Service Load (0.3ms) SELECT "services".*, t0.staff_id as the_parent_record_id FROM "services" INNER JOIN "services_staffs" t0 ON "services".id = t0.service_id WHERE (t0.staff_id IN (12,13,14)) Service Load (0.0ms) SQLite3::SQLException: no such column: staff_id: SELECT * FROM "services" WHERE (staff_id = 13)

ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column: staff_id: SELECT * FROM "services" WHERE (staff_id = 13) ):

Any idea why??

Answer 1

I normally use has_many and then through, but the concept is the same. You need to include the association in the search so either

Service.find( :all, :include => :staffs, :conditions => "staffs.id = #{staff_id}" )

This will do an outer join so will include all services and will eager load the staff data.

Service.find( :all, :joins => :staffs, :conditions => "staffs.id = #{staff_id}" )

This will do an inner join and only have the service datasets (it will have to go to the database if you call service.staffs

for unsolicited advice, I recommend modifying your query a bit.

Service.all(:include => :staffs, :conditions => ["staffs.id = ?", staff_id])

The array escapes your staff_id variable to help to prevent malicious code attacks.