Recursive CTE as secondary for ORM relationship?

Issue #4165 new
Sebastian Bank
created an issue

Not sure whether I did not read the docs carefully enough, or this is a bug, or a new feature. Trying to use a recursive CTE (e.g. in sqlite3) as join table for a relationship:

import sqlalchemy as sa
import sqlalchemy.ext.declarative

class Node(sa.ext.declarative.declarative_base()):

    __tablename__ = 'node'

    id = sa.Column(sa.Integer, primary_key=True)
    parent_id = sa.Column(sa.ForeignKey(''))

    def tree(cls):
        child, parent = (sa.orm.aliased(cls, name=n) for n in ('child', 'parent'))
        tree_1 =[
            ]).where(child.parent_id != None)\
        tree_2 =[tree_1.c.child_id, parent.parent_id])\
            .select_from(tree_1.join(parent, == tree_1.c.parent_id))\
            .where(parent.parent_id != None)
        return tree_1.union_all(tree_2)

tree = Node.tree()

Node.ancestors = sa.orm.relationship(Node, secondary=tree, == tree.c.child_id, == tree.c.parent_id)

session = sa.orm.Session()


# output
WITH RECURSIVE tree(child_id, parent_id) AS 
(SELECT AS child_id, child.parent_id AS parent_id 
FROM node AS child 
WHERE child.parent_id IS NOT NULL UNION ALL SELECT tree.child_id AS child_id, parent.parent_id AS parent_id 
FROM tree JOIN node AS parent ON = tree.parent_id 
WHERE parent.parent_id IS NOT NULL)
 SELECT AS node_id, node.parent_id AS node_parent_id 
FROM node 
FROM tree AS anon_1, tree, node AS node_1 
WHERE anon_1.child_id = tree.child_id AND = tree.parent_id AND = :id_1)

# expected
FROM tree AS anon_1, node AS node_1 
WHERE anon_1.child_id = node.child_id AND = anon_1.parent_id AND = :id_1)

Sorry for the long example (and thanks in advance for taking a look).

Comments (6)

  1. Sebastian Bank reporter

    Thanks. Extending the example I get similar results for a join:

    anc = sa.orm.aliased(Node)
    query = session.query(,, Node.ancestors)
    # output
    SELECT AS node_id, AS node_1_id 
    FROM node JOIN tree AS anon_1 ON anon_1.child_id = tree.child_id
    JOIN node AS node_1 ON = tree.parent_id'''
    # expected
    FROM node JOIN tree AS anon_1 ON = anon_1.child_id
    JOIN node AS node_1 ON = anon_1.parent_id
    engine = sa.create_engine('sqlite:///', echo=True)
    engine.execute(sa.insert(Node), [
        {'id': 1, 'parent_id': None},
        {'id': 2, 'parent_id': 1},
        {'id': 3, 'parent_id': 2},
        {'id': 4, 'parent_id': None},
    query = query.with_session(sa.orm.Session(engine))
    query.all()  # fails with 'no such column: tree.child_id'

    I tried playing with diferent remote_side, and remote/foreign() arguments in the relationship() without success.

  2. Sebastian Bank reporter

    With this, query.all() fails with no such table: anon_2:

    FROM node JOIN anon_2 AS anon_1 ON anon_1.child_id = anon_2.child_id
    JOIN node AS node_1 ON = anon_2.parent_id
  3. Log in to comment