SQLAlchemy：按至少一个多对多相关表中的成员身份进行过滤

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

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

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.

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

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.

我正在尝试做的是构建一个SQLAlchemy查询，该查询仅返回属于组列表中至少一个组的用户。

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.

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

3 个解决方案

#1

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:

好的，经过大量的研究，我意识到我自己对SQL术语的无知让我筋疲力尽。我搜索找到属于“至少一个”组列表的用户的解决方案应该是找到属于组列表中“任意”的用户。 SQLAlchemy的任何ORM函数完全符合我的需要，如下所示：

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

That code emits this SQL (on MySQL 5.1):

该代码发出此SQL（在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)
    )

#2

According to the docs for any, the query will run faster if you use an explicit join instead:

根据任何文档，如果您使用显式连接，查询将运行得更快：

Because any() uses a correlated subquery, its performance is not nearly as good when compared against large target tables as that of using a join.

因为any（）使用相关子查询，所以与大目标表进行比较时，其性能与使用连接时的表现差不多。

In your case, you could do something like:

在您的情况下，您可以执行以下操作：

users = (
    session.query(ZKUser)
    .join(user_groups)
    .filter(user_groups.columns.group_id.in_([1, 2, 3]))
)

This emits SQL like:

这会发出如下SQL：

SELECT *
FROM users
JOIN user_groups ON users.id = user_groups.user_id 
WHERE user_groups.group_id IN (1, 2, 3)

#3

You can use in_:

你可以使用in_：

session.query(ZKUser).filter(ZKGroup.id.in_([1,2])).all()

#1

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