Sqlalchemy 子查询(subquery)

摘要：【Flask】Sqlalchemy 子查询 - 小小易拉罐 - 博客园

• 子查询1

subq = session.query(User.city.label('city'),     
    User.age.label('age')).filter(User.username=='saber').subquery()

users = session.query(User).filter(User.city==subq.c.city, User.age==subq.c.age)
print users
print users.all()

• 通过子查询结果排序

1. label：指定别名
2. outerjoin：左连接

• 子查询2

# 子查询
subqry = session.query(func.count(Server.id).label("sid")).filter(Server.id == Group.id).correlate(Group).as_scalar()
result = session.query(Group.name, subqry)

"""
SELECT 
`group`.name AS group_name, 
      (SELECT count(server.id) AS sid FROM server  WHERE server.id = `group`.id) AS anon_1 
       FROM `group`
"""