Join - order by vote-sum and check if user has voted 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.
Schema

Your schema isn't well normalized: you shouldn't have network_name in the comments table when you already have network_id.

Non-standard GROUP BY

Standard SQL requires that when the query has a GROUP BY, all columns being selected must either be mentioned in the GROUP BY clause or be an aggregate function.  However, MySQL is sloppy, and allows non-sensical queries like yours that violate that rule, unless you enable the standard behaviour explicitly by setting the ONLY_FULL_GROUP_BY option on the server.  (When you write SELECT *, …, which created_at timestamp would you expect to appear in the results, for example?)  

You wrote GROUP BY ( v.`foreign_id`), but GROUP BY c.id would make it more obvious that you want to group by comment.

Subselect

Subselects within the SELECT column list are a bit nasty, and can nearly always be expressed better.  In this case, I think that a COUNT() of votes whose user_id is 123 would do the job — and it doesn't require another join.

Indentation

More meaningful indentation would make the query easier to read.  You don't have to indent it the way I suggest, but you should do something better than nearly no indentation at all.

Suggested solution

SELECT c.id AS comment_id
     , SUM(v.value) AS votesum
     , COUNT(WHEN v.user_id = 123 THEN 1 END) &gt; 0 AS user_voted_already
    FROM comments as c
        LEFT JOIN votes as v
            ON v.foreign_type = 'comment'
            AND v.foreign_id = c.id
    WHERE c.network_id = 3183
    GROUP BY c.id
    ORDER BY votesum DESC;