Reputation: 1896
I have method which calculates total income from receipts
table. But currently it makes two sql
calls every time the method is called, is it possible to improve the sql
to make only one database call. I am fine with raw sql
if needed.
def total
Receipt.where(receipt_type: 'income').sum(:amount) - Receipt.where(receipt_type: 'refund').sum(:amount)
end
Main objective is avoid two database calls. And if the difference can be calculated in the database only it will be even better. Thanks.
Upvotes: 0
Views: 417
Reputation: 13139
In SQL, I would solve this with
SELECT SUM(amount) FILTER (WHERE receipt_type = 'income')
- SUM(amount) FILTER (WHERE receipt_type = 'refund')
FROM receipt
but I wasn't able to find the equivalent in ruby. I have no experience with it, but from this example, I think the following should work:
Receipt.select("SUM(amount) FILTER (WHERE receipt_type = 'income') - SUM(amount) FILTER (WHERE receipt_type = 'refund') )
Upvotes: 1