Jack Jack - 1 month ago 5
MySQL Question

MYSQL Query - Select posts and like counts for each post

I am trying to get all the posts that belong to a certain user and then the like counts for each of those posts.

Here I get the all the posts that belong to a certain user:

SELECT Posts.id,
Posts.uuid,
Posts.caption,
Posts.path,
Posts.date,
USERS.id,
USERS.username,
USERS.fullname,
USERS.profileImage
FROM A.Posts JOIN A.USERS ON
Posts.id = 145 AND USERS.id = 145 ORDER by date DESC
LIMIT 0, 5


And here is how I query the likes for each post:

SELECT COUNT(uuidPost)
FROM Activity
WHERE type = "like" AND uuidPost = "FA4C8196-CEA3-4373-94B2-59F387BB1906"


Not sure how to combine them?

If anyone can help or give me tips on the queries, I'd appreciate all the help!

Thanks in advance!

Answer

One way to do this would be to use an inline select... using a correlated query.

SELECT Posts.id,
    Posts.uuid,
    Posts.caption,
    Posts.path,
    Posts.date,
    USERS.id,
    USERS.username,
    USERS.fullname,
    USERS.profileImage,
   (SELECT COUNT(A.uuidPost)
    FROM Activity A
    WHERE type =  "like" 
      AND A.uuidPost = Posts.uuid) as LikeCNT
FROM Posts 
INNER JOIN USERS 
  ON Posts.id = 145 
 AND USERS.id = 145
ORDER BY date DESC
LIMIT 0, 5

Though I'm not a big fan of this on large datasets... I generally prefer...

   SELECT Posts.id,
        Posts.uuid,
        Posts.caption,
        Posts.path,
        Posts.date,
        USERS.id,
        USERS.username,
        USERS.fullname,
        USERS.profileImage,
        coalesce(A.LikeCNT,0)
    FROM Posts 
    INNER JOIN USERS 
      ON Posts.id = 145 
     AND USERS.id = 145
    LEFT JOIN (SELECT COUNT(A.uuidPost) LikeCNT, A.UUIDPost
        FROM Activity A
        WHERE type =  "like" 
        GROUP BY A.UUIDPOST) A
     on A.UUIDPost=Posts.uuid
    ORDER BY date DESC
    LIMIT 0, 5

As the engine generates the count data set once instead of having to execute for each UUID. we have to use a coalesce as a UUID for a post may have no likes thus no record exists, thus a null value on the left join. so to show 0 we need to take the 1st non-null value, either a number or use 0.