Emanuel Emanuel - 3 years ago 209
Ruby Question

Computing ActiveRecord nil attributes

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?

Answer Source

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')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download