How to sum through multiple has_many associations?

Question

I do have nested associations like this :

# note : irregular inflection (proj_paquet_mesures : proj_paquets_mesures)
class ProjPaquetMesures < ActiveRecord::Base
  ...
  has_many :proj_mesures
end

class ProjMesure < ActiveRecord::Base
  ...
  has_many :proj_projets
end

class Projprojet < ActiveRecord::Base
  ...
  has_many :proj_sous_projets
end

class ProjSousProjet < ActiveRecord::Base
  ...
  has_many :proj_charges
end

class ProjCharge < ActiveRecord::Base
  # integer value
  attr_accessible :montant 
end

I want to make the sum of all 'montant' nested into one of my proj_paquet_mesures.

I perform the following which is not working (and is not efficient on SQL queries though using 'includes') :

proj_paquet_mesures = ProjPaquetMesures.includes([{:proj_mesures => {:proj_projets => {:proj_sous_projets => :proj_charges}}}]).find(1)
total_charges_of_my_paquet_mesures = proj_paquet_mesures.proj_mesures.proj_projets.proj_sous_projets.proj_charges.sum(:montant)

The fist line of code result in 4 queries instead of the signle expected one join query :

  ProjPaquetMesures Load (8.9ms)  SELECT "proj_paquets_mesures".* FROM "proj_paquets_mesures" WHERE "proj_paquets_mesures"."id" = $1 LIMIT 1  [["id", 1]]
  ProjMesure Load (1.4ms)  SELECT "proj_mesures".* FROM "proj_mesures" WHERE "proj_mesures"."proj_paquet_mesures_id" IN (1)
  ProjProjet Load (0.6ms)  SELECT "proj_projets".* FROM "proj_projets" WHERE "proj_projets"."proj_mesure_id" IN (3)
  ProjSousProjet Load (0.8ms)  SELECT "proj_sous_projets".* FROM "proj_sous_projets" WHERE "proj_sous_projets"."proj_projet_id" IN (1)
  ProjCharge Load (2.7ms)  SELECT "proj_charges".* FROM "proj_charges" WHERE "proj_charges"."proj_sous_projet_id" IN (2)

The second line of code doesn't work at all.

Any idea ?

=== UPDATE ===

Following the first answer, it appears that PostgreSQL is more compliant to SQL standard than MySQL so it needs a "GROUP BY" clause for each selected column you want to display with your aggregated function. So I tried the following code :

proj_paquet_mesures = ProjPaquetMesures.joins([{:proj_mesures => {:proj_projets => {:proj_sous_projets => :proj_charges}}}]).select("proj_charges.id, sum(proj_charges.montant) as total_montant").group([id]).find(1)

But Rails replies with NameError: undefined local variable or methodid' for main:Object`

The official doc is unclear where to apply the group method and lacking explanation and examples.

So I removed the proj_charges.id and end up with this :

proj_paquet_mesures = ProjPaquetMesures.joins([{:proj_mesures => {:proj_projets => {:proj_sous_projets => :proj_charges}}}]).select("sum(proj_charges.montant) as total_montant").find(1)
total_charges_of_my_paquet_mesures = proj_paquet_mesures.total_montant

It works but if I one day I want to calculate a total per proj_charges.id this won't work.

More over the query plan generated by this 4 levels-nested-joins show that Rails is not mature enough to manage professional complex database.

When you start using "JOIN" sql command you need to be able to tell the database in which order you want her to join the table. Otherwise if you don't do so and use deeply nested JOIN, you will become facing responsiveness problem from your database.

To do that you need to be able to use parenthesis between your JOIN instructions to instruct the database which tables must be joined before in which order. Rails is not yet able to let you do that.

Instead you have to rely to SQL by using the rails find_by_sql method.

ProjSouProjet.find_by_sql ['SELECT sp.id AS "proj_sous_projet_id", SUM(c.montant) AS total FROM proj_charges c JOIN (proj_sous_projets sp JOIN (proj_projets p JOIN (proj_mesures m JOIN proj_paquets_mesures pm ON m.proj_paquet_mesures_id = pm.id AND pm.id = ?) ON p.proj_mesure_id = m.id) ON sp.proj_projet_id = p.id) ON c.proj_sous_projet_id = sp.id GROUP BY sp.id', 1]

More explanation here on Join and the query planning of PostgreSQL.

Answer 1

This Should Work. Single Query.

 proj_paquet_mesures = ProjPaquetMesures.join([{:proj_mesures => {:proj_projets => {:proj_sous_projets => :proj_charges}}}]).select("proj_charges.*, sum(proj_charges.monatent) as total_monatant)").find()
 proj_paquet_mesures.total_monatent