How to retrieve BigDecimal with find_by_sql?

Question

I'm performing a sum with postgreSQL and want to apply some calculation on the result, but rails return a string instead of a bigdecimal and refuse to convert it to bigdecimal.

create_table SCHEMA_PROJETS + '.proj_charges', :options => 'TABLESPACE ' + TABLESPACE_PROJETS do |t|
  # Gem's Reference : https://github.com/lomba/schema_plus
  t.integer :proj_sous_projet_id, :null => false, :foreign_key => { :name => SCHEMA_PROJETS + '_proj_charges_proj_sous_projet_id_fkey', :references => SCHEMA_PROJETS + ".proj_sous_projets", :on_delete => :restrict, :on_update => :restrict }
  t.string :designation, :null => false, :limit => 60
  t.decimal :montant, :null => false, :default => 0, :precision => 18, :scale => 2
  t.decimal :tva, :null => false, :default => 8.0, :precision => 5, :scale => 2

  t.timestamps
end

This is my find_by_sql and the code to calculate :

      tableau_charges = ProjMesure.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', id.to_i]

      tableau_charges.each do |charges|
        total_charges += charges.total
      end

Produce following error :

String can't be coerced into BigDecimal

=== UPDATE ===

The answer below gave me an idea : My model ProjMesure do have a BigDecimal attribute (montant_reference). So instead of naming my sum with arbitrary name (total), I used the name of that BigDecimal attribute : SUM(c.montant) AS montant_reference.

This trick makes automatically the parsing from string to BigDecimal. It's possible only if you don't plan to use this very attribute in the result of your query.

My new SQL request :

tableau_charges = ProjMesure.find_by_sql ['SELECT sp.id AS "proj_sous_projet_id", SUM(c.montant) AS montant_reference 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', id.to_i]

      tableau_charges.each do |charges|
        total_charges += charges.montant_reference
      end

Answer 1

To my knowledge AR do not parse find_by_sql results automatically. You should parse a string yourself. By just using charges.total.to_d

AFAIK you have two other options:

First is use AR sum and joins methods, if you can do your query with AR stuff. Probably digging deeper to AREL facilities.

Second option is to create a view in your DB and create an active record model for that view. But you can encounter some problems with query params (i.e. filtering results). Consider documentation for your DBMS.