[Sqlalchemy-tickets] Issue #4165: Recursive CRE as secondary for ORM relationship? (zzzeek/sqlalchemy)

[Sebastian B. <iss...@bi...>]

New issue 4165: Recursive CRE as secondary for ORM relationship?
https://bitbucket.org/zzzeek/sqlalchemy/issues/4165/recursive-cre-as-secondary-for-orm

Sebastian Bank:

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](https://www.sqlite.org/lang_with.html)) as join table for a relationship:
```python
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).