3
\$\begingroup\$

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.

\$\endgroup\$
0
1
\$\begingroup\$

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) > 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;
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.