MySQL SELECT but ORDER BY a different table

[imperium2335]

Hi,

 

I am trying to setup a gallery where the user can sort the pictures by a few different things. I have everything perfect, like sorting by date and view count, just not rating.

 

Ratings are all kept in a different table, and are either "like" or "hate" ratings.

 

I am thinking in order for this to work it is going to have to count the likes and hates for each image in the images table inside the MySQL query.

 

Currently I have:

if($sort == "newest") $sort = "dateAdded DESC" ;
if($sort == "oldest") $sort = "dateAdded ASC" ;
if($sort == "views") $sort = "viewCount DESC" ;
if($sort == "rating") $sort = "dateAdded DESC" ;

$result = mysql_query("SELECT * FROM images WHERE category = 'shaven' ORDER BY $sort LIMIT $lowerLimit, $perPage") ;

 

What do I need to add to this query to make it do what I want?

 

Tom.

[kickstart]

Hi

 

You need to do a JOIN witha subselect on the ratings table.

 

$result = mysql_query("SELECT * 
FROM images a
LEFT OUTER JOIN (SELECT ImageId, COUNT(HateRatings) AS HateCount, COUNT(LikeRatings) AS LikeCount
FROM RatingsTable
GROUP BY ImageId) b
ON a.ImageId = b.ImageId
WHERE category = 'shaven' ORDER BY $sort LIMIT $lowerLimit, $perPage") ;

 

Could be done with a plain JOIN and COUNT as well

 

$result = mysql_query("SELECT a.*, COUNT(b.HateRatings) AS HateCount, COUNT(b.LikeRatings) AS LikeCount
FROM images a
LEFT OUTER JOIN RatingsTable
ON a.ImageId = b.ImageId
WHERE category = 'shaven' 
GROUP BY ..........
ORDER BY $sort LIMIT $lowerLimit, $perPage") ;

 

Replace ..... with the fields you want from the images table.

 

All the best

 

Keith

[imperium2335]

Hi,

 

Thanks for your help.

 

This would normally work for me but in my tables there is only one column called 'liked', and it is either 0 or 1 (0 for hated 1 for liked).

 

How do I adjust your statement to only count if the value in the liked column is 0 (hates) or 1 (likes)?

 

Tried:

$result = mysql_query("SELECT * 
FROM images a
LEFT OUTER JOIN (SELECT imageRefId, COUNT(likes) AS HateCount WHERE liked = 0, COUNT(likes) AS LikeCount WHERE liked = 1
FROM image_ratings
GROUP BY imageRefId) b
ON a.id = b.imageRefId
WHERE category = 'shaven' ORDER BY $sort LIMIT $lowerLimit, $perPage")or die(mysql_error()) ;

 

But doesn't work

 

Tom.

[kickstart]

Hi

 

Easy to understand with 2 subselects.

 

$result = mysql_query("SELECT * 
FROM images a
LEFT OUTER JOIN (SELECT ImageId, COUNT(*) AS LikeCount
FROM RatingsTable
WHERE liked = 1
GROUP BY ImageId) b
ON a.ImageId = b.ImageId
LEFT OUTER JOIN (SELECT ImageId, COUNT(*) AS HateCount
FROM RatingsTable
WHERE liked = 0
GROUP BY ImageId) c
ON a.ImageId = b.ImageId
WHERE category = 'shaven' ORDER BY $sort LIMIT $lowerLimit, $perPage") ;

 

Another way

 

$result = mysql_query("SELECT a.*, SUM(IF(Liked=0),1,0) AS HateCount, SUM(IF(Liked=1),1,0) AS LikeCount
FROM images a
LEFT OUTER JOIN RatingsTable
ON a.ImageId = b.ImageId
WHERE category = 'shaven' 
GROUP BY ..........
ORDER BY $sort LIMIT $lowerLimit, $perPage") ;

 

Neither tested so please excuse any typos.

 

All the best

 

Keith

[imperium2335]

Could you explain what the a, b and c refers to as I still can't get it to work.

 

is ON a.id = b.liked refer to the id from the images table (a) and the liked column in the image_ratings table (b)?

 

Thanks for your help so far.

[kickstart]

Hi

 

The a, b and c are just aliases for the tables. However just noticed that in the 2nd example I forgot to alias the table .

 

$result = mysql_query("SELECT ........, SUM(IF(Liked=0),1,0) AS HateCount, SUM(IF(Liked=1),1,0) AS LikeCount

FROM images a

LEFT OUTER JOIN RatingsTable b

ON a.ImageId = b.ImageId

WHERE category = 'shaven'

GROUP BY ..........

ORDER BY $sort LIMIT $lowerLimit, $perPage") ;

 

You need to add the b I have put in bold.

 

The ...... need to be replaced with the fields from images that you need.

 

All the best

 

Keith

[imperium2335]

Hi,

 

Have been trying for the past day but still does not work  :'(

 

this is what I have:

$result = mysql_query("SELECT a.*, SUM(IF(liked=0),1,0) AS HateCount, SUM(IF(liked=1),1,0) AS LikeCount
FROM images a
LEFT OUTER JOIN image_ratings b
ON a.id = b.image_ratings
GROUP BY imageRefId
ORDER BY LikeCount LIMIT 0, 9")or die(mysql_error()) ;

 

gives me:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '),1,0) AS HateCount, SUM(IF(liked=1),1,0) AS LikeCount FROM images a LEFT OUTE' at line 1

 

Have tried loads of variations with no luck. Have tried your other example with limited success, it would either give me an error or output results in an order that was wrong.

 

Thanks for your help so far!

[imperium2335]

Hi,

 

In case anyone's interested, I have created a work around for the time being:

 

$scanned = array() ;

$ratedIds = array() ;
$ratedRates = array() ;

$result = mysql_query("SELECT * FROM image_ratings ORDER BY imageRefId") ;

while($row = mysql_fetch_assoc($result)) {

$likers = $haters = $rating = 0 ;

$refId = $row['imageRefId'] ;
if(!in_array($refId, $scanned)) {
$resultCount = mysql_query("SELECT * FROM image_ratings WHERE imageRefId = $refId") ;

while($row2 = mysql_fetch_assoc($resultCount)) {

	if($row2['liked'] == 1) $likers++ ;
	else $haters++ ;

}

if($haters > $likers && $likers != 0) $rating = round(($haters / $likers), 2) ;
if($likers > $haters && $haters != 0) $rating = round(($likers / $haters), 2) ;

array_push($scanned, $refId) ;
array_push($ratedIds, $refId) ;
array_push($ratedRates, $rating) ;
}

}
print_r($ratedIds) ;
echo "<br />" ; // BOTH ARRAYS LINE THE IMAGE UP WITH ITS CORRESPONDING RATING
print_r($ratedRates) ;

[kickstart]

Hi

 

I put the brackets in the wrong place

 

$result = mysql_query("SELECT a.*, SUM(IF(liked=0),1,0) AS HateCount, SUM(IF(liked=1),1,0) AS LikeCount

FROM images a

LEFT OUTER JOIN image_ratings b

ON a.id = b.image_ratings

GROUP BY imageRefId

ORDER BY LikeCount LIMIT 0, 9")or die(mysql_error()) ;

 

Should be

 

$result = mysql_query("SELECT a.*, SUM(IF(liked=0,1,0)) AS HateCount, SUM(IF(liked=1,1,0)) AS LikeCount

FROM images a

LEFT OUTER JOIN image_ratings b

ON a.id = b.image_ratings

GROUP BY imageRefId

ORDER BY LikeCount LIMIT 0, 9")or die(mysql_error()) ;

 

I suspect that imageRefId in the group by should be the column you are joining on (but not sure of your table layouts to be certain)

 

The fields in the GROUP BY should match the non aggregate fields in the SELECT clause.

 

All the best

 

Keith

[imperium2335]

Thanks very much! It's working now