PostgreSQL query in two tables with CASE

Question

I have two table publications and publication_likes, where I have a many to many relationship and this fields: user_id, pub_id, created_at.

Users have a profile and on that profile they have publications. I need to get a specific user's publications, when his profile is requested(that's easy). Also I need to know which of those publications the user who is logged in likes.

Example: user_a is the profile owner and has publications, user_b is logged in and visits user_a profile. Server loads user_a publications and tells user_b which of those user_a publications has liked

Before asking I tried thinking and found a solution, but I don't know if it is correct

 SELECT p.id, 
      p.user_id as creator, 
      p.p_content, 
      CASE WHEN pl.user_id = $1 THEN 1 
           ELSE 0 
       END as liked from publications p
  LEFT JOIN publications_likes pl on p.id = pl.pub_id 
  WHERE p.user_id = $2;

Please tell me if you think this is right or not and if there is another way to do the same.

NOTE: I know the title it is not really specific but I could not think of other. If you can make it better, feel free to do it.

I'm confused by which of that publications the user who is logged in likes. Do you mean user_a is logged in & looking at the profile of user_b, and you need to fetch all the publications liked by user_b to show to user_a or publications by user_b that user_a has liked. — Haleemur Ali
– Haleemur Ali, Commented Jul 16, 2018 at 3:02
yes, I have user_a who has some publications, and user_b who may have liked user_a publications and is logged in. I need to get user_a publications and at the same time to know which from that publications user_b likes. :-) — Ángel Barrios
– Ángel Barrios, Commented Jul 16, 2018 at 4:40
your statement is giving you the correct result, although you could simplify the case statement as this answer suggests. just ensure that $1 = user who is viewing profile and liked publications & $2 = user who created publication — Haleemur Ali
– Haleemur Ali, Commented Jul 16, 2018 at 5:08
Yeah, I already simplified it. Do you consider this is a good and optimized way to do it? do you have any recommendation? :-) — Ángel Barrios
– Ángel Barrios, Commented Jul 16, 2018 at 5:41
yes, this is a good way to write this query. IMO the suggested answer should be accepted — Haleemur Ali
– Haleemur Ali, Commented Jul 16, 2018 at 13:42

ajxs · Accepted Answer · 2018-07-16 06:20:20Z

4

EDIT: Updated to reflect clarification in comments.

You can get the correct result set without needing to use a CASE statement.

 SELECT p.id, 
       p.user_id as creator, 
       p.p_content, 
       pl.id IS NOT NULL AS liked 
   FROM publications p
   LEFT JOIN publications_likes pl ON p.id = pl.pub_id AND pl.user_id = $1
   WHERE p.user_id = $2;

You'll need to use two conditions on the join to ensure you're finding the correct pl row, since you're joining to a junction table for a M:N relationship. Otherwise you don't really know which pl you'll actually join to, and you may end up with false-negatives for liked.

edited Jul 16, 2018 at 6:20

answered Jul 16, 2018 at 3:39

ajxs

3,7082 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ángel Barrios Over a year ago

Thanks, The user who likes the publications is not the profile owner. I have the owner of the profile: user_a. There is a user_b who likes user_a publications. I need to get publications of user_a and then or at the same time to know which of those user_a publications is liked by user_b.

ajxs Over a year ago

Thanks for the clarification, edited my answer to reflect this.

Collectives™ on Stack Overflow

PostgreSQL query in two tables with CASE

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related