Find the item with the best rating

Databases
1

Hi all!

I have a table for storing submitted ratings of user images.
The rows are :

image_id (unique identifier of the rated image) and rating_num (value from 1-5 depending on the rating). Every time a user clicks to rate an image a row is added to the table (with the image_id and the rating_num).

I use one query to find the image_id which is rated the most :


SELECT image_id, COUNT(image_id) AS countrates FROM ratings GROUP BY image_id ORDER BY countrates DESC LIMIT 1

which works OK.
What I want is to build a query (if possible) to find the image_id with the highest rating.
I have this PHP function to find the overall rating of an image with the given id :


function outOfFive($id) {

	$total = 0;
	$rows = 0;
	
	$sel = mysql_query("SELECT rating_num FROM ratings WHERE image_id = '$id'");
	if(mysql_num_rows($sel) > 0){
	
		while($data = mysql_fetch_assoc($sel)){
			$total = $total + $data['rating_num'];
			$rows++;
		}
		$perc = ($total/$rows);
		return round($perc,2);
	} else {
		return '0';
	}
} // end function out of 5

but I think it’s not elegant to cycle through this function for every single image_id from the table (there are already over 1,000 rows and counting…). Can someone suggest a good solution please?
Thanks advanced.

2 
SELECT image_id
     , COUNT(image_id) AS countrates 
     , [COLOR="#0000FF"]SUM(rating_num ) AS totalrating[/COLOR]
  FROM ratings 
GROUP 
    BY image_id 
ORDER 
    BY [COLOR="#0000FF"]totalrating[/COLOR] DESC LIMIT 1
3

In the meantime I solved this with the PHP function above :


$best_rating = 0;
$q = "SELECT * FROM ratings ORDER BY image_id ASC";
$result = mysql_query($q, $this->connection);

while ($ratings_row = mysql_fetch_array($result)) {
	$rating_this_image = outOfFive($ratings_row['image_id']);
	if ($rating_this_image != '0') {
		if ($rating_this_image > $best_rating) {
			$best_rating = $rating_this_image;
			$best_rated_img_id = $ratings_row['image_id'];
		}
	}
}

I will try to do it with your query and will post the results… THNX a lot!!!

4

I forgot an important detail. I’ve modified the function like


function outOfFive($id) {
			
	$total = 0;
	$rows = 0;
							
	$sel = mysql_query("SELECT rating_num FROM ratings WHERE rating_id = '$id'");
		if(mysql_num_rows($sel) > 10){ // I WANT TO COMPARE RATINGS WITH MORE THAN 10 VOTES!
			
			while($data = mysql_fetch_assoc($sel)){
				$total = $total + $data['rating_num'];
				$rows++;
			}
			$perc = ($total/$rows);
			return round($perc,2);
			
		} else {
			return '0';
		}
	}

… look at the comment

5

please give up this idea of doing the counting or summing with php

it is ~way~ more efficient to do this with sql

also, the new condition (more than 10 votes) is trivial with sql

6

I’ve tried with the new query and I got different results! The script didn’t retrieve the best rated image… It did retrieve an image with an average rating, I can’t figure out on basis of which condition…

7

kindly show the query you tried if you expect to get help on it

8

I used the query in the exact form you have posted…

9

if that’s true then maybe i don’t understand your data

can you set up a test case please? a number of sample rows, so that i can test the query myself

you can use mysqldump to generate the CREATE TABLE and INSERT statements

10

Here you go Rudy,


CREATE TABLE IF NOT EXISTS `ratings` (
  `id` int(11) NOT NULL auto_increment,
  `image_id` int(11) NOT NULL,
  `media_type` varchar(20) NOT NULL,
  `rating_num` int(11) NOT NULL,
  `IP` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1083 ;

some inserts :


INSERT INTO `ratings` (`id`, `media_id`, `media_type`, `rating_num`, `IP`) VALUES
(5, 223, 'images', 5, '89.216.76.193'),
(6, 237, 'images', 5, '89.216.76.193'),
(7, 219, 'images', 5, '213.47.66.9'),
(8, 226, 'images', 5, '213.47.66.9'),
(9, 217, 'images', 5, '89.216.76.193'),
(10, 219, 'images', 5, '89.216.76.125'),
(301, 96, 'images', 5, '180.76.5.16'),
(300, 96, 'images', 4, '180.76.5.22'),
(299, 128, 'images', 5, '89.216.76.209'),
(298, 144, 'images', 2, '180.76.5.21'),
(297, 260, 'images', 5, '89.216.76.120'),
(296, 260, 'images', 5, '213.47.66.9');

I believe you got the point. Somehow the script showed the image with overall rating of 3.14 and best rated was 3.4 which was showedwith the help of the previously posted PHP function (my not-so-elegant solution).

I really appreciate your help in this… :wink:
Regards

11

when i ran my query on your data, this is what i got –

image_id     countrates     totalrating
    96           2              9
   128           1              5
   144           1              2
   217           1              5
   219           2              10
   223           1              5
   226           1              5
   237           1              5
   260           2              10

this was without the LIMIT

so, can you tell me what’s wrong with this output?

12

I believe you misunderstood my goal… Instead of totalrating, we need an average rating of any image. I.E. the average rating for the image_id 96 should be 4.5 instead of 9. The query need to retrieve the image_id with the highest AVERAGE rating (for images with above 10 ratings).

13

yeah, looks like i missed that

okay, see in my query where it has SUM()? change that to AVG() and let me know how it goes…

:slight_smile:

14

No problem! :slight_smile:

It seems it’s working now… How can I implement the condition : at least 10 ratings? I.E. something like :


SELECT image_id
     , COUNT(image_id) AS countrates 
     , AVG(rating_num ) AS totalrating
  FROM ratings
WHERE COUNT(image_id) > 10
GROUP 
    BY image_id 
ORDER 
    BY totalrating DESC LIMIT 1

???

EDITED : I got a MySql warning for the query above…

15

you need a HAVING clause, not a WHERE clause

it’s a condition on the group of rows (i.e. HAVING), not on each individual row (i.e. WHERE)

16

Sorry, I’m NOT an sql expert… Here’s the wanted query :


SELECT rating_id
	 , COUNT(rating_id) AS countrates 
	 , AVG(rating_num ) AS totalrating
	  FROM ratings 
					  
	GROUP 
	BY rating_id 
	HAVING COUNT(rating_id) > 4 
	ORDER 
		BY totalrating DESC LIMIT 1

Thank you once again! Problem solved.