CODEWITHSUNDEEP
mysqljoin
vasanth.v
vasanth.v

Reputation: 213

mysql query in order to get the post ids like twitter homefeed which will show our posts and also our followings post

I am having 2 tables names posts and follows

the structure of posts table is

id | uid | text | time

follows table has the userid and the followers user id the structure of follows table is

uid | followingid

Now I need to write a query in order to get the post ids like twitter homefeed which will show our posts and also our followings post.

SELECT posts.id FROM posts 
INNER JOIN follows ON posts.uid = follows.followingid 
AND follows.uid = "'.$currentuser.'" OR posts.uid = "'.$currentuser.'"

but this is not working if there is no entry in the follows table.

Upvotes: 2

Views: 103

Answers (2)

kazinix
kazinix

Reputation: 30093

You basically want to get all posts of the current user and all posts of the user followed by the current user. So here's the code, I use union. First I select all the current user's post, next I select the posts of user followed by the current user.

select posts.id
from posts
where posts.uid = "'.$currentuser.'"

union all

select posts.id
from posts
inner join follows
on follows.followingid = posts.uid
where follows.uid = "'.$currentuser.'"

Upvotes: 4

Tudor Constantin
Tudor Constantin

Reputation: 26861

Try changing that with a LEFT JOIN, like:

SELECT posts.id FROM posts 
   LEFT JOIN follows ON posts.uid = follows.followingid 
      AND follows.uid = "'.$currentuser.'" 
WHERE posts.uid = "'.$currentuser.'"

Upvotes: 1

Related Questions