CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4activerecordruby-on-rails-5
Slowboy
Slowboy

Reputation: 601

ActiveRecord: treat nil as a 0 in calculations?

I'm using this code in my controller to access the average paper_weight for all my users in the Heavy classification.

if User.where(industry_type: 'Heavy')
 .joins(:papers)
 .where(papers: { paper_type: 'Officepaper' } )
 .pluck('avg(paper_weight)').first.nil?
 @heavy_indust_officepaper == 0
else
@heavy_indust_officepaper = User.where(industry_type: 'Heavy')
 .joins(:papers)
 .where(papers: { paper_type: 'Officepaper' } )
 .pluck('avg(paper_weight)').first * 3 
end

Then the variable @heavy_indust_officepaper is displayed in a view.

The problem is that this code above doesn't seem to be calculating the average correctly when one or more users have a nil entry in paper_type: 'Officepaper' .

I know that because I have two users in the industry_type: 'Heavy' One of them has one entry of paper_type: 'Officepaper' which is the decimal 30. And the other user has nil entry in paper_type: 'Officepaper'.

By my understanding the calculation should be 30 + 0 / 2(users) = 15

and then the 15is multiplied by 3 which should give 45

But instead the variable @heavy_indust_officepaper is displaying 90in the view... which must be the result of 30 * 3

Is there a way to convert the nilto 0 with in the code above???

Please Can some one advise me?

Here is a link to a question I asked earlier to day, I got help with this code from it Using .average with .pluck in ruby on rails app?

Upvotes: 3

Views: 2782

Answers (2)

Andrey Deineko
Andrey Deineko

Reputation: 52357

The issue here is (among others) is your misunderstanding of what pluck does. It returns you an array of average someting for every user. So in your example it returns you

[30, nil]

That's why you get 90 ([30, nil].first * 30 => 90).

To get average for all users, use average.

Solution:

You can use COALESCE to convert NULL to zero while calculating average:

User.where(industry_type: 'Heavy')
    .joins(:papers)
    .where(papers: { paper_type: ['Officepaper', nil] } )
    .average('COALESCE(papers.paper_weight, 0)') * 3
#=> 15

One thing to be aware of is that while COALESCE will convert any null value to 0 while calculating, average will still return nil if there were no records to calculate average on.

How to handling this case is for you to decide, but @max already has shown one of the pretty straightforward options (assuming that above query results is written to average variable):

average.nil? ? 0 : average * 3

Upvotes: 5

max
max

Reputation: 101901

avg = User.where(industry_type: 'Heavy')
 .joins(:papers)
 .where(papers: { paper_type: ['Officepaper', nil] } )
 .average('papers.paper_weight')

@heavy_indust_officepaper = avg.nil? ? 0 : avg * 3

Upvotes: 1

Related Questions