CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordrails-activerecord
emi
emi

Reputation: 2950

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?

Upvotes: 1

Views: 214

Answers (2)

ulferts
ulferts

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

moveson
moveson

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

Related Questions