SQL/typeORM查询，以在聊天web应用程序中获取DM对话

Question

我对SQL和typeORM还很陌生，尤其是在处理多对多关系时遇到了麻烦。

我正在开发一个聊天应用程序。我有两张表通过多对多的关系连接在一起。

=========          =========
|  Room |          |  User |
=========          =========
|   id  |       -< |   id  |
---------      /   ---------
|  type |     /    |  ...  |
---------    /     ---------
| User[]| >--
---------
|  ...   |
---------

Room.type的值可以是dm、private或public我在两个表之间也有一个联接表，但它是由typeORM处理的，所以我并不真正关心它

我想做的是查询Room表，并获得所有类型为dm的房间，其中用户恰好user1_id和user2_id(不多也不少(，但我真的不知道如何制定这个查询。类似于：

SELECT * FROM 'Room'
WHERE type IS 'dm'
AND WHERE 'user1_id' IN 'User.id'
AND WHERE 'user2_id' IN 'User.id'
AND WHERE LENGTH(Room.User) = 2

我用SQL发布了这篇文章，但最终我会用typeORM语法翻译它，这应该不是问题。

谢谢。

Answer 1

由于Room和User之间存在多对多关系，并且模型看起来像Room有一个User数组，因此数据库中会有一个名为room_users_user的联接表。现在，您想要所有只有user1_id和user2_id是参与者的房间。相应的SQL查询应该是：

SELECT rooms.id from rooms
LEFT JOIN room_users_user ON rooms.id = room_users_user.room_id
LEFT JOIN users ON room_users_user.user_id = users.id
WHERE rooms.type = 'dm' AND users.id IN (2, 4)
GROUP BY rooms.id
HAVING COUNT(users.id) = 2;

可以用这样的字体写：

const roomIds = await roomRepository.createQueryBuilder('room')
.leftJoinAndSelect('room.users', 'users')
.select('room.id', 'roomId')
.where('room.type = :type', { type: 'dm' })
.andWhere('users.id in (:...userId)', { userId: [user1_id, user2_id] })
.groupBy('room.id')
.having('count(users.id) = 2')
.getRawMany();

DB Fiddle以获得更好的理解。