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('node.id'))

    @classmethod
    def tree(cls):
        child, parent = (sa.orm.aliased(cls, name=n) for n in ('child', 'parent'))
        tree_1 = sa.select([
                child.id.label('child_id'),
                child.parent_id.label('parent_id'),
            ]).where(child.parent_id != None)\
            .cte(recursive=True)\
            .alias('tree')
        tree_2 = sa.select([tree_1.c.child_id, parent.parent_id])\
            .select_from(tree_1.join(parent, parent.id == 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,
    primaryjoin=Node.id == tree.c.child_id,
    secondaryjoin=Node.id == tree.c.parent_id)

session = sa.orm.Session()

print(session.query(Node).filter(Node.ancestors.any(id=42)))

# output
'''
WITH RECURSIVE tree(child_id, parent_id) AS 
(SELECT child.id 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 parent.id = tree.parent_id 
WHERE parent.parent_id IS NOT NULL)
 SELECT node.id AS node_id, node.parent_id AS node_parent_id 
FROM node 
WHERE EXISTS (SELECT 1 
FROM tree AS anon_1, tree, node AS node_1 
WHERE anon_1.child_id = tree.child_id AND node_1.id = tree.parent_id AND node_1.id = :id_1)
'''

# expected
'''
(...)
WHERE EXISTS (SELECT 1 
FROM tree AS anon_1, node AS node_1 
WHERE anon_1.child_id = node.child_id AND node_1.id = anon_1.parent_id AND node_1.id = :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.id, anc.id).join(anc, Node.ancestors)
    print(query)
    
    # output
    '''
    (...)
    SELECT node.id AS node_id, node_1.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 node_1.id = tree.parent_id'''
    
    # expected
    '''
    FROM node JOIN tree AS anon_1 ON node.id = anon_1.child_id
    JOIN node AS node_1 ON node_1.id = anon_1.parent_id
    '''
    
    engine = sa.create_engine('sqlite:///', echo=True)
    
    Node.metadata.create_all(engine)
    
    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 node_1.id = anon_2.parent_id
    '''
    
  3. Log in to comment