CODEWITHSUNDEEP
mysqlsqldatabaseselect
rel1x
rel1x

Reputation: 2441

Query to select posts and mark if some of them liked by current user

I have 3 tables: user, post, like. When I select posts from post I need to mark if one of them was already liked by user.id = 1. Like in social networks, posts on the feed that you already liked - marked. But I am novice in databases and don't understand how can I update my query to mark if post was liked by user.id = 1.

My current select looks like this:

SELECT post.id, post.text, post.datetime, user.username,
    (SELECT COUNT(userId) FROM `like` WHERE postId = post.id) AS likes
                    FROM post
                      INNER JOIN user ON user.id = post.authorId;

I also wrote a query to select a data from like by user.id and post.id but don't know how to join my two selects and mark a post:

SELECT * from `like` as lk where lk.userId = 1 and lk.postId = 5;

My current result of first query is:

{
    "username": "monica",
    "datetime": "2017-05-02T16:11:31.000Z",
    "id": 3,
    "likes": 2,
    "text": "Do pariatur anim aliqua adipisicing sit non."
}

But I need, if I ask posts for user.id=1:

{
    "liked": true, // means that user.id == 1 already liked the post
    "username": "monica",
    "datetime": "2017-05-02T16:11:31.000Z",
    "id": 3,
    "likes": 2,
    "text": "Do pariatur anim aliqua adipisicing sit non."
}

My tables:

CREATE TABLE IF NOT EXISTS user (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(15),
    // ...
);

CREATE TABLE IF NOT EXISTS post (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    text VARCHAR(500),
    authorId INT UNSIGNED,
    // ...
);

CREATE TABLE IF NOT EXISTS `like` (
  postId INT UNSIGNED,
  userId INT UNSIGNED,
  PRIMARY KEY (postId, userId)
);

Upvotes: 1

Views: 1979

Answers (1)

ghenghy
ghenghy

Reputation: 361

I'm a little rusty, so there's likely a more efficient way to do this, but it works and you can adapt it as needed. The following gets all posts and for each post marks if the current user liked it and how many people liked it in total:

SELECT 
  exists(select 1 from `like` li where li.postId = p.id and li.userId = u.id limit 1) as liked
  , u.username
  , p.id as postId
  , p.text
  , (select count(distinct l.userId) from `like` l where l.postId = p.id) as liked
FROM
  user u,
  post p
WHERE
  u.id = 2;

Upvotes: 4

Related Questions