MYSQL Query - Select posts and like counts for each post

Question

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

Are we to assume `activity.uuidPost = Posts.UUID`? if not, How does activity relate to Posts or users? — xQbert, Oct 25 '16 at 12:49
@xQbert if there's any likes for the posts that are fetched then there will be the Posts.uuid in activity.uuidPost...If not the count should return nil(0) — , Oct 25 '16 at 12:52
Does your Posts table have a column referencing the Users table? Something like `author_id` or `user_id`? — Armin Sam, Oct 25 '16 at 12:54
What is the A.Posts, and A.Users? on the from clause for; different schema? — xQbert, Oct 25 '16 at 12:59

xQbert · Accepted Answer · 2016-10-25T17:15:30.703

4

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

edited Oct 25 '16 at 17:15

answered Oct 25 '16 at 12:52

xQbert

34,733
2
41
62

Tried both and they both worked, though the second one wasn't working untill you just updated it....So you reccomend using the second one? – Oct 25 '16 at 13:01
I prefer the 2nd on larger data sets and I generally find it easier to read. – xQbert Oct 25 '16 at 13:04
Ok well thanks a lot, would of never got it without you help! +1 – Oct 25 '16 at 13:04
Quick question, I am using this in my IOS app. I have a php file with connects to my server, which contains the query you gave me. Unfortunately now when I run my app it isn't return anything even though it does work when I run it in the query tester(not sure of the name) on my DB. Any idea why or where I should look to fix this? – Oct 25 '16 at 13:16
If the IOS app is logging into the database with a different userID, than you're logging into the server with using the query window, then the `A.` may be the schema (Table owner) and necessary for the IOS app to access the DB Schema. (Put the `A.` back in front of the table names (all 3) and try again) – xQbert Oct 25 '16 at 13:27
I tried that and tried the other query I first had without A. and it worked! so I'll just have to keep trying different things – Oct 25 '16 at 13:34
Hmm, can't seem to sort it out... I have posted another question with more coe if your interested http://stackoverflow.com/questions/40242472/mysql-query-php-query-returning-nil-to-ios-app – Oct 25 '16 at 14:18
You obviously know what you're doing with mysql, So I was wondering if you could tell me if this is possible... Would I be able to do what you gave me but instead of getting the like counts, get the last 2 comment related to that post?? – Oct 25 '16 at 15:15
yes, it's a little more annyoing/complex in mySQL as mySQL doesn't support the row_number() analytic; but it can be done. We would have to use an inline view like we did in the 2nd' query and correlate it like we did in the first query, and then generate a row number for each group of comments for a post and limit the results based on that row number. So each uuidpost would have row numbers starting over at 1 each time and we would only reutrn those with a row number less than 3. (thus the 2 most recent comments) but this is getting into a different question. – xQbert Oct 25 '16 at 15:21
Several examples of this type of query already exist on the SO site. you would just have to put the pieces together. you want to simulate ROW_NUMBER analytic in mySQL. – xQbert Oct 25 '16 at 15:22
Thanks alot, I'll give it a go a let you know!! – Oct 25 '16 at 15:23
What about checking if currentUser has like the post? would I add another Left Join to the qeury you gave me and check if the user.id is in the Activity.id? – Oct 25 '16 at 15:26
I would add another field to the existing left join using a case statement something like `sum(CASE WHEN A.USERID = CurrentUserID then 1 else 0 end) as CurrentUserLiked` and then add `A.currentUserLiked` to the outer select. 0 would mean they haven't 1 would mean they have. where currentUserID is a variable like you have for the 145.. – xQbert Oct 25 '16 at 16:03
Yeh found a question on here fixed it! Thanks anyway! – Oct 25 '16 at 16:05
Actually your solutions sounds better... Would I add the sum() at the bottom of the left Join? After Group by? – Oct 25 '16 at 16:09
I have added my solution, I'll put it in my question...If you could give me you oppinion that would be great! – Oct 25 '16 at 16:28
Please do excuse me, but I didn't see your edit until now! Thanks alot for that actually currentUser is to check if the currentUser had liked it, but since you added A.CureentUserLiked I removed my way – Oct 26 '16 at 15:04
I'm actually trying to do the comments now, not had any lucky searching google... If you think you could spare some time I shall probably post a question soon! I'll link it to you if you're interested!! – Oct 26 '16 at 15:05

MYSQL Query - Select posts and like counts for each post

1 Answers1