Topic: Stuck! association and find problem...
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?