Search code examples
javascriptmysqlsqlmariadbsubquery

mysql conditional subquery with joins or case

I have 3 tables Project, ProjectLikes and User.

When a logged in user is browsing through the list of Projects I have to identify if the current user liked the project i.e there is a row storing user id, project id in ProjectLikes table.

But i don't know how to do it. This is how i do it to fetch the projects. I can't figure out how to solve this.

SELECT 
   id,
   title,
   createdAt,
FROM
   Project
ORDER BY 
   createdAt desc 
LIMIT 10

In ProjectLikes i am storing project id and user id. and the output that i want is an additional row which says this project is liked by the user (i already have the user id since user is logged in)

Solution

  • You could use exists:

    select p.*, 
    (exists (select 1 from projetlikes pl where pl.projectid = p.id and pl.userid = ?)) is_liked_by_user
from project p

    This adds a 0/1 value to each row that indicates whether the current user likes the project. This assumes that table projectlikes has columns projectid and userid. The id of the logged in user is represented by ? in the query.