CODEWITHSUNDEEP
phpmysql
user5899631
user5899631

Reputation:

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!

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

Upvotes: 3

Views: 1375

Answers (1)

xQbert
xQbert

Reputation: 35333

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.

---UPDATE:

You do realize the A.ID you added will be a random ID from the activity table for the uuidpost right?

I've amended this to include if the current user (145?) "liked" the post by adding a new column called CurrentUserLiked to the subquery.

I had to make an assumption that the column name for userID on the activity table is UserID; change it as necessary. I also assumed current user was defined as 145 and this would ultimately be passed in via php. along with the other two 145's listed.

I'm not sure what you're trying to do with the A.uuidPost for current user so I left it alone for now.

SELECT Posts.id,
    Posts.uuid,
    Posts.caption,
    Posts.path,
    Posts.date,
    USERS.id,
    USERS.username,
    USERS.fullname,
    USERS.profileImage,
    coalesce(A.LikeCNT,0),
IF( A.uuidPost IS NOT NULL , 1, 0 ) AS CurrentUser,
    A.CurrentUserLiked
FROM Posts 
INNER JOIN USERS 
ON Posts.id = 145 
AND USERS.id = 145
LEFT JOIN (SELECT COUNT(A.uuidPost) LikeCNT, A.UUIDPost, A.id, sum(CASE WHEN A.USERID = 145 then 1 else 0 end) as CurrentUserLiked
           FROM Activity A
           WHERE type = 'like' 
           GROUP BY A.UUIDPOST) A
on A.UUIDPost=Posts.uuid
AND A.id = Posts.id
WHERE Posts.id = 145
ORDER BY date DESC
LIMIT 0, 5

Upvotes: 4

Related Questions