Jack Jack - 1 month ago 10
MySQL Question

MYSQL Query - Get current user's posts and the user's he's following posts

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:

SELECT P.id,
P.caption,
P.date,
U.id,
U.fullname,
U.username,
F.IdOtherUser
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


145 = current user.

Activity.IdOtherUser = the user '145' is following

Activity.id = will be '145' the current user

If anyone can help me sort this I'd appreciate it alot!

Cannot seem to understand it fully, as I am quite new to MYSQL...

Answer

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

Assumptions:

  1. Activity.id field represents the user, who follows the other user. If not, you need to change the field name to the appropriate one.

  2. Invented the userid field of Posts table representing the user who posted the post. Pls use the correct field name in its place.