I am trying to query to get the current user's posts and the posts of the people he is following. Like most social networks do in their home page.
I know how to query all of the current user's posts, but I am struggling to get the user's he's following posts.
I saw this other question Mysql select query for getting current user post and followed friend post
But the answer there isn't really helping me...
Here's is my query so far:
FROM USERS AS U
INNER JOIN Activity AS F
ON U.id = F.id
INNER JOIN Posts AS P
ON P.id = U.id OR P.id = F.IdOtherUser
WHERE P.id = 145
ORDER BY P.id DESC
To get all the posts a given user (id=145) and all the users it follows posts, along with the user details for each post, I would rewrite the query to use
union instead of the or, thus simplifying the logic. The 1st select gets the posts of the given user, the 2nd gets the posts of the users it is following:
(SELECT P.id as postid, P.caption, P.date, U.id as userid, U.fullname, U.username, FROM USERS AS U INNER JOIN Posts AS P ON P.userid = U.id WHERE U.id = 145) UNION (SELECT P.id, P.caption, P.date, U.id, U.fullname, U.username, FROM Activity AS A INNER JOIN USERS AS U ON A.IdOtherUser=U.id INNER JOIN Posts AS P ON P.userid = U.id WHERE A.id = 145) ORDER BY postid DESC
Activity.id field represents the user, who follows the other user. If not, you need to change the field name to the appropriate one.
Invented the userid field of Posts table representing the user who posted the post. Pls use the correct field name in its place.