Multi-table insert constraint

Questions / Help
1

I have a chatrooms and a users table joined via a chatroom_members table

I am modeling “private” chatrooms as being chatrooms with type: :private and exactly 2 chatroom_members. Private chatrooms are unique via their chatroom_members, i.e. 2 users can have no more than 1 private chatroom together.

Given the following records

chatroom — id:1, type: :private
chatroom_member — chatroom_id: 1, user_id: 100
chatroom_member — chatroom_id: 1, user_id: 200

How do i check on insert chatroom whether a chatroom already exists with only and exactly those 2 user_id's in the associated chatroom_members?

2

Are you using postgres? If so, you could group chatroom_member by :chatroom_id and use aggregate function array_agg (this might require Fragments) on user_id. Then join the resulting table with chatrooms which holds the :type.

You should now be able to filter the results based on type == :private and comparing the :user_id list with the users who are requesting a new private room.

This might also be a good fit for ETS. Then you could use simple pattern matching to check for conflicting rooms.