Materialized path relationship: `lazy="subquery"` not working while `subqueryload()` works

[Jack Zhou]

Reference: http://stackoverflow.com/questions/23054398/materialized-path-relationship-in-declarative-sqlalchemy/23176477#comment36511621_23176477

The configuration:

class Node(Base):
    __tablename__ = "node"
    id = Column(Integer, primary_key=True)
    path = Column(String(500), nullable=False)
    children = relationship(
        "Node", primaryjoin=remote(foreign(path)).like(path.concat(".%")),
        viewonly=True, lazy="subquery")

The query:

db.add(Node(path="foo"))
db.add(Node(path="foo.bar"))
db.add(Node(path="foo.bar.baz"))
db.flush()

db.query(Node).all()  # issues one SELECT to find all nodes
db.query(Node).options(subqueryload(Node.children)).all()  # issues two SELECTs to find all nodes and their children

The lazy="subquery" declaration does not seem to have any effect.

[Mike Bayer]

this is because self-referential relationships don't structurally eager load unless you give it a "join depth": http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#configuring-self-referential-eager-loading.

feel free to reopen if that doesn't resolve.