0

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.

Jason
  • 1,098
  • 12
  • 33

4 Answers4

2

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.

mkk
  • 7,583
  • 7
  • 46
  • 62
  • Thanks for the suggestion about getting the average. I combined it with Shadwell's solution to get what I needed. – Jason Nov 02 '12 at 22:43
2

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.

Shadwell
  • 34,314
  • 14
  • 94
  • 99
  • I actually wanted the highest average review so the second one is close but then I need to divide by the total to get the average. This seemed to work: ".order("sum(reviews.rating) / count(*)"). – Jason Nov 02 '12 at 22:41
  • 2
    If you want the average you can probably do `.order("avg(reviews.rating)")` – Shadwell Nov 03 '12 at 21:37
  • 'avg', even better! Wish I could up vote this again. Do you know how to include Courses without any reviews? As it is now I only get those with reviews so I do a subsequent query to grab more. – Jason Nov 04 '12 at 19:09
  • If you use `Course.include(:reviews)` it should do an outer join which will pull in courses that have no reviews. – Shadwell Nov 04 '12 at 20:21
  • 1
    Sorry, it should be `includes` not `include`. So `Course.includes(:reviews)` etc. – Shadwell Nov 04 '12 at 23:24
  • Tried this: `Course.includes(:reviews).where('courses.ID IS NOT NULL').group('courses.id, reviews.id').order("avg(reviews.rating) desc")` but now it doesn't return the courses with reviews! – Jason Nov 04 '12 at 23:29
  • You shouldn't need the extra bits and bobs - just a straight swap of `joins` for `includes` should do it. If not it won't be a massive issue to do another query for a few courses to pad out the list. – Shadwell Nov 05 '12 at 00:12
  • Tried this and it didn't work: `Course.includes(:reviews).group("courses.id, reviews.id").order("avg(reviews.rating) desc").limit(num)`. The reviews.id was necessary in the group clause. – Jason Nov 05 '12 at 02:19
0

you may try this

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

or

select top 1.* from courses order by review desc
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
SRIRAM
  • 1,888
  • 2
  • 17
  • 17
0

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`
Claud Kho
  • 426
  • 4
  • 12