Materialized path relationship in declarative SQLAlchemy

I have a hierarchical categories model, where hierarchy is maintained using materialized path (one character per level):

class Category(Base):
    __tablename__ = 'categories'

    id = Column(SmallInteger, primary_key=True)
    path = Column(String, unique=True, nullable=False)

    # problematic relationship
    all_subcats = relationship('Category', lazy='dynamic', viewonly=True,
                               primaryjoin=foreign(path).like(remote(path).concat('%')))

When trying to define "all subcategories" relationship I run into a problem:

sqlalchemy.exc.ArgumentError: Can't determine relationship direction for
relationship 'Category.all_subcats' - foreign key columns within the join
condition are present in both the parent and the child's mapped tables.
Ensure that only those columns referring to a parent column are marked as
foreign, either via the foreign() annotation or via the foreign_keys argument.

SQLAlchemy is confused, because I'm joining on the same column. All examples I've managed to find always join on different columns.

Is this sort of relationship possible at all? I want to query through this join, so custom @property is not acceptable.

Use the latest git master or version 0.9.5 or greater of SQLAlchemy. Then:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Element(Base):
    __tablename__ = 'element'

    path = Column(String, primary_key=True)

    related = relationship('Element',
                           primaryjoin=
                                remote(foreign(path)).like(
                                        path.concat('/%')),
                           viewonly=True,
                           order_by=path)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)
sess.add_all([
    Element(path="/foo"),
    Element(path="/foo/bar1"),
    Element(path="/foo/bar2"),
    Element(path="/foo/bar2/bat1"),
    Element(path="/foo/bar2/bat2"),
    Element(path="/foo/bar3"),
    Element(path="/bar"),
    Element(path="/bar/bat1")
])

e1 = sess.query(Element).filter_by(path="/foo/bar2").first()
print [e.path for e in e1.related]

note that this model, whether you deal with "descendants" or "anscestors", uses collections. You want to keep remote() and foreign() together so that the ORM considers it as one to many.