title: Recursive CTE with subclause in SQLAlchemy
body: <p>I am trying to implementa CTE in SQLAlchemy that returns a root node from a give node in a parent/child tree.</p>
  
  <p>I was able to follow the example in <a href="https://stackoverflow.com/questions/25896706/recursive-cte-query">Recursive CTE Query</a> to produce a CTE recursion in SQLAlchemy.</p>
  
  <p>But I am not sure how to append an addional condition on to it. Namely:</p>
  
  <pre><code>WITH ...
  SELECT parent_id from parents
  WHERE parent_id NOT IN (SELECT child_id FROM node_parent_relation );
  </code></pre>
  
  <p>I'd like to do this so that I only get the root nodes reuturned.</p>
  
  <p>The query works in SQL.</p>
  
  <pre><code>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 );
  </code></pre>
  
  <p>But I can's see how to add this additional clause to my SQLAlchemy statements.</p>
  
  <p>I imagine that it would involve adding these statements somewhere.</p>
  
  <pre><code>sub_query = db.session.query(NodeParentRelation.child_id)
  [...].filter([...].parent_id.in_(sub_query))
  </code></pre>
  
  <p>This is how my SQLAlchemy code looks at present:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>For reference, this is the SQL query that is currently being constructed from the above SQLAlchemy statements.</p>
  
  <pre><code>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
  </code></pre>