3

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?

Andrey Deineko
  • 51,333
  • 10
  • 112
  • 145
Slowboy
  • 581
  • 1
  • 7
  • 25

2 Answers2

5

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
Andrey Deineko
  • 51,333
  • 10
  • 112
  • 145
  • Good answer but you still need to check for nil. "Calculates the average value on a given column. Returns nil if there's no row." http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-average – max Apr 24 '17 at 02:45
1
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
max
  • 96,212
  • 14
  • 104
  • 165
  • It would be a bit more concise to say `avg ? avg * 3 : 0`. – moveson Apr 21 '17 at 04:47
  • @moveson `avg&.* 3` is even more concise, but I think shortness is not the main point :) – Andrey Deineko Apr 21 '17 at 07:02
  • Agreed, but if you are going to go to the trouble of having a ternary, I figure you might as well get rid of the extra method call. I try not to use `#nil?` unless it leads to a clearly better result as compared to relying on the inherent falsiness of the variable that evaluates to `nil`. – moveson Apr 23 '17 at 21:51
  • @moveson as @AndreyDeineko already stated shortness is not the point. Rather the `.nil?` call is just to make it abundantly obvious that whats going on is a nil check. – max Apr 24 '17 at 02:41
  • I understand that shortness is not the point. I'm merely suggesting a style that is both cleaner and [recommended](https://github.com/bbatsov/ruby-style-guide#no-non-nil-checks) in the Ruby style guide. – moveson Apr 24 '17 at 15:38