I'm trying to check if a conversation exists between an array of user IDs and then return the conversation_id
.
conversations table
+----+---------+------------+
| ID | name | created_at |
+----+---------+------------+
| 1 | Group 1 | datetime |
| 2 | Group 2 | datetime |
+----+---------+------------+
conversation_user table
+----+-----------------+---------+
| ID | conversation_id | user_id |
+----+-----------------+---------+
| 1 | 1 | 9 |
| 2 | 1 | 8 |
| 3 | 1 | 7 |
| 4 | 2 | 9 |
| 5 | 2 | 6 |
+----+-----------------+---------+
So say I have an array of user IDs [9,8,7]
this would return the conversation_id of 1
And if I had an array of user IDs [8,6]
this would return empty as the conversation doesn't exist.
But also if I had an array of user IDs [9,7]
this would return empty because a conversation between just these two users doesn't exist either.
I've tried variances of this SQL query:
SELECT conversation_id FROM conversation_user WHERE user_id IN (9,8,7) GROUP BY conversation_id
I am struggling to get this query right. If anyone can help that would be great.
Thanks