How to include user in MySQL query?

Question

I currently have a query that returns a list of every post that exists, its comments (concatenated), and how many likes it has.

I would like to include a column that that indicates whether or not the user has upvoted/liked this specific post. The structure of the relevant tables are as follows:

likes (id, elementID, googleID)
elements (id, googleID, time, title, body, type)
comments (id, elementID, googleID, body)

So how would I go through the likes, determine if one of them has the user's ID (dynamically put into the query via Node.JS), and then create a column indicating a simple true or false?

SELECT E.id, E.time, E.title, E.body, E.type, C.comments, E.googleID, L.likeCount
FROM elements E
LEFT JOIN(
    SELECT elementID, GROUP_CONCAT(body SEPARATOR '|-|') AS comments
    FROM comments
    GROUP BY elementID
) C on C.elementID = E.id
LEFT JOIN (
    SELECT elementID, COUNT(id) AS likeCount
    FROM likes
    GROUP BY elementID
) L ON L.elementID = E.id;

Some sample data will help. Try to provide a SqlFiddle so we can understand the problem better and give you an answer much faster. That query works and you want help to update that? or you want create a new query ? googleID is the user? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Aug 30, 2015 at 17:48
googleID is the user. I'd like to add to this query to add another column that indicates whether or not the user has liked the post (which is an element). — Connorelsea
– Connorelsea, Commented Aug 30, 2015 at 17:51

Michael Berkowski · Accepted Answer · 2015-08-30 18:03:27Z

1

Add a LEFT JOIN against likes, passing the user's googleID into the join's ON clause. If values from that join are non-null, then the user has liked the post. If null, the user has not liked it.

Note that your first subquery join is unnecessary. That can be taken care of directly in the SELECT list, and by adding all the other selected columns to the GROUP BY clause (actually that isn't even necessary in MySQL owing to its default behavior of group by lenience, but is a good habit to be in since other RDBMS require it)

SELECT
  E.id,
  E.time,
  E.title,
  E.body,
  E.type,
  -- This can be moved to the outer SELECT
  GROUP_CONCAT(C.body SEPARATOR '|-|') AS comments,
  E.googleID,
  L.likeCount,
  -- Conditional statement to match non-nulls in the
  -- left joined likes table, indicating whether the
  -- user has liked the post
  CASE
    -- has a like, return true
    WHEN Luser.id IS NOT NULL THEN true
    -- does not have a like, return false
    ELSE false
  END AS userLiked
FROM elements E
-- Convert this to a plain left join instead of subquery
LEFT JOIN comments C ON C.elementID = E.id
-- Left join against likes, strictly matching the user id
LEFT JOIN likes Luser
  ON Luser.elementID = E.id
  -- Pass the user id into the join condition.
  AND Luser.googleID = <the user's id>
LEFT JOIN (
    SELECT elementID, COUNT(id) AS likeCount
    FROM likes
    GROUP BY elementID
) L ON L.elementID = E.id;
-- Put the other columns into GROUP BY
-- instead of another left join subquery
GROUP BY
  E.id,
  -- MySQL will let you get by without the rest of these
  -- but it is good practice to use them. Other RDBMS would
  -- not allow them to be omitted...
  E.time,
  E.title,
  E.body,
  E.type,
  E.googleID
  likeCount,
  userLiked

If you prefer to go back to using your subquery join for the GROUP_CONCAT(), you may revert to that and remove the outer GROUP BY, but it may not be as fast as this method.

edited Aug 30, 2015 at 18:03

answered Aug 30, 2015 at 17:53

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

2 Comments

Connorelsea Over a year ago

It says column body in field list is ambiguous.

Michael Berkowski Over a year ago

Ah, because of the comment body. Add a C alias. GROUP_CONCAT(C.body SEPARATOR '|-|') AS comments,

Community · Accepted Answer · 2017-05-23 12:14:39Z

Add a conditional SUM this will be 0 or something >0 in case user like it.

SELECT 
     E.id, E.time, E.title, E.body, E.type, 
     C.comments, E.googleID, L.likeCount, L.user_likeit
FROM elements E
LEFT JOIN(
    SELECT elementID, GROUP_CONCAT(body SEPARATOR '|-|') AS comments
    FROM comments
    GROUP BY elementID
) C on C.elementID = E.id
LEFT JOIN (
    SELECT elementID, COUNT(id) AS likeCount, SUM( CASE 
                                                     WHEN googleID =@userID THEN 1
                                                     ELSE 0
                                                   END
                                                 ) as user_likeit
    FROM likes
    GROUP BY elementID
) L ON L.elementID = E.id;

I google and found Conditional Count/Sum in Select and looks like can also work

sum(googleID = @userID)

Collectives™ on Stack Overflow

How to include user in MySQL query?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related