使用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
语句。
好吧,经过大量的研究,我意识到是我自己对SQL术语的无知阻碍了我。我搜索一个解决方案来查找属于"至少一个"组列表的用户,应该是查找属于"任何"组列表中的用户。SQLAlchemy中的any
ORM函数正是我所需要的,比如:
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)
)
根据any
的文档,如果使用显式join
,查询将运行得更快:
由于any()使用相关的子查询,因此与大型目标表相比,其性能远不如使用联接的性能好。
在你的情况下,你可以做一些类似的事情:
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)
您可以使用in_
:
session.query(ZKUser).filter(ZKGroup.id.in_([1,2])).all()