rails return list of users with average rating above 5

Question

I have 2 models User and Rating as follows:

class User < ActiveRecord::Base
  has_many :ratings
end

class Rating < ActiveRecord::Base
  belongs_to :user
end

Each user receives multiple ratings from 1 - 10. I want to return all users with an average rating of > 5. I've got this so far...

User.joins(:ratings).where('rating > ?', 5)

But that code returns all Users with any rating above 5. I want Users with an Average rating above 5.

I've seen other posts like this and that are asking similar questions, but I'm having a brainfart today, and can't simulate their question into an appropriate answer.

score 1 · Answer 1 · answered May 21 '15 at 18:45

1

If you're looking at all users, why join first?

@avg = Ratings.group(:user_id).average("rating") #returns hash which contains arrays
@avg.each do |avg|                               #0 is user_id, 1 is value
  puts avg[0] if avg[1] > 5
end

answered May 21 '15 at 18:45

Clark

616
4
7

Thanks man. That got me a lot a bit closer! `ids = Rating.group(:user_id).average("rating").select do |item, val| val > 5 end` then to find Users I do `User.where(id:ids.keys)` I feel like it's gotta be possible to do in just one statement, starting with User. – thedanotto May 22 '15 at 03:52

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

You need to defined method average for user rating.

Check link below this is good example of moving float to average.

How do I create an average from a Ruby array?

edited May 23 '17 at 10:26

Community

1
1

answered May 21 '15 at 18:34

Misha

1,876
2
17
24

1

I am torn, I visited that thread and it was really interesting. I learned quite a bit. But returning I do not see its applicability to the OPs question. It may be worthwhile expanding your answer. – Matt Stevens May 21 '15 at 21:52
Agreed, I don't have a ruby array. I've got a bunch of records with ratings 1-10 and an association to the User table. I want to return all Users with an average rating of greater than 5. – thedanotto May 22 '15 at 03:58

score 0 · Accepted Answer · answered May 22 '15 at 05:18

Hope this helps someone in the future. This will find the average rating of each user through the ratings table, and return all users with an average rating above 5.

User.joins(:ratings).merge(Rating.group(:user_id).having('AVG(rating) > 5'))

.having was my missing link. More examples of .having here and here

rails return list of users with average rating above 5

3 Answers3