CODEWITHSUNDEEP
mysqlsql
0xSina
0xSina

Reputation: 21553

SQL simplication

I have this SQL statement:

SELECT
       (CASE
            WHEN EXISTS
                   (SELECT *
                    FROM votes
                    WHERE votes.user_id = 0
                      AND votes.post_id = posts.id
                      AND votes.vote = 0) THEN 0
            WHEN EXISTS
                   (SELECT *
                    FROM votes
                    WHERE votes.user_id = 0
                      AND votes.post_id = posts.id
                      AND votes.vote = 1) THEN 1
            ELSE 2
        END) AS vote_by_me ,
       posts.*
FROM `posts`

Is there a way I can do this in a DRY manner? Both select statements are almost the same, would be nice to factor them out some way.

Thanks

Upvotes: 3

Views: 49

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726559

Yes, you can select votes.vote directly, like this:

SELECT
    COALESCE(
        (
        SELECT MIN(votes.vote)
        FROM votes
        WHERE votes.user_id = 0 AND votes.post_id = posts.id
        AND votes.vote in (0, 1)
        GROUP BY votes.user_id, votes.post_id
        )
    ,   2
    ) AS vote_by_me
,   posts.*
FROM `posts

If a post cannot have multiple votes by the same user, you could eliminate the GROUP BY, like this:

SELECT
    COALESCE(
        (
        SELECT votes.vote
        FROM votes
        WHERE votes.user_id = 0 AND votes.post_id = posts.id AND votes.vote in (0, 1)
        )
    ,   2
    ) AS vote_by_me
,   posts.*
FROM `posts

Upvotes: 1

Digital Chris
Digital Chris

Reputation: 6202

SELECT
       (CASE                
            WHEN EXISTS
                   (SELECT *
                    FROM votes
                    WHERE votes.user_id = 0
                      AND votes.post_id = posts.id
                      AND votes.vote IN (0,1) )THEN votes.vote
            ELSE 2
        END) AS vote_by_me ,
       posts.*
FROM `posts`

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This would seem to simplify the query:

SELECT (CASE WHEN v.votes0 > 0 THEN 0
             WHEN v.votes1 > 0 THEN 1
             ELSE 2
        END) AS vote_by_me,
       p.*
FROM posts p left outer join
     (select v.post_id, sum(v.vote = 1) as vote1, sum(v.vote = 0) as vote0
      from votes v
      where v.user_id = 0
      group by v.post_id
     ) v
     on p.post_id = v.post_id;

The bad news is that if you have an index on votes(user_id, post_id, votes) then your original form will probably have better performance.

EDIT:

The following formulation might perform well and sort-of simplify the query:

SELECT (CASE (SELECT min(vote)
              FROM votes
              WHERE votes.user_id = 0 AND
                    votes.post_id = posts.id
             )
             WHEN 0 then 0
             WHEN 1 then 1
             ELSE 2
        END) AS vote_by_me,
       posts.*
FROM `posts`;

Upvotes: 1

Related Questions