activerecord - Rails complex query with multi level associations -

activerecord - Rails complex query with multi level associations -

i have nested layout follows:

contract -> has many packages -> has many services

payment -> belongs_to invoice -> belongs_to contract

class contract < activerecord::base has_many :invoices has_many :contract_packages has_many :packages, through: :contract_packages end class bundle < activerecord::base has_many :services has_many :contract_packages has_many :contracts, through: :contract_packages end class contractpackage < activerecord::base belongs_to :contract belongs_to :package end class service < activerecord::base belongs_to :package end class invoice < activerecord::base belongs_to :contract end class payment < activerecord::base belongs_to :invoice end

i want find services, , how many times invoiced in period of time, based on payment date. invoice date may not same payment date.

i know hot pure sql, , works, stuck if want rails way.

any ideas?

edit:

the pure sql query:

select s.[name], count(*), s.[price] payments p left bring together invoices on p.invoice_id=i.id left bring together contracts c on i.[contract_id]=c.id left bring together contract_packages cp on cp.contract_id=c.id left bring together packages pk on cp.[package_id]=pk.id left bring together services s on s.package_id=pk.id ... conditions grouping s.id order s.id asc

in original question left out, brevity, bring together table, because bundle may belong many contracts. sql here includes bring together table. updated models also.

doing joins in in activerecord quite straight forwards long have defined relationships in models. pass hash joins , figures out keys use. adding conditions can done in similar fashion.

i noticed there no has_many :payments in invoice design? in case why?

the select clause have written give service objects created query method count find value.

service.select('*, count(*) count') .joins({ package: { contract: { invoices: :payment } } }) .where(conditions_hash) .group('services.id asc') .order(:id)

ruby-on-rails activerecord