CODEWITHSUNDEEP
phpmysql
max
max

Reputation: 3716

getting users with highest scores without having users id in the scores table

lets say i have a website , users register , upload their pictures and give score to each other pictures

here is 3 main tables 

users
id , username

images
id , image_name , user_id

scores 
voter_id , image_id

i want to get users who , their uploaded images have the highest scores (all together )... top users by score if you will

i think the best way to do this is change users table like

users
id , username , image_Scores

and calculate user score with each vote and store it in the image_Scores column , but i didn't wrote this website and it already has couple hounded thousand images and i'm not planing to do this in short time .

is there any way to get users with highest image score with current data structure ?

Upvotes: 1

Views: 77

Answers (2)

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6726

SELECT 
   u.id, u.username, COUNT(*) as image_Scores
FROM
   users u
       LEFT JOIN images i ON (u.id = i.user_id)
       LEFT JOIN scores s ON (i.id = image_id)
GROUP BY
   u.id, u.username
ORDER BY
   COUNT(*) DESC
LIMIT 3

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212412

SELECT u.id, 
       u.username,
       count(s.voter_id) as votes
  FROM users u
       images i,
       scores s
 WHERE i.user_id = u.id
   AND s.image_id = i.id
 ORDER BY 3 DESC
 LIMIT 10

Upvotes: 2

Related Questions