开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜