SQL / typeORM query to get a DM conversation in a chat web application

Romain T-BIGOT 0
Viewed 32

I am pretty new to SQL and typeORM, and I especially have troubles when it comes to many to many relationships.

I am working on a chat application. I have two tables connected by a many to many to many relationship.

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

Room.type can be of value dm, private or public I also have a join table in between the two tables, but it is handled by typeORM so I don't really care about it.

What I want to do is to query the Room table and get all the rooms of type dm where the user are exactly user1_id and user2_id (no more, no less) but I really don't know how to formulate this query. Something like:

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

I am posting this with SQL but eventually I will translate it with typeORM syntax which should not be a problem.

Thanks.

typeorm database sql postgresql chat
1 Answers

Since there's a many to many relation between Room and User and as the model looks like Room has an array of User, there'd be a join table in the database named room_users_user. Now you want all rooms where only user1_id and user2_id are participants. The corresponding SQL query should be:

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;

which can be written in typeorm like this:

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();

A DB Fiddle for better understanding.

user3uh80
Related