In my Rails app I have something like this in one of the models
columns_to_sum = "sum(price_before + price_after) as price"
where('product.created_at >= ?', 1.month.ago.beginning_of_day).select(columns_to_sum)
You can ensure the
NULL values to be calculated as 0 by using
COALESCE which will return the first non
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
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_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:
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')