Mysql – Query to get reposts from people the user is following

MySQL

I have a table posts where all the posts by users are stored, the structure of this table is as follows.

| post_id | post_user | post_content | post_date |

The users table is as follows.

| user_id | username | user_joined |

User relationship table is as follows.

| follower | following | rel_date |

This is the query I am using to get the posts from people that user is following to show them.

SELECT * FROM posts 
WHERE post_user in (
    SELECT follower 
    from user_follow 
    where following=$loggedin_user)

Now I want users to share posts, for which I created a table repost_user as follows.

| repost_user | original_post_user | post_id | repost_date |

Best Answer

SELECT p.*,r.repost_userr.repost_date,IF(r.post_id>1,'true','false') FROM posts p 
left join repost_user r on p.post_id = r.post_id  
left join user_follow u on p.post_user = u.follower
where following=$loggedin_user;

Regarding the performance implications of using union all in place of joins, please see this answer on Stack Overflow:

Inner Join versus Union All

Related Question