Reputation: 21
I want to show all the posts from the people I am following including my own posts too. While it is working as intended, I have a problem: I get the same posts multiple times.
Here is my query:
SELECT posts.id, posts.body, posts.posted_at, posts.postimg, posts.likes, users.`username`
FROM users, posts, followers
WHERE (posts.user_id = followers.user_id OR posts.user_id = :userid)
AND users.id = posts.user_id
AND follower_id = :userid
ORDER BY posts.posted_at DESC;
Can anyone help me? Thank you in advance.
Upvotes: 0
Views: 88
Reputation: 31832
The reason is in this condition:
WHERE (posts.user_id = followers.user_id
OR posts.user_id = :userid)
For every user you are following, you will get all your own posts, thus creating duplicates of own posts.
You should use a UNION (ALL) query. To avoid the full query duplication, you can first select all user IDs in a subquery:
SELECT followers.user_id
FROM followers
WHERE followers.follower_id = :userid
UNION ALL SELECT :user_id
Then join it with your tables:
SELECT posts.id,
posts.body,
posts.posted_at,
posts.postimg,
posts.likes,
users.`username`
FROM (
SELECT followers.user_id
FROM followers
WHERE followers.follower_id = :userid
UNION ALL SELECT :user_id
) uids
JOIN users ON users.id = uids.user_id
JOIN posts ON posts.user_id = uids.user_id
Upvotes: 1
Reputation: 1
You should try to use:
Select DISTINCT(fields)
From....
Where ...
The DISTINCT will show only one row for every equal results.
I think that your query is working like a cross join.
I hope this helps you, and sorry for my english.
Upvotes: 0