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

Question

I am having a somewhat too nested database layout, however, I seem to need it. That is, Our website visitors may each have a single account with maintaining multiple users (think of identities) within. Now they may create tickets, which are grouped by ticket sections, and we have ticket manager (operator) to process the incoming tickets. Not every ticket manager may see every ticket but only those this manager is a member of the given ticket section for.

Now, I am totally fine in querying via raw SQL statements, but I failed to verbalizing those two special queries the Rails way.

Here is the (abstract) model:

# account system
class Account < ActiveRecord::Base
  has_many :users
  has_many :tickets, :through => :users
  has_many :managing_ticket_sections, ... # TicketSection-collection this account (any of its users) is operate for
  has_many :managing_tickets, ...         # Ticket-collection this account (any of its users) may operate on
end

class User < ActiveRecord::Base
  belongs_to :account
  has_many :tickets
  has_many :managing_ticket_sections, ... # TicketSection-collection this user is operate for
  has_many :managing_tickets, ...         # Ticket-collection this 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 am aware of basic has_many :through-constructs, however, here, I am accessing more than three tables to get the tickets.

Something that actually works for in the User's model is:

class User < ActiveRecord::Base
  has_many :managing_relations, :class_name => "TicketSectionManager" # XXX some kind of helper, for the two 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 I am using a helper relation (managing_relations), which is absolutely never used except by the two has_many relations below. I were not able to describe a User.managing_sections nor User.managing_tickets relation without this helper, which is, where I need an advice for.

Secondly, the customer is to have a look at all of the tickets he can manage on any User (think of an identity) he has logged in, so what I need, is a way to collect all tickets (/sections) this Account is permitted to manage (identified by being a member of the corresponding TicketSection)

Here I even were not able to express this relation the ruby way, and I had to work around it by the following:

class Account
  def managing_tickets
    Ticket.find_by_sql("SELECT t.* FROM tickets AS t
        INNER JOIN ticket_section_managers AS m ON m.section_id = t.section_id
        INNER JOIN users AS u ON u.id = m.user_id
        WHERE u.account_id = #{id}")
  end
end

I'd appreciate any kind of advice, and many thanks in advance, Christian Parpart.

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

0 Answers0