How to retrieve all post and verify if user liked the post with an SQL query

I would like know how to retrieve all post and verify which one I liked with a dynamic UserId

the query that I tried is :

SELECT
    posts.id,
    posts.content,
    posts.user_id,
    users.pseudo,
    posts.score,
    posts_score.user_id as 'liked'
FROM
    posts
    INNER JOIN users ON posts.user_id = users.id
    LEFT JOIN posts_score ON posts_score.posts_id = posts.id
ORDER BY
    posts.date DESC
LIMIT
    100

id	content	user_id	pseudo	score	liked
3	Hhhkdk	83	Bxbbxnx	0	83
2	Heyy	83	Bxbbxnx	1	68
1	Hello	68	Test	0	68
1	Hello	68	Test	0	83

I think that I should introduce the ID : 68 in the request but I don’t know how

Two issue here:

Don’t want see Double post ( like Hello )
I only want see results with the userId 68 and If I didn’t liked one of these post, I would like see null on Liked column.

I don’t want to see 83 in liked column, only 68 OR null if I didn’t liked the post.

I would like this

id	content	user_id	pseudo	score	liked
3	Hhhkdk	83	Bxbbxnx	0	NULL
2	Heyy	83	Bxbbxnx	1	68
1	Hello	68	Test	0	68

Need your help friends

Answer

here is how you can do it :

SELECT distinct
    posts.id,
    posts.content,
    posts.user_id,
    users.pseudo,
    posts.score,
    posts_score.user_id as 'liked'
FROM
    posts
INNER JOIN users 
   ON posts.user_id = users.id
LEFT JOIN posts_score 
   ON posts_score.posts_id = posts.id
   AND posts_score.user_id = 68
ORDER BY
    posts.date DESC
LIMIT  100

Advertisement

Answer