子查询中的 SqlAlchemy 闭包

发布于 2024-12-03 03:43:33 字数 353 浏览 1 评论 0原文

我搜索了很多主题但没有找到答案，或者问题太复杂。那么好吧。这是我的第一个问题。这是 SQL

SELECT  parent.*,
(
    SELECT  COUNT(*)
    FROM    child
    WHERE   parent.id = child.parent_id
)
FROM parent

如何在 sqlalchemy 中执行此子句？

WHERE   ui.invited_by = u.id

可以在收藏中复制吗？ SQL 表达式 ? PS我知道可以通过group_by来完成。但我需要通过子查询。

谢谢。

原文

I have searched many topics and didn't find the answer, or question was too complex. So okay. This is my first question.
Here is the SQL

SELECT  parent.*,
(
    SELECT  COUNT(*)
    FROM    child
    WHERE   parent.id = child.parent_id
)
FROM parent

How to do this clause in sqlalchemy?

WHERE   ui.invited_by = u.id

Can it be reproduced in collections ? sql expressions ?
P.S. I know that it can be done by group_by. But i need by subquery.

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

缱倦旧时光 2024-12-10 03:43:33

SA 查询（使用子查询）将为您提供您想要的结果：

sq = session.query(Child.parent_id, func.count(Child.id).label("child_num")).group_by(Child.parent_id)
sq = sq.subquery()
# use outerjoin to have also those Parents with 0 (zero) children
q = session.query(Parent, sq.c.child_num).outerjoin(sq)
q = q.filter(Parent.id == 1) # add your filter here: ui.invited_by = u.id
for x in q.all():
    print x

虽然子查询并不完全像您所描述的那样，而是类似于：

SELECT      parent.*,
            anon_1.child_num AS anon_1_child_num 
FROM        parent 
LEFT JOIN  (SELECT  child.parent_id AS parent_id, 
                    count(child.id) AS child_num 
            FROM    child 
            GROUP BY child.parent_id
            ) AS anon_1
        ON parent.id = anon_1.parent_id

仍然不明白为什么您需要您所描述的方式的子查询。

The SA query (using subquery) will give you the results you want:

sq = session.query(Child.parent_id, func.count(Child.id).label("child_num")).group_by(Child.parent_id)
sq = sq.subquery()
# use outerjoin to have also those Parents with 0 (zero) children
q = session.query(Parent, sq.c.child_num).outerjoin(sq)
q = q.filter(Parent.id == 1) # add your filter here: ui.invited_by = u.id
for x in q.all():
    print x

although the subquery is not exactly as you described, but rather something like:

SELECT      parent.*,
            anon_1.child_num AS anon_1_child_num 
FROM        parent 
LEFT JOIN  (SELECT  child.parent_id AS parent_id, 
                    count(child.id) AS child_num 
            FROM    child 
            GROUP BY child.parent_id
            ) AS anon_1
        ON parent.id = anon_1.parent_id

Still do not understand why you need a sub-query the way you described though.

回复收藏 0 原文

牵你的手，一向走下去 2024-12-10 03:43:33

我在这里找到了非常棒的答案。但也太复杂了。首先我想告诉大家，sql 世界中的闭包是关联性的。

这不一样，但对我有帮助。

pparent = Parent.__table__.alias('pparent') # using table directly to alias.

subquery = s.query(count(Child.id)).join(pparent) # usual thing but use aliased table.

s.query(Parent, subquery.filter(Parent.id == pparent.id).correlate(Parent).as_scalar()) #magic

I find here really awesome answer. But too also too complicated. First of all i want to tell that closure in sql world is CORRELATION.

This is NOT the same but helps me.

pparent = Parent.__table__.alias('pparent') # using table directly to alias.

subquery = s.query(count(Child.id)).join(pparent) # usual thing but use aliased table.

s.query(Parent, subquery.filter(Parent.id == pparent.id).correlate(Parent).as_scalar()) #magic

回复收藏 0 原文

~没有更多了~