My plan is to get all chat groups between two users but prevent showing any chat group that has a blocked user.
to explain more let's say this is chatgroups table.
And when a user sends a message to other, a row will be inserted here with the id of each one of them
+------------------------------+
| id | member_one | member_two |
|------------------------------|
| 1 | 1 | 2 |
|------------------------------|
| 2 | 3 | 1 |
|------------------------------|
| 3 | 1 | 4 |
|------------------------------|
| 4 | 5 | 1 |
+------------------------------+
And this is blocks table.
when a user blocks another one, same thing will be here.
+-------------------------+
| id | blocker | blocked |
|-------------------------|
| 1 | 1 | 3 |
|-------------------------|
| 2 | 5 | 1 |
+-------------------------+
Now in the chat page, I want to get all groupchats rows which has no id that is already in blocks table(blocker or blocked)
Now let's say my id is 1. in this case i would get just the first and the third rows from chatgroups where me and the other user didn't block each of us.
here is what i do to get all chat groups that one of its rows has my id.
SELECT * FROM chatgroups
WHERE member_one=:myID
OR member_two=:myID
