What's the most efficient way to conditionally join two tables with random result order
The problem is very similar to the stack exchange voting system. I'd like to query an object table for a random object and also get back any votes a specified user has on that object.
objects table:
id
user开发者_开发问答_id
message
votes table:
object_id
user_id
vote_value
select * from objects order by RAND()
produces a random object, but how can I select a specific user's vote value for that object in the same query? It's possible that the user never votes on the result object so I can't simply do a join on id/object_id.
This query will print NULL
for user_id
and vote_value
if the user (in this case 1
) didn't vote on an object:
SELECT o.id, v.user_id, v.vote_value
FROM objects o LEFT OUTER JOIN votes v ON o.id = v.object_id AND v.user_id = 1
WHERE v.user_id = 1 OR v.user_id IS NULL
ORDER BY RAND() LIMIT 1;
精彩评论