- The "primaryjoin" model has been stretched a bit further to allow

a join condition that is strictly from a single column to itself,
translated through some kind of SQL function or expression.  This
is kind of experimental, but the first proof of concept is a
"materialized path" join condition where a path string is compared
to itself using "like".   The :meth:`.Operators.like` operator has
also been added to the list of valid operators to use in a primaryjoin
condition.  fixes #3029

2 files changed, 199 insertions, 2 deletions

diff --git a/test/orm/test_rel_fn.py b/test/orm/test_rel_fn.py
index c4d811d53..f0aa538f4 100644
--- a/test/orm/test_rel_fn.py
+++ b/test/orm/test_rel_fn.py
@@ -3,7 +3,7 @@ from sqlalchemy.testing import assert_raises_message, eq_, \
 from sqlalchemy.testing import fixtures
 from sqlalchemy.orm import relationships, foreign, remote
 from sqlalchemy import MetaData, Table, Column, ForeignKey, Integer, \
-    select, ForeignKeyConstraint, exc, func, and_
+    select, ForeignKeyConstraint, exc, func, and_, String
 from sqlalchemy.orm.interfaces import ONETOMANY, MANYTOONE, MANYTOMANY
 
 
@@ -119,6 +119,10 @@ class _JoinFixtures(object):
                         ("composite_target.uid", "composite_target.oid")),
             )
 
+        cls.purely_single_col = Table('purely_single_col', m,
+            Column('path', String)
+            )
+
     def _join_fixture_overlapping_three_tables(self, **kw):
         def _can_sync(*cols):
             for c in cols:
@@ -440,6 +444,37 @@ class _JoinFixtures(object):
                     **kw
                     )
 
+    def _join_fixture_purely_single_o2m(self, **kw):
+        return relationships.JoinCondition(
+                    self.purely_single_col,
+                    self.purely_single_col,
+                    self.purely_single_col,
+                    self.purely_single_col,
+                    support_sync=False,
+                    primaryjoin=
+                        self.purely_single_col.c.path.like(
+                            remote(
+                                foreign(
+                                    self.purely_single_col.c.path.concat('%')
+                                )
+                            )
+                        )
+                )
+
+    def _join_fixture_purely_single_m2o(self, **kw):
+        return relationships.JoinCondition(
+                    self.purely_single_col,
+                    self.purely_single_col,
+                    self.purely_single_col,
+                    self.purely_single_col,
+                    support_sync=False,
+                    primaryjoin=
+                        remote(self.purely_single_col.c.path).like(
+                            foreign(self.purely_single_col.c.path.concat('%'))
+                        )
+                )
+
+
     def _assert_non_simple_warning(self, fn):
         assert_raises_message(
             exc.SAWarning,
@@ -829,6 +864,13 @@ class ColumnCollectionsTest(_JoinFixtures, fixtures.TestBase,
             ]
         )
 
+    def test_determine_local_remote_pairs_purely_single_col_o2m(self):
+        joincond = self._join_fixture_purely_single_o2m()
+        eq_(
+            joincond.local_remote_pairs,
+            [(self.purely_single_col.c.path, self.purely_single_col.c.path)]
+        )
+
 class DirectionTest(_JoinFixtures, fixtures.TestBase, AssertsCompiledSQL):
     def test_determine_direction_compound_2(self):
         joincond = self._join_fixture_compound_expression_2(
@@ -862,6 +904,13 @@ class DirectionTest(_JoinFixtures, fixtures.TestBase, AssertsCompiledSQL):
         joincond = self._join_fixture_m2o()
         is_(joincond.direction, MANYTOONE)
 
+    def test_determine_direction_purely_single_o2m(self):
+        joincond = self._join_fixture_purely_single_o2m()
+        is_(joincond.direction, ONETOMANY)
+
+    def test_determine_direction_purely_single_m2o(self):
+        joincond = self._join_fixture_purely_single_m2o()
+        is_(joincond.direction, MANYTOONE)
 
 class DetermineJoinTest(_JoinFixtures, fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = 'default'
diff --git a/test/orm/test_relationships.py b/test/orm/test_relationships.py
index ccd54284a..3d8287b75 100644
--- a/test/orm/test_relationships.py
+++ b/test/orm/test_relationships.py
@@ -8,7 +8,7 @@ from sqlalchemy.orm import mapper, relationship, relation, \
                     backref, create_session, configure_mappers, \
                     clear_mappers, sessionmaker, attributes,\
                     Session, composite, column_property, foreign,\
-                    remote, synonym, joinedload
+                    remote, synonym, joinedload, subqueryload
 from sqlalchemy.orm.interfaces import ONETOMANY, MANYTOONE, MANYTOMANY
 from sqlalchemy.testing import eq_, startswith_, AssertsCompiledSQL, is_
 from sqlalchemy.testing import fixtures
@@ -231,6 +231,154 @@ class DependencyTwoParentTest(fixtures.MappedTest):
         session.flush()
 
 
+class DirectSelfRefFKTest(fixtures.MappedTest, AssertsCompiledSQL):
+    """Tests the ultimate join condition, a single column
+    that points to itself, e.g. within a SQL function or similar.
+    The test is against a materialized path setup.
+
+    this is an **extremely** unusual case::
+
+    Entity
+    ------
+     path -------+
+       ^         |
+       +---------+
+
+    In this case, one-to-many and many-to-one are no longer accurate.
+    Both relationships return collections.   I'm not sure if this is a good
+    idea.
+
+    """
+
+    __dialect__ = 'default'
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table('entity', metadata,
+                Column('path', String(100), primary_key=True)
+            )
+
+    @classmethod
+    def setup_classes(cls):
+        class Entity(cls.Basic):
+            def __init__(self, path):
+                self.path = path
+
+
+    def _descendants_fixture(self, data=True):
+        Entity = self.classes.Entity
+        entity = self.tables.entity
+
+        m = mapper(Entity, entity, properties={
+                "descendants": relationship(Entity,
+                                primaryjoin=
+                                    remote(foreign(entity.c.path)).like(
+                                            entity.c.path.concat('/%')),
+                                viewonly=True,
+                                order_by=entity.c.path)
+            })
+        configure_mappers()
+        assert m.get_property("descendants").direction is ONETOMANY
+        if data:
+            return self._fixture()
+
+    def _anscestors_fixture(self, data=True):
+        Entity = self.classes.Entity
+        entity = self.tables.entity
+
+        m = mapper(Entity, entity, properties={
+                "anscestors": relationship(Entity,
+                                primaryjoin=
+                                    entity.c.path.like(
+                                            remote(foreign(entity.c.path)).concat('/%')),
+                                viewonly=True,
+                                order_by=entity.c.path)
+            })
+        configure_mappers()
+        assert m.get_property("anscestors").direction is ONETOMANY
+        if data:
+            return self._fixture()
+
+    def _fixture(self):
+        Entity = self.classes.Entity
+        sess = Session()
+        sess.add_all([
+            Entity("/foo"),
+            Entity("/foo/bar1"),
+            Entity("/foo/bar2"),
+            Entity("/foo/bar2/bat1"),
+            Entity("/foo/bar2/bat2"),
+            Entity("/foo/bar3"),
+            Entity("/bar"),
+            Entity("/bar/bat1")
+        ])
+        return sess
+
+    def test_descendants_lazyload(self):
+        sess = self._descendants_fixture()
+        Entity = self.classes.Entity
+        e1 = sess.query(Entity).filter_by(path="/foo").first()
+        eq_(
+            [e.path for e in e1.descendants],
+            ["/foo/bar1", "/foo/bar2", "/foo/bar2/bat1",
+                "/foo/bar2/bat2", "/foo/bar3"]
+        )
+
+    def test_anscestors_lazyload(self):
+        sess = self._anscestors_fixture()
+        Entity = self.classes.Entity
+        e1 = sess.query(Entity).filter_by(path="/foo/bar2/bat1").first()
+        eq_(
+            [e.path for e in e1.anscestors],
+            ["/foo", "/foo/bar2"]
+        )
+
+    def test_descendants_joinedload(self):
+        sess = self._descendants_fixture()
+        Entity = self.classes.Entity
+        e1 = sess.query(Entity).filter_by(path="/foo").\
+                options(joinedload(Entity.descendants)).first()
+
+        eq_(
+            [e.path for e in e1.descendants],
+            ["/foo/bar1", "/foo/bar2", "/foo/bar2/bat1",
+                "/foo/bar2/bat2", "/foo/bar3"]
+        )
+
+    def test_descendants_subqueryload(self):
+        sess = self._descendants_fixture()
+        Entity = self.classes.Entity
+        e1 = sess.query(Entity).filter_by(path="/foo").\
+                options(subqueryload(Entity.descendants)).first()
+
+        eq_(
+            [e.path for e in e1.descendants],
+            ["/foo/bar1", "/foo/bar2", "/foo/bar2/bat1",
+                "/foo/bar2/bat2", "/foo/bar3"]
+        )
+
+    def test_anscestors_joinedload(self):
+        sess = self._anscestors_fixture()
+        Entity = self.classes.Entity
+        e1 = sess.query(Entity).filter_by(path="/foo/bar2/bat1").\
+                options(joinedload(Entity.anscestors)).first()
+        eq_(
+            [e.path for e in e1.anscestors],
+            ["/foo", "/foo/bar2"]
+        )
+
+    def test_plain_join_descendants(self):
+        self._descendants_fixture(data=False)
+        Entity = self.classes.Entity
+        sess = Session()
+        self.assert_compile(
+            sess.query(Entity).join(Entity.descendants, aliased=True),
+            "SELECT entity.path AS entity_path FROM entity JOIN entity AS "
+            "entity_1 ON entity_1.path LIKE (entity.path || :path_1)"
+        )
+
+
+
 class CompositeSelfRefFKTest(fixtures.MappedTest):
     """Tests a composite FK where, in
     the relationship(), one col points