[Sqlalchemy-tickets] Issue #3831: Self referencing relationship can not determine join condition (zzzeek/sqlalchemy)

[Lukas S. <iss...@bi...>]

New issue 3831: Self referencing relationship can not determine join condition
https://bitbucket.org/zzzeek/sqlalchemy/issues/3831/self-referencing-relationship-can-not

Lukas Siemon:

I'm trying to follow the documentation as described here:
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#non-relational-comparisons-materialized-path

This works as expected, however when I try to use (any) `func` function this fails. I noticed this feature is experimental, but is it possible to fix it for this use case?

Error:

`
ArgumentError: Relationship Venue.parents could not determine any unambiguous local/remote column pairs based on join condition and remote_side arguments.  Consider using the remote() annotation to accurately mark those elements of the join condition that are on the remote side of the relationship.
`

The issue is also documented here:
http://stackoverflow.com/questions/38006116/how-can-i-create-a-many-to-many-relationship-with-sqlalchemy-using-a-sql-functio

Minimal Test case:
```
#!python

import unittest
from sqlalchemy import (Column, Integer, String, func)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (relationship, joinedload)
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy


# -- create all the database models
from sqlalchemy.orm import foreign
from sqlalchemy.orm import remote

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = ("sqlite://")
db = SQLAlchemy(app)

Base = declarative_base()


class Venue(db.Model):
    __tablename__ = 'venue'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(254))

    parents = relationship(
        "Venue",
        # doesn't work
        primaryjoin=func.substring(remote(foreign(name)), name),
        # works
        # primaryjoin=remote(foreign(name)).like('%' + name + '%'),
        viewonly=True,
        order_by=remote(foreign(name))
    )

db.drop_all()
db.create_all()


class TestContainsEager(unittest.TestCase):

    def test_contains_eager(self):
        query = Venue.query.options(joinedload(Venue.parents))

        # contains_eager does not pick up the alias from the inner query
        import sqlalchemy.dialects.postgresql as postgresql
        print query.statement.compile(dialect=postgresql.dialect())
```

Responsible: zzzeek