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)

For some of the rows we have
and or
. This is not ideal as I want to add both columns and call it
. 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

  price_before.to_i + price_after.to_i

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)

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)

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