SQLAlchemy: 按照许多对多关联表中的至少一个成员过滤

Question

我在使用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))

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

我想做的是构建一个SQLAlchemy查询，返回那些至少属于一个特定小组的用户，这些小组是我提供的一个列表。

我尝试过使用in_这个函数，但它似乎只适用于检查某个值是否在列表中。我对SQL不太熟悉，所以我甚至不知道需要什么样的SELECT语句。

Answer 1

你可以使用 in_：

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

Answer 2

根据文档中关于any的说明，如果你使用明确的join，查询会更快：

因为any()使用的是相关子查询，所以在处理大表时，它的性能比使用join要差很多。

在你的情况下，你可以这样做：

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

这会生成类似于以下的SQL：

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

Answer 3

好的，经过大量的研究，我意识到是我对SQL术语的无知让我陷入困境。我原本想找出属于“至少一个”组的用户，实际上应该是找出属于“任何一个”组的用户。SQLAlchemy中的any函数正好满足了我的需求，像这样：

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

这段代码生成了以下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)
    )