# 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
WHERE user_id = 123 and id = v.id

ON c.id = v.foreign_id and v.foreign_type = 'comment'
WHERE c.network_id = 3183
GROUP BY ( v.foreign_id)


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
foreign_id
foreign_type
category
value
comment
created_at
updated_at

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