I'm doing a clone of Twitter using PostgreSQL. The users can write posts and they can share them. There is a timeline for each user, where it shows the posts and the shares of the people he follows. I also have to show the difference between a shared post (retweet) and a regular one by the original author. I have the following problem when joining the tables:
Post
post_id | user_id | text
1 1 text
Shares
post_id | user_id
1 2
Join_result
Post.post_id | Post.user_id | Post.text | Shares.post_id | Shares.user_id
1 1 text 1 2
Then I filter by Post.user_id or Shares.user_id. However, with this result, I don't know if I'm showing the post because it is a user 2 sharing or user 1 post. A good solution to me, it would be this join table:
Join_result
Post.post_id | Post.user_id | Post.text | Shares.post_id | Shares.user_id
1 1 text null null
1 1 text 1 2
Now I could filter correctly:
(Post.user_id in following and Share.user_id is NULL) or Share.user_id in followings
In this example, if the user follows user 1, it returns the first row; if he follows user 2 I get the second one and if he follows the two users, returns the two rows.
I've already solved this problem using a UNION query but I'd like to know if there is another solution (and better).
EDIT the query:
SELECT p0* f1.*, FROM "posts" AS p0
LEFT OUTER JOIN "forwards" AS f1 ON f1."post_id" = p0."id"
INNER JOIN (SELECT id FROM users AS u0
INNER JOIN follows AS f1 ON (f1.follower_id = $1) AND (f1.following_id = u0.id) UNION
SELECT id FROM users AS u1 WHERE (u1.id = $1)
) AS f3 ON (p0."author_id" = f3."id") OR (f1."user_id" = f3."id") ORDER BY p0."inserted_at" DESC
