sqlalchemy的联系关系子查询

SQLAlchemy也算是用过好几年了，不过一直都用着其中相对简单的一小部分，最近写个程序碰到个问题，需要作一个关联子查询，类似这样的SQL语句：

SELECT master.*, (    SELECT count(*)     FROM detail     WHERE detail.parentid=master.id AND detail.someflag IS NOT NULL) FROM master;

试了很久不知道怎么用ORM来写。

如果COUNT不为0，用下面这个查询的结果是一样的：

SELECT master.id, count(detail.id) FROM master INNER JOIN detail ON detail.parentid=master.id WHERE detail.someflag IS NOT NULL GROUP BY master.id;

这个语句倒是可以用ORM实现：

qry = orm.query(master, func.count(detail.id).join(detail,     detail.parentid==master.id).filter(detail.someflag!=None).group_by(master)

但是如果COUNT为0就不行了，即便用OUTER JOIN也不行，这种情况下会丢失COUNT为0的master记录。

想来想去大概只能用子查询实现，但是试了这样的语句，结果跟上面一个是一样的，也会丢失COUNT为0的master记录。

subqry = orm.query(detail.parentid, func.count(detail.id).label(    "flagcnt").filter(detail.someflag!=None).group_by(detail.parentid).subquery()qry = orm.query(master, subqry.c.flagcnt).join(subqry, subqry.c.parentid==master.id)

这句的SQL相当于这样：

SELECT master.id, subqry.flagcnt FROM master JOIN (    SELECT detail.parentid, count(*) AS flagcnt     FROM detail     WHERE detail.someflag IS NOT NULL     GROUP BY detail.parentid) AS subqry ON subqry.parentid=master.id

没办法，只好继续搜索，最后还是在SO上看到一个类似问题的答案而受到启发，解决了这个困扰了一天多的问题。

subqry = orm.query(func.count(detail.id).label("flagcnt")).filter(    detail.parentid==master.id).filter(detail.someflag!=None).correlate(master).as_scalar()qry = orm.query(master, subqry)

其中的重点就在于correlate和as_scalar。