Tutorial :MySQL Rating system (calculating average from two tables)



Question:

I have two tables, videos and videos_ratings. The videos table has an int videoid field (and many others but those fields are not important I think) and many records. The videos_ratings table has 3 int fields: videoid, rating, rated_by which has many records (multiple records for each fields from the videos table) but not for all records from the videos table.

Currently I have the following mysql query:

  SELECT `videos`.*, avg(`videos_ratings`.`vote`)       FROM `videos`, `videos_ratings`      WHERE `videos_ratings`.`videoid` = `videos`.`videoid`   GROUP BY `videos_ratings`.`videoid`   ORDER BY RAND() LIMIT 0, 12  

It selects all the records from table videos that have a rating in table video_ratings and calculates the average correctly. But what I need is to select all records from the videos table, no matter if there is a rating for that record or not. And if there aren't any records in the videos_ratings table for that particular videos record, the average function should show 0.

Hope someone could understand what I want... :)

Thanks!


Solution:1

Use:

   SELECT v.*,            COALESCE(x.avg_vote, 0)       FROM VIDEOS v  LEFT JOIN (SELECT vr.videoid,                    AVG(vr.vote) AS avg_vote               FROM VIDEO_RATINGS vr           GROUP BY vr.videoid) x ON x.videoid = v.videoid   ORDER BY RAND()      LIMIT 12  

Be aware that ORDER BY RAND() does not scale well - see this question for better alternatives.


Solution:2

You need to do a LEFT JOIN to include all records from the videos table, even if there's no rating.

You can then put an if() in your select to turn a NULL rating to 0.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »