SQLAlchemy中带有子句的递归CTE [英] Recursive CTE with subclause in SQLAlchemy

问题描述

我正在尝试在SQLAlchemy中实现CTE，该CTE从父/子树中的Give节点返回根节点.

I am trying to implementa CTE in SQLAlchemy that returns a root node from a give node in a parent/child tree.

我能够按照递归CTE查询中的示例在SQLAlchemy中生成CTE递归.

I was able to follow the example in Recursive CTE Query to produce a CTE recursion in SQLAlchemy.

但是我不确定如何在其上附加条件.即:

But I am not sure how to append an addional condition on to it. Namely:

WITH ...
SELECT parent_id from parents
WHERE parent_id NOT IN (SELECT child_id FROM node_parent_relation );

我想这样做，以便只恢复根节点.

I'd like to do this so that I only get the root nodes reuturned.

查询在SQL中工作.

WITH RECURSIVE parents( child_id, parent_id ) 
AS (
  -- get leaf children
  SELECT child_id, parent_id
  FROM node_parent_relation
  WHERE child_id = '4415b581-0a31-43e8-a69d-d74caeccacd2'

  UNION ALL

  -- get all parents  
  SELECT t.child_id, t.parent_id
  FROM parents p
  JOIN node_parent_relation t
  ON p.parent_id = t.child_id
)
SELECT parent_id from parents
WHERE parent_id NOT IN (SELECT child_id FROM node_parent_relation );

但是我可以看到如何将此附加子句添加到我的SQLAlchemy语句中.

But I can's see how to add this additional clause to my SQLAlchemy statements.

我想这会涉及将这些语句添加到某个地方.

I imagine that it would involve adding these statements somewhere.

sub_query = db.session.query(NodeParentRelation.child_id)
[...].filter([...].parent_id.in_(sub_query))

这是我目前的SQLAlchemy代码的样子:

This is how my SQLAlchemy code looks at present:

parent = aliased(NodeParentRelation)
child = aliased(NodeParentRelation)

sub_query = db.session.query(NodeParentRelation.child_id)
top_query = db.session.query(NodeParentRelation.child_id,
                             NodeParentRelation.parent_id)\
                         .filter(NodeParentRelation.child_id == self.id)\
                         .cte(recursive=True)

bottom_query = db.session.query(child.child_id, child.parent_id)\
                         .join(parent, parent.parent_id == child.child_id)

query = top_query.union(bottom_query)

作为参考，这是根据上面的SQLAlchemy语句构建的SQL查询.

For reference, this is the SQL query that is currently being constructed from the above SQLAlchemy statements.

WITH RECURSIVE anon_1(child_id, parent_id) AS
(SELECT node_parent_relation.child_id AS child_id, node_parent_relation.parent_id AS parent_id
FROM node_parent_relation
WHERE node_parent_relation.child_id = %(child_id_1)s UNION SELECT node_parent_relation_1.child_id AS node_parent_relation_1_child_id, node_parent_relation_1.parent_id AS node_parent_relation_1_parent_id
FROM node_parent_relation AS node_parent_relation_1 JOIN node_parent_relation AS node_parent_relation_2 ON node_parent_relation_2.parent_id = node_parent_relation_1.child_id)
 SELECT anon_1.child_id AS anon_1_child_id, anon_1.parent_id AS anon_1_parent_id
FROM anon_1

推荐答案

当前查询根本没有在递归步骤中引用CTE.您需要CTE本身的别名才能解决该问题:

The current query does not reference the CTE in the recursive step at all. You need an alias of the CTE itself in order to fix that:

In [5]: top_query = db.session.query(NodeParentRelation.child_id,
   ...:                              NodeParentRelation.parent_id)\
   ...:                          .filter(NodeParentRelation.child_id == 1)\
   ...:                          .cte(recursive=True)
   ...:                              

In [6]: parents = db.aliased(top_query)

In [7]: t = db.aliased(NodeParentRelation)

In [11]: query = top_query.union_all(
    ...:     db.session.query(t.child_id, t.parent_id).
    ...:         join(parents, t.child_id == parents.c.parent_id))

其余都是非常标准的东西:

The rest is pretty standard stuff:

In [14]: db.session.query(query.c.parent_id).\
    ...:     filter(query.c.parent_id.notin_(
    ...:         db.session.query(NodeParentRelation.child_id)))
Out[14]: <sqlalchemy.orm.query.Query at 0x7f56bf55b668>

In [15]: print(_)
WITH RECURSIVE anon_1(child_id, parent_id) AS 
(SELECT node_parent_relation.child_id AS child_id, node_parent_relation.parent_id AS parent_id 
FROM node_parent_relation 
WHERE node_parent_relation.child_id = ? UNION ALL SELECT node_parent_relation_1.child_id AS node_parent_relation_1_child_id, node_parent_relation_1.parent_id AS node_parent_relation_1_parent_id 
FROM node_parent_relation AS node_parent_relation_1 JOIN anon_1 AS anon_2 ON node_parent_relation_1.child_id = anon_2.parent_id)
 SELECT anon_1.parent_id AS anon_1_parent_id 
FROM anon_1 
WHERE anon_1.parent_id NOT IN (SELECT node_parent_relation.child_id AS node_parent_relation_child_id 
FROM node_parent_relation)

尽管我个人更希望不存在:

Though personally I'd prefer NOT EXISTS:

In [25]: db.session.query(query.c.parent_id).\
    ...:     filter(~db.session.query(NodeParentRelation).
    ...:         filter(NodeParentRelation.child_id == query.c.parent_id).
    ...:         exists())
    ...:         

这篇关于SQLAlchemy中带有子句的递归CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！