Only return value when row exists in SQL

Question

I have a sql query where I want to check if a certain post is has been liked already by the current user.

I do this currently by checking the existing likes for the post and whether one of them has the current user id as user id.

However, I have trouble doing this in a query. I am using PostgresSQL btw.

When I do the following:

CASE WHEN likes.user_id = 5 THEN 1 END AS "liked"

I am required to add likes.user_id in the GROUP BY clause which results in multiple results per post since it groups them by likes.

I also try to do it with a count wrapped around it but since I use Knex and express, the ints get converted to strings, which I don't want.

Is there something I'm not seeing or doing wrong?

The full query in PostgresSQL is:

select 
    posts.id, 
    posts.created_at, 
    posts.text, 
    posts.title,
    count(DISTINCT likes.id) as "likeCount",
    count(DISTINCT comments.id) as "commentCount",
    count(CASE WHEN likes.user_id = 5 THEN 1 END) AS "liked"
from updates
LEFT JOIN comments ON updates.id = comments.update_id
LEFT JOIN likes ON updates.id = likes.update_id
WHERE posts.user_id = 5
GROUP BY posts.id, likes.user_id
ORDER BY updates.created_at

The result I am now getting is (simplified):

╔════╤═══════════════════════════════╤═══════════════╤════════════╤═══════╗
║ id │ created_at                    │ comment_count │ like_count │ liked ║
╠════╪═══════════════════════════════╪═══════════════╪════════════╪═══════╣
║ 40 │ 2017-09-21 11:53:11.064774+02 │ 1             │ 0          │ 0     ║
╟────┼───────────────────────────────┼───────────────┼────────────┼───────╢
║ 40 │ 2017-09-21 11:53:11.064774+02 │ 1             │ 0          │ 0     ║
╟────┼───────────────────────────────┼───────────────┼────────────┼───────╢
║ 40 │ 2017-09-21 11:53:11.064774+02 │ 1             │ 0          │ 1     ║
╚════╧═══════════════════════════════╧═══════════════╧════════════╧═══════╝

user_2270762 · Answer

Use conditional aggregation:

MAX(CASE WHEN likes.user_id = 5 THEN 1 ELSE 0 END) AS liked

This gives you 1 if there is at least one like from user 5, else 0.

(And this should result in an integer. If your framework is in the way, you may be able to force this by MAX(...)::integer AS liked.)

user_8378419 · Answer

If you have a reliable way to sort them you can use

CASE WHEN likes.user_id = 5 THEN 1 END AS "liked"
GROUP BY yourcondition
LIMIT 1

This will get only the first row returned, so if you have a way to ensure the record you want will be returned first it may work for you. Other than that it's hard to help you without more information.

Only return value when row exists in SQL

2 Answers