[SOLVED] Order by top rated

[balkan7]

hi guys, i need help to sort pictures with top rated.

 

datebase: photos

photo_id

photo_name

photo_thumb

photo_file

photo_date

 

datebase: ratings

rating_id

rating_item_id

rating_type

rating_user

rating_vote

rating_datestamp

[Mchl]

What did you come up with so far?

[kickstart]

Hi

 

Looks like you need to do a join of the 2 tables, and count the number of resulting rows grouped by the entries on the photos table.

 

Something like this:-

 

SELECT photo_id, photo_name, photo_thumb, photo_file, photo_date, COUNT(*) AS RatingCount
FROM photos a JOIN ratings b ON a.photo_id = b.rating_item_id
GROUP BY photo_id, photo_name, photo_thumb, photo_file, photo_date
ORDER BY RatingCount

 

All the best

 

Keith

[balkan7]

Thanks for your post,

but forgot that I pasted my query that should be little to be changed because not show me toprated.

 

SELECT tp.*, SUM(tr.rating_vote) AS sum_rating, COUNT(tr.rating_item_id) AS count_votes
				FROM ".DB_PHOTOS." tp
				LEFT JOIN ".DB_RATINGS." tr ON tr.rating_item_id = tp.photo_id AND tr.rating_type='P'
				GROUP BY photo_id ORDER BY count_votes DESC LIMIT ".$_GET['rowstart'].",".$settings['thumbs_per_page']

Hi

 

Looks like you need to do a join of the 2 tables, and count the number of resulting rows grouped by the entries on the photos table.

 

Something like this:-

 

SELECT photo_id, photo_name, photo_thumb, photo_file, photo_date, COUNT(*) AS RatingCount
FROM photos a JOIN ratings b ON a.photo_id = b.rating_item_id
GROUP BY photo_id, photo_name, photo_thumb, photo_file, photo_date
ORDER BY RatingCount

 

All the best

 

Keith

[balkan7]

i have to try whit this one but again whiout result:

SELECT tp.*, ta.*, SUM(tr.rating_vote) AS sum_rating, COUNT(tr.rating_item_id) AS count_votes
	FROM ".DB_PHOTOS." tp
	LEFT JOIN ".DB_PHOTO_ALBUMS." ta USING (album_id)
	LEFT JOIN ".DB_RATINGS." tr ON tr.rating_item_id = tp.photo_id AND tr.rating_type='P'
	GROUP BY tp.photo_id ORDER BY count_votes DESC LIMIT ".$_GET['rowstart'].",".$settings['thumbs_per_page']

[kickstart]

Hi

 

Do you mean no result in that it brings nothing back, or no result in that it abends?

 

All the best

 

Keith

[balkan7]

This returns the results but not as a top rated.

 

Hi

 

Do you mean no result in that it brings nothing back, or no result in that it abends?

 

All the best

 

Keith

[kickstart]

This returns the results but not as a top rated.

 

Do you want to order by ORDER BY sum_rating rather than ORDER BY count_votes?

 

All the best

 

Keith

[balkan7]

Same result, i dont know where i wrong...

[kickstart]

Hi

 

Reduce the number of columns you are selecting. Not sure but suspect that MySQL might be getting confused by trying to group on one column while bringing back other columns which are not unique for the group by clause.

 

All the best

 

Keith

[balkan7]

I think I find solution.