python - How to filter exact many to many

Question

I have User and Room model in Flask SQLAlchemy. I need to filter if Room exists with users [user1, user2, ...]. Filter must be exact.
Here are my models:

room_users_table = db.Table(
  'room_users',
    db.metadata,
    db.Column('user', db.Integer, db.ForeignKey('user.id')),
    db.Column('room', db.Integer, db.ForeignKey('room.id'))
)

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(80))
    last_name = db.Column(db.String(80))
    password = db.Column(db.String(80))
    email = db.Column(db.String(120), unique=True)
    rooms = db.relationship(
        "Room",
        secondary=room_users_table,
        back_populates="users"
    )

class Room(db.Model):
   __tablename__ = 'room'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True)
    users = db.relationship(
        "User",
        secondary=room_users_table,
        back_populates="rooms"
    )

Thanks, I've searched all simular questions and can't find answer.

Answer 1

You can achieve this using a version of relational division and some additional filtering:

First build a temporary "table" (a union) of the emails you want to search against:

In [46]: emails = ['[email protected]', '[email protected]']

In [47]: emails_union = db.union(*(db.select([db.literal(email).label('email')])
                                   for email in emails)).alias()

That may look a bit unwelcoming, but it essentially forms an SQL UNION like this:

SELECT '[email protected]' AS email
UNION
SELECT '[email protected]' AS email

and gives it an alias. Some databases may support other means to generate a new relation from a list, for example with Postgresql you could:

In [64]: from sqlalchemy.dialects.postgresql import array

In [65]: emails_relation = db.func.unnest(array(emails)).alias()

The division itself is done using a double negation, or 2 nested NOT EXISTS conditions:

In [48]: db.session.query(Room).
    ...:     filter(~db.session.query().select_from(emails_union).
    ...:                filter(~Room.users.any(email=emails_union.c.email)).
    ...:                exists(),
    ...:            ~Room.users.any(User.email.notin_(emails))).
    ...:     all()
Out[48]: [<__main__.Room at 0x7fad4d238128>]

In [49]: [(r.name, [u.email for u in r.users]) for r in _]
Out[49]: [('room1', ['[email protected]', '[email protected]'])]

The query pretty much answers the question "find those Rooms for which no such email exists that is not in Room.users" – which finds rooms with all given emails – and then it applies the 3rd NOT EXISTS condition, which filters out rooms with additional emails. Without it the query would also return room2, which has emails 1, 2, and 3.

The searches were done against this data:

In [10]: users = [User(id=id_, email='email{}@mail.com'.format(id_))
    ...:          for id_ in range(1, 10)]

In [11]: rooms = [Room(id=id_, name='room{}'.format(id_))
    ...:          for id_ in range(1, 10)]

In [18]: db.session.add_all(users)

In [19]: db.session.add_all(rooms)

In [20]: for room, user1, user2 in zip(rooms, users, users[1:]):
    ...:     room.users.append(user1)
    ...:     room.users.append(user2)
    ...:     

In [21]: rooms[1].users.append(users[0])

In [22]: db.session.commit()