How to get following users post feed that has visibility condition?

Question

I am trying to make an SQL query on postgresql database that should give me posts feed of my posts and posts of users that I follow and my friends ( which is mutual follow ) I have this table structure

users table
id  username
1   me
2   user2
3   user3
4   user4

relationships table
id follower_id following_id
1   1           2 // me following user2
2   2           1 // user2 also following me so we are friends
3   3           1

posts table
id user_id post visibility
1   2      post1  friends
2   2      post2  public
3   2      post3  public
4   1      post4  public
5   3      post5  public

select p.*
from posts p
where (
    visibility = 'friends' and 
    user_id in (select following_id from relationships r1 where r1.follower_id = 1) and
    user_id in (select follower_id from relationships r2 where r2.following_id = 1)
) or (
    visibility = 'public' and 
    user_id in (select following_id from relationships r3 where follower_id = 1)
)

and this is the query I made which is giving a result but for me it is not an efficient query I need a better query to get the result as

user id 1's feed should be

id user_id post   visibility
1   2      post1  friends
2   2      post2  public
3   2      post3  public
4   1      post4  public

Answer 1

Look next query:

select p.*
from posts p
join (
    -- get followers and friends
    select distinct relationships.*, coalesce(friends.follower_id, 0) as friend_id
    from relationships
    -- join self for check is follower friend
    left join relationships friends on 
        relationships.following_id = friends.follower_id and
        relationships.follower_id = friends.following_id
    where relationships.follower_id = 1
) followers on (
    visibility = 'friends' and followers.friend_id = p.user_id or
    visibility = 'public' and followers.following_id = p.user_id 
);

Try this query on SQLize.online