Vfrdtyky

There are two users

The User 1 profile_id

3734387944

then the User 2 Profile_id

1421536173

there is a table named friends with a structure of

from_profile_id

to_profile_id

request

blocked_by

and this is the data and structure of users

var_id  profile_id  fullname             firstname   lastname

180     3734387944  John Louis Domincel  John Louis  Domincel  

181     1421536173  James Domincel       James       Domincel  

and this is the data and structure of friends

from_profile_id  to_profile_id  request  blocked_by

3734387944       1421536173     3        3734387944

and this is the data and structure of post

id   profile_id   text 

1    3734387944   POST OF USER 1

2    1421536173   POST OF USER 2

Using this Query, I can select all post of every user

SELECT * FROM post ORDER BY var_id DESC

But i want to filter it:
User 1 has blocked user 2 so both of them cannot receive each user post, Same Function. If not friends, But if they are friends the data will show up in each user.

Request Legend

1 = Friend Request

2 = Friends

3 = Blocked

I have tried this Another Query: But User 1 can see the user 2 posts while user 2 cannot see User 1 post if blocked only. I want to
modify it and make the two of them cannot see each user posts if Blocked and not Friends

SELECT u.*,f.*

FROM post AS u

LEFT JOIN friends AS f

ON f.blocked_by = u.profile_id AND

f.blocked_by <> "3734387944" AND

(

f.to_profile_id = "3734387944" OR

f.from_profile_id = "3734387944"

) AND

f.request = 3

WHERE f.from_profile_id IS NULL

Here's a DB-FIDDLE LINK

As mentioned in comment use a CASE statement sub-select to get the correct users id, the rest is then just a normal select.

...

WHERE u.profile_id IN (

  SELECT CASE WHEN blocked_by = ? THEN from_profile_id ELSE to_profile_id END AS friend_id 

  FROM friends

  WHERE (from_profile_id = ? OR to_profile_id = ?) AND request = 1

)

...

https://www.db-fiddle.com/f/9K3wy4tqRKVGgvvyxvkgMS/0