开发者

Rails3/ActiveRecord: select sum with parameters?

开发者 https://www.devze.com 2023-02-28 22:24 出处:网络
Give the following (already simplified) query in SQLite: def self.calculate(year, month, user_id, partner_id)

Give the following (already simplified) query in SQLite:

def self.calculate(year, month, user_id, partner_id)
  where(':user_id = entries.user_id OR :partner_id = entries.user_id', {    
      :user_id => user_id,
      :partner_id => partner_id
  }).
  where('entries.date <= :last_day', { 
      :last_day => Date.new(year, month, 1).at_end_of_month
  }).
  select('sum(case when joint = "f" and user_id = :user_id then amount_calc else 0 end) as sum_single' , {    
      :user_id => user_id 
  }).
  group("strftime('%Y-%m', date)")
end

The full query has more sums with different case when statements and some of them depend on whether it is user_id oder partner_i开发者_如何学God. Unfortunately, Rails complains as select does not take the second parameter with the substitutions like where does. Is there any way to achieve what I want without running two queries, one for user_id and one for partner_id?


One can be so blind....instead of:

select('sum(case when joint = "f" and user_id = :user_id then amount_calc else 0 end) as sum_single' , {    
  :user_id => user_id 
}).

just build the string:

select('sum(case when joint = "f" and user_id = ' + user_id.to_s + ' then amount_calc else 0 end) as sum_single').

As nobody answered, this is for the archives :)

Edit: Sorry, beware of that: as noted below, this is vulnerable.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号