1014
1014
The custom criteria we use in a :paramref:`~.relationship.primaryjoin`
1015
1015
is generally only significant when SQLAlchemy is rendering SQL in
1016
order to load or represent this relationship. That is, it's used in
1016
order to load or represent this relationship. That is, it's used in
1017
1017
the SQL statement that's emitted in order to perform a per-attribute
1018
1018
lazy load, or when a join is constructed at query time, such as via
1019
1019
:meth:`.Query.join`, or via the eager "joined" or "subquery" styles of
1157
1157
flag to assist in the creation of :func:`.relationship` constructs using
1158
1158
custom operators.
1160
Non-relational Comparisons / Materialized Path
1161
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1163
.. warning:: this section details an experimental feature.
1165
Using custom expressions means we can produce unorthodox join conditions that
1166
don't obey the usual primary/foreign key model. One such example is the
1167
materialized path pattern, where we compare strings for overlapping path tokens
1168
in order to produce a tree structure.
1170
Through careful use of :func:`.foreign` and :func:`.remote`, we can build
1171
a relationship that effectively produces a rudimentary materialized path
1172
system. Essentially, when :func:`.foreign` and :func:`.remote` are
1173
on the *same* side of the comparison expression, the relationship is considered
1174
to be "one to many"; when they are on *different* sides, the relationship
1175
is considered to be "many to one". For the comparison we'll use here,
1176
we'll be dealing with collections so we keep things configured as "one to many"::
1178
class Element(Base):
1179
__tablename__ = 'element'
1181
path = Column(String, primary_key=True)
1183
descendants = relationship('Element',
1185
remote(foreign(path)).like(
1190
Above, if given an ``Element`` object with a path attribute of ``"/foo/bar2"``,
1191
we seek for a load of ``Element.descendants`` to look like::
1193
SELECT element.path AS element_path
1195
WHERE element.path LIKE ('/foo/bar2' || '/%') ORDER BY element.path
1197
.. versionadded:: 0.9.5 Support has been added to allow a single-column
1198
comparison to itself within a primaryjoin condition, as well as for
1199
primaryjoin conditions that use :meth:`.Operators.like` as the comparison
1160
1202
.. _self_referential_many_to_many:
1162
1204
Self-Referential Many-to-Many Relationship