advanced (nexted) has_many :through queries in Ruby on Rails (double-JOIN) -

i having nested database layout, however, seem need it. is, our website visitors may each have single account maintaining multiple users (think of identities) within. may create tickets, grouped ticket sections, , have ticket manager (operator) process incoming tickets. not every ticket manager may see every ticket manager member of given ticket section for.

now, totally fine in querying via raw sql statements, failed verbalizing 2 special queries rails way.

here (abstract) model:

# account system class account < activerecord::base   has_many :users   has_many :tickets, :through => :users   has_many :managing_ticket_sections, ... # ticketsection-collection account (any of users) operate   has_many :managing_tickets, ...         # ticket-collection account (any of users) may operate on end  class user < activerecord::base   belongs_to :account   has_many :tickets   has_many :managing_ticket_sections, ... # ticketsection-collection user operate   has_many :managing_tickets, ...         # ticket-collection user may operate on end  # ticket system class ticket < activerecord::base   belongs_to :author, :class_name => "user"   belongs_to :assignee, :class_name => "user"   belongs_to :section, :class_name => "ticketsection" end  class ticketsection < activerecord::base   has_many :tickets   has_many :operators end  class ticketsectionmanager < activerecord::base   belongs_to :manager, :class_name => "user"   belongs_to :section end 

i aware of basic has_many :through-constructs, however, here, accessing more 3 tables tickets.

something works in user's model is:

class user < activerecord::base   has_many :managing_relations, :class_name => "ticketsectionmanager" # xxx kind of helper, 2 below   has_many :managing_sections, :class_name => "ticketsection", :through => :managing_relations, :source => :section   has_many :managing_tickets, :class_name => "ticket", :through => :managing_relations, :source => :section end 

here using helper relation (managing_relations), absolutely never used except 2 has_many relations below. not able describe user.managing_sections nor user.managing_tickets relation without helper, is, need advice for.

secondly, customer have @ of tickets can manage on any user (think of identity) has logged in, need, way collect tickets (/sections) account permitted manage (identified being member of corresponding ticketsection)

here not able express relation ruby way, , had work around following:

class account   def managing_tickets     ticket.find_by_sql("select t.* tickets t         inner join ticket_section_managers m on m.section_id = t.section_id         inner join users u on u.id = m.user_id         u.account_id = #{id}")   end end 

i'd appreciate kind of advice, , many in advance, christian parpart.