Extending Query Statement to Include Users Following

Question

I have a query that I am getting post from a database. I have a 3 table join to get posts, profile picture, and user names. The current query is below.

 SELECT Posts.*, Profiles.ProfilePhoto, Users.Name, Users.Username
 FROM ((Posts
 INNER JOIN Profiles ON Posts.UserID = Profiles.UserID)
 INNER JOIN Users ON Posts.UserID = Users.ID)
 WHERE Posts.UserID = ? 
 ORDER BY Posts.CreateDate DESC

Right now I am getting the data from the user's posts (where posts.userID = ?). But i wanted to extend the query to also get the posts from users that the he/she follows.

That would need me adding something like another inner join like

INNER JOIN Follow ON Posts.UserID = Follow.Followed (WHERE Follow.Follower = ?) // ? being $userID;

But that isn't going to work because you can't throw that WHERE statement to the end of the inner join. So I was wondering what the most efficient way to add that into the current query would be?

Focus on finding a way, then work out how to make it more efficient — Strawberry
– Strawberry, Commented Mar 20, 2018 at 23:20
On rows showing the posts from users that the target userId follows, what should be in Profiles.ProfilePhoto, Users.Name, Users.Username columns? The target UserId's profile/user/username or the users who posted the posts? — kc2018
– kc2018, Commented Mar 20, 2018 at 23:29

Peter M · Accepted Answer · 2018-03-20 23:26:23Z

1

if you use a left join instead of an inner join, it might work

your left join would be something like

left join follow 
on posts.userid=follow.follwed
and follow.follower=?

be careful, though, because when you add that extra join it could give you extra rows (depending on how many rows you get from follow)

without seeing the diagram it is hard to say for sure

another way would do it would be maybe something like this?

WHERE (posts.userid=?
OR posts.userid in (select followed from follow
                    where follower=?))

answered Mar 20, 2018 at 23:26

Peter M

1925 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryne Over a year ago

Perfect! Thank you.. I always forget about the IN

Collectives™ on Stack Overflow

Extending Query Statement to Include Users Following

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related