Mysql order by rating - but capture all

Question

Hi, I have this PHP/MYSQL code which returns records from a table ordered by their ratings, from highest rated to lowest rated:

<table width="95%">
 <tr>
 <?php
  if (isset($_GET['p'])) {
   $current_page = $_GET['p'];
  } else {
   $current_page = 1;
  }
  $cur_category = $_GET['category'];
  $jokes_per_page = 40;
  $offset = ($current_page - 1) * $jokes_per_page;

  $result = mysql_query("
  select jokedata.id as joke_id, jokedata.datesubmitted as datesubmitted,
  jokedata.joketitle as joke_title, sum(ratings.rating)/count(ratings.rating) as average
  from jokedata inner join ratings
  on ratings.content_type = 'joke' and ratings.relative_id = jokedata.id
  WHERE jokecategory = '$cur_category'
  group by jokedata.id
  order by average desc
  limit $offset, $jokes_per_page
  ");

  $cell = 1;
  while ($row = mysql_fetch_array($result)) {
   if ($cell == 5) {
    echo "</tr><tr class=\"rowpadding\"><td></td></tr><tr>";
    $cell = 1;
   }
   $joke_id = $row['joke_id'];
   $joke_title = $row['joke_title'];
   $joke_average = round($row['average'], 2);

   echo "<td><strong><a class=\"joke_a\" href=\"viewjoke.php?id=$joke_id\">$joke_title</a></strong> -average rating $joke_average.</td>";
   $cell++;
  }
 ?>
 </tr>
 <tr class="rowpadding"><td></td></tr>
</table>

It works perfectly but there is one problem - if an item does not have at least one rating, it will not be selected by the query at all!

How can I remedy this? Thanks.

Answer 1

You need to use a left join rather than an inner join, and then handle the case where ratings.ratings is null:

$result = mysql_query("
            SELECT jokedata.id AS joke_id, 
            jokedata.datesubmitted AS datesubmitted,
            jokedata.joketitle AS joke_title, 
            -- average is 0 if count or sum is null
            IFNULL(SUM(ratings.rating)/COUNT(ratings.rating), 0) AS average
            FROM jokedata 
            -- return all rows from left table (jokedata), and all nulls for ratings
            -- data when there is no matching row in the right table (ratings)
            LEFT JOIN ratings ON ratings.content_type = 'joke' AND jokedata.id = ratings.relative_id 
            WHERE jokecategory = '$cur_category'
            GROUP BY jokedata.id
            ORDER BY average desc
            LIMIT $offset, $jokes_per_page
            ");

The left join will return all results from jokedata and will just return all nulls for ratings for each row where the join condition is not met.

Answer 2

I would recommend the following:

• use left outer join to get jokes that have no ratings
• use avg() instead of manually computing average
• possibly use coalesce() to avoid null values in the result

Here's a simplified version of your tables:

create table joke(jokeid int primary key, jokedata varchar(50));
create table ratings(rating int, relative_id int);
insert into joke values(1, "killing");
insert into joke values(2, "no rating");
insert into ratings values(5, 1);
insert into ratings values(10, 1);

And some example queries:

select joke.jokeid, avg(ratings.rating) as average 
   from joke 
   left outer join ratings 
     on ratings.relative_id = joke.jokeid 
   group by joke.jokeid;
+--------+---------+
| jokeid | average |
+--------+---------+
|      1 |  7.5000 | 
|      2 |    NULL | 
+--------+---------+

Or, using coalesce():

select joke.jokeid, avg(coalesce(ratings.rating, 0)) as average 
  from joke 
  left outer join ratings 
    on ratings.relative_id = joke.jokeid 
  group by joke.jokeid;
+--------+---------+
| jokeid | average |
+--------+---------+
|      1 |  7.5000 | 
|      2 |  0.0000 | 
+--------+---------+

Answer 3

It seems to me that using the average is a little unfair. If Joke A has 1 rating of 5 and Joke B has 25 ratings of 4, then Joke A will rank above Joke B. It gives unpopular jokes more of an advantage to be ranked higher.

I would suggest associating weight to ratings and then ranking by the weight. For example, on a scale from 1 to 5, 1 would get a -2, 2 would get a -.5, 3 is 0, 4 is +.5 and 5 would get a +2. So this would allow a joke with 5 "4" ratings to be ranked higher than the joke with 1 "5" rating.

The -2 to +2 scale may need some tweaking, but hopefully you see my point.

Answer 4

Try this:

SELECT  jokedata.id as joke_id,
        jokedata.datesubmitted as datesubmitted,
        jokedata.joketitle as joke_title,
        COALESCE(
        (
        SELECT  AVG(rating)
        FROM    ratings
        WHERE   ratings.relative_id = jokedata.id
                AND ratings.content_type = 'joke'
        ), 0) AS average
FROM    jokedata
ORDER BY
        average DESC
LIMIE   $offset, $jokes_per_page