Topic: Stuck! association and find problem...

Hi all,
I am hoping for some guidance and or help with this one. I have been through all of the actors, dancers and authors type examples, but this one seems to be a little different.

I have a table of Staff, and a table of recording sessions.
In recording sessions, there are fields for producer,  mix engineer and assistant
I have successfully setup the associations correctly such as

    belongs_to :producer, :class_name=> 'Staff', :foreign_key =>'producer_id'
    belongs_to :mixengineer, :class_name=> 'Staff', :foreign_key =>'MixEngineer_id'
    belongs_to :assistant, :class_name=> 'Staff', :foreign_key =>'assistant_id'

and back the other way.

My question is - how best to construct a query in the controller that gives me a list of all recording sessions a particualr staff member is involved with (without duplicates), regardless of the role?? In this application any staff can act in any role in any session.

I immediatly thought of find_by_sql using a union query to remove the duplicates - or is there an easier way?


Re: Stuck! association and find problem...

Perhaps a change in the schema will make this easier. Have you considered making a HABTM relationship between Recording Session and Staff? You could put a join table, let's call it "jobs" which can hold the details of the relationship.

class Staff < ActiveRecord::Base
  has_many :jobs
  has_many :recording_sessions, :through => :jobs

class Job < ActiveRecord::Base
  belongs_to :staff
  belongs_to :recording_session

class RecordingSession < ActiveRecord::Base
  has_many :jobs
  has_many :staff, :through => :jobs

You can throw a "type" column into the jobs table and use Single Table Inheritence to specify the job.

class Producer < Job

class MixEngineer < Job

class Assistant < Job

It's hard to say if this will work any better without knowing the interface and other details of the application, but might be worth a try.

Railscasts - Free Ruby on Rails Screencasts

Re: Stuck! association and find problem...

Cheers RyanB,
Will try that. After moving this app from PHP am still hungover!