SQLAlchemy:通过至少一个多对多相关表中的成员资格过滤

2021-12-30 00:00:00 python sqlalchemy many-to-many mysql

使用 SQLAlchemy 0.7.1 和 MySQL 5.1 数据库，我建立了如下的多对多关系:

Using SQLAlchemy 0.7.1 and a MySQL 5.1 database, I've got a many-to-many relationship set up as follows:

user_groups = Table('user_groups', Base.metadata,
    Column('user_id', String(128), ForeignKey('users.username')),
    Column('group_id', Integer, ForeignKey('groups.id'))
)

class ZKUser(Base, ZKTableAudit):
    __tablename__ = 'users'

    username   = Column(String(128), primary_key=True)
    first_name = Column(String(512))
    last_name  = Column(String(512))

    groups = relationship(ZKGroup, secondary=user_groups, backref='users')

class ZKGroup(Base, ZKTableAudit):
    __tablename__ = 'groups'

    id          = Column(Integer, primary_key=True)
    name        = Column(String(512))

用户可以属于多个组，组可以包含多个用户.

Users can belong to multiple Groups, and Groups can contain multiple Users.

我想要做的是构建一个 SQLAlchemy 查询，该查询仅返回属于组列表中至少一个组的用户.

What I'm trying to do is build a SQLAlchemy query that returns only the Users who belong to at least one Group out of a list of Groups.

我使用了 in_ 函数，但这似乎只适用于测试列表中成员资格的标量值.我不是一个 SQL 编写者，所以我什至不知道这需要什么样的 SELECT 语句.

I played around with the in_ function, but that only seems to work for testing scalar values for membership in a list. I'm not much of a SQL writer, so I don't even know what kind of SELECT statement this would require.

推荐答案

好吧，经过大量研究，我意识到是我自己对 SQL 术语的无知阻碍了我.我寻找一种解决方案来查找属于至少一个"组列表的用户应该是找到属于任何"组列表的用户.来自 SQLAlchemy 的 any ORM 函数正是我所需要的，就像这样:

OK, after a lot of research, I realized that it was my own ignorance of SQL terminology that was holding me back. My search for a solution to find users belonging to "at least one of" the list of groups should have been to find users belonging to "any" of the list of groups. The any ORM function from SQLAlchemy does exactly what I needed, like so:

session.query(ZKUser).filter(ZKUser.groups.any(ZKGroup.id.in_([1,2,3])))

该代码发出此 SQL(在 MySQL 5.1 上):

That code emits this SQL (on MySQL 5.1):

SELECT * FROM users 
WHERE EXISTS (
    SELECT 1 FROM user_groups, groups 
    WHERE users.id = user_groups.contact_id 
        AND groups.id = user_groups.group_id 
        AND groups.id IN (%s, %s, %s)
    )

相关文章