如何过滤精确的多对多 [英] How to filter exact many to many

问题描述

我在 Flask SQLAlchemy 中有用户和房间模型.我需要过滤房间是否与用户 [user1, user2, ...] 一起存在.过滤器必须准确.
这是我的模型:

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.

推荐答案

您可以使用 关系划分和一些额外的过滤:

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 = ['[e-mail]', '[e-mail]']

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

这可能看起来有点不受欢迎，但它本质上形成了这样的 SQL UNION:

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

SELECT '[e-mail]' AS email
UNION
SELECT '[e-mail]' AS email

并给它一个别名.某些数据库可能支持从列表中生成新关系的其他方法，例如使用 Postgresql，您可以:

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

除法本身是使用双重否定或 2 个嵌套的 NOT EXISTS 条件完成的:

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', ['[e-mail]', '[e-mail]'])]

查询几乎回答了这个问题找到那些不存在这样的电子邮件的 Room 不在 Room.users 中"——它找到所有给定的房间emails – 然后它应用第三个 NOT EXISTS 条件，它过滤掉带有额外电子邮件的房间.如果没有它，查询也会返回 room2，其中包含电子邮件 1、2 和 3.

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

这篇关于如何过滤精确的多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！