CODEWITHSUNDEEP
sqlruby-on-railssum
Jason
Jason

Reputation: 1138

Rails select and sort by aggregate of has_many

I have a Course model which has_many reviews. My Review model has a rating field. What I would like is a query to find the top rated courses. I want to do this in the SQL in order to avoid returning all the course objects and then trying to rank and sort them.

Upvotes: 0

Views: 1142

Answers (4)

Shadwell
Shadwell

Reputation: 34784

You don't describe how you define "top rated". It could be that you want the courses with the highest individual ratings:

Course.joins(:reviews).group("courses.id").order("reviews.rating desc")

Or you might want the courses with the highest total ratings:

Course.joins(:reviews).group("courses.id").order("sum(reviews.rating) desc")

Then on each of these you can call first(10) to get the top ten by whichever criteria you want.

Upvotes: 2

mkk
mkk

Reputation: 7693

I would try this SQL:

SELECT courses.* FROM 
 (SELECT courses.*, count(*) as rating_no 
  FROM courses 
  JOIN reviews ON reviews.course_id = courses.id 
  GROUP BY reviews.course_id ) as res 
ORDER BY rating_no DESC

the idea: You first take all data for each course, along with number of ratings. Then you just sort them. My experinece in MySQL is that GROUP BY and ORDER BY do not like each other, and that is why I used subquery. You might want to try on one query first though, but I doubt it will work :) If you are not interstend in just number of ratings, but for example actual average rate, you can make query like 

SELECT courses.* FROM 
 (SELECT courses.*, (SUM(reviews.rating)/count(*)) as avg_rating 
  FROM courses 
  JOIN reviews ON reviews.course_id = courses.id 
  GROUP BY reviews.course_id HAVING count(*) > 0) as res 
ORDER BY rating_no DESC

having count(*) >0 is most likely not needed, but just in case I have added it, to point out that you should avoid dividing by 0.

Upvotes: 2

SRIRAM
SRIRAM

Reputation: 1888

you may try this

select  * from courses where review=(select max(rating) from table)

or

select top 1.* from courses order by review desc

Upvotes: 0

Claud Kho
Claud Kho

Reputation: 426

Try this

@courses = Course.find(:all, :joins => "INNER JOIN reviews ON reviews.courses_id = courses.id", :order => "reviews.rating DESC")

or RAW sql

`SELECT `courses`.* FROM `courses` INNER JOIN reviews ON reviews.courses_id = courses.id ORDER BY reviews.rating DESC`

Upvotes: 0

Related Questions