Reputation: 2950
In my Rails app I have something like this in one of the models
def self.calc
columns_to_sum = "sum(price_before + price_after) as price"
where('product.created_at >= ?', 1.month.ago.beginning_of_day).select(columns_to_sum)
end
For some of the rows we have price_before
and or price_after
as nil
. This is not ideal as I want to add both columns and call it price
. How do I achieve this without hitting the database too many times?
Upvotes: 1
Views: 214
Reputation: 2242
You can ensure the NULL
values to be calculated as 0 by using COALESCE
which will return the first non NULL
value:
columns_to_sum = "sum(COALESCE(price_before, 0) + COALESCE(price_after, 0)) as price"
This would however calculate the sum prices of all products.
On the other hand, you might not have to do this if all you want to do is have an easy way to calculate the price of one product. Then you could add a method to the Product
model
def.price
price_before.to_i + price_after.to_i
end
This has the advantage of being able to reflect changes to the price (via price_before or price_after) without having to go through the db again as price_before
and price_after
will be fetched by default.
But if you want to e.g. select records from the db based on the price you need to place that functionality in the DB.
For that I'd modulize your scopes and join them again later:
def self.with_price
columns_to_sum = "(COALESCE(price_before, 0) + COALESCE(price_after, 0)) as price"
select(column_names, columns_to_sum)
end
This will return all records with an additional price
reader method.
And a scope independent from the one before:
def self.one_month_ago
where('product.created_at >= ?', 1.month.ago.beginning_of_day)
end
Which could then be used like this:
Product.with_price.one_month_ago
This allows you to continue modifying the scope before hitting the DB, e.g. to get all Products where the price is higher than x
Product.with_price.one_month_ago.where('price > 5')
Upvotes: 4
Reputation: 5213
If you are trying to get the sum of price_before and price_after for each individual record (as opposed to a single sum for the entire query result), you want to do it like this:
columns_to_sum = "(coalesce(price_before, 0) + coalesce(price_after, 0)) as price"
I suspect that's what you're after, since you have no group
in your query. If you are after a single sum, then the answer by @ulferts is correct.
Upvotes: 1