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

问题描述

我在Flask SQLAlchemy中有用户和房间模型.我需要过滤Room是否与用户[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.users中不存在此类电子邮件的Room" –查找包含所有给定电子邮件的房间–然后应用第3个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屋！