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.
精彩评论