CODEWITHSUNDEEP
mysqlsql
kevin richard
kevin richard

Reputation: 91

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:

  1. Don't want see Double post ( like Hello )
  2. 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

Upvotes: 0

Views: 569

Answers (1)

eshirvana
eshirvana

Reputation: 24593

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

Upvotes: 1

Related Questions