2

I'm trying to pass a value calculated in my controller to a model method and use it in a SELECT query.

I calc a value and want to pass it as an argument to the method below as 'control' and use it in place of the .02 in the sum calculations. The query below works as is, but if I add a third argument, call it 'control' and try to use it in the sum calc it fails. I tried it as I did with the bom and eom values by sticking {:control => control} on to the SELECT statement after the quote and before the parenthesis. The error output shows me that that :control is equal to the value passed in, but it errors on the rest of the instances of :control in the SELECT statement. The DB is PostgreSQL.

 def self.rolling_total_month(bom, eom)
select("SUM((usages.usg_amount * materials.mat_voc_with)/2000) AS uc_emit_tons,
SUM(((.02) * usages.usg_amount * materials.mat_voc_with)/2000) AS c_emit_tons, 
SUM((.02) * (usages.usg_amount * materials.mat_pounds_per_gal)/2000) AS hap_tons
").
joins("JOIN materials ON usages.material_id = materials.id").
  where("usages.material_id <> 65 AND materials.mat_active_flag = TRUE AND materials.mat_report_flag = TRUE AND :bom <= usages.usg_date AND usages.usg_date <= :eom", { :bom => bom, :eom => eom })

end

Kurtismonger
  • 43
  • 4
  • 1
    If you're using ruby on rails, why aren't you using activerecord for all this? You generally shouldn't be writing this much SQL in rails. As far as I can tell, all the SQL you are writing seems to be something that can simply be expressed with an ActiveRecord model. – PressingOnAlways Sep 18 '15 at 04:46
  • I'd love to, but it seemed too complex of a query to do in activerecord with all the SUMs and WHERE clauses. I should add, that I cut out eight or so additional SUM calculations in order to simplify it for posting. – Kurtismonger Sep 18 '15 at 08:29
  • Possible duplicate of [ActiveRecord Select with Parameter Binding](http://stackoverflow.com/questions/13062623/activerecord-select-with-parameter-binding) – Jimeh Oct 14 '15 at 04:53
  • Hey sorry I took so long to reply. My previous answer was way off. I stumbled upon the answer on another stackoverflow [answer](http://stackoverflow.com/a/13063682/3843101), so I marked this one as a duplicate. – Jimeh Oct 14 '15 at 04:54

0 Answers0