How to query an Entity from a Many-to-Many realtionship dependendt on two ore more conditions in Ktor with the Kotlin Exposed Framework?

Question

I have 3 entities:

User
id: Int
name: String

Room
id: Int
name: String

RoomUser
id: Int
room_id: Int (Reference on Room.id)
user_id: Int (Reference on User.id)

Now lets say we have 2 rooms and 3 users:

Rooms:

id	name
1	Room1
2	Room2

Users:

id	name
3	User3
7	User7
9	User9

RoomUsers:

id	room_id	user_id
1	1	3
2	1	7
3	2	7
4	2	9

As you can see, User3 and User7 are in the room Room1 and User7 and User9 are in the room Room2.

In my use case I have given for example 2 users. Now I want to get the room, in which these both Users are.

In this case, when I have given the users User3 and User7 I want to get the room Room1 or when I have given the users User7 and User9 I want to get the room Room2.

How can I write an query with the Kotlin Exposed Framework to get this specific room?

Answer 1

In SQL notation it should be like:

SELECT room_id
FROM RoomUsers
where user_id in (3,7)
GROUP BY room_id
HAVING count(*) = 2

In Exposed:

RoomUsers
  .slice(RoomUsers.roomId)
  .select{
     RoomUsers.userId inList listOf(User3.id, User7.id)
  }
  .groupBy(RoomUsers.roomId)
  .having {
    RoomUsers.userId.count() eq 2
  }.map {
    it[RoomUsers.roomId]
  }