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?
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:
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 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 TestSelfJoin(unittest.TestCase): def test_self_join(self): query = Venue.query.options(joinedload(Venue.parents)) import sqlalchemy.dialects.postgresql as postgresql print query.statement.compile(dialect=postgresql.dialect())