I have a vote and a comment table. Now I want to display all comments and order them by votes-sum. Sum because some votes can have a higher value than others.
Therefore I want to display if the user has voted already on a comment. So I need to get that value too.
This is my actual query:
SELECT *, SUM(v.`value`) as votesum, ( SELECT 1 FROM votes WHERE user_id = 123 and id = v.id ) as user_voted_already FROM comments as c LEFT JOIN votes as v ON c.id = v.`foreign_id` and v.`foreign_type` = 'comment' WHERE c.`network_id` = 3183 GROUP BY ( v.`foreign_id`) ORDER BY votesum DESC
It's working but I'm just wondering if this is the right way (or: the fastest way).
These are the table definitions:
votes (polymorphic): id user_id guest_ip (for guest votes) foreign_id foreign_type category value comment created_at updated_at comments id network_id network_name
… and many others that I don't think are needed for the question.