I have a simple chat functionality in my application where signed up users are able to create chat groups with other users. To store the information which user is a member in which chat I am using a simple relationship table:
CREATE TABLE `Chat_Users` (
`ID_Chat` int(11) NOT NULL,
`ID_User` int(11) NOT NULL,
PRIMARY KEY (`ID_Chat`,`ID_User`),
KEY `ID_User` (`ID_User`),
CONSTRAINT `Chat_Users_ibfk_1` FOREIGN KEY (`ID_Chat`) REFERENCES `Chats` (`ID`),
CONSTRAINT `Chat_Users_ibfk_2` FOREIGN KEY (`ID_User`) REFERENCES `Users` (`ID`)
)
Imagine three users (ids: 1, 2, 3) in a group chat (id: 1) and three other friends (ids: 3, 4, 5) in another group chat (id: 2). Then this relationship table would look like:
Chat_ID | Chat_User
-------------------
1 | 1
1 | 2
1 | 3
2 | 3
2 | 4
2 | 5
To avoid multiple chat groups with the exact same users I want to check if an identical one already exists when a users tries to create a new one. I have a string list with the userIDs of that new chat at the time of creation. So I have to check if there is already a chat with the exact same userIDs but unfortunately I do not know a 'good' way to do this.
My only idea so far was something like
SELECT GROUP_CONCAT(`ID_User`) FROM `Chat_Users`
GROUP BY `ID_Chat`
and then iterate with php over the results and check is one value equals the userIDs list from above. If there are no matches I know that there is no chat so far but this way is of course highly inefficient as soon as the table gets larger.
Any help would be much appreciated.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…