How to use the order_by defined on the relationship in SQLAlchemy and contains_eager?

Question

The Zen of Joined Eager Loading docs recommends using contains_eager() if we want to keep the relationship order defined in the model.

"If we wanted to use just one JOIN for collection loading as well as ordering, we use the contains_eager() option, described in Routing Explicit Joins/Statements into Eagerly Loaded Collections below."

But the following example seems to behave otherwise. I must be missing something, but unsure what.

class Parent(Base):
    __tablename__ = "parent"

    parent_id = Column(types.Integer, primary_key=True)
    name = Column(types.String(200), nullable=False)


class Child(Base):
    __tablename__ = "child"

    order = Column(types.Integer, default=0)
    name = Column(types.String(200))

    parent_id = Column(types.Integer, ForeignKey(Parent.parent_id))
    parent = relationship(
        Parent,
        backref=backref(
            "children",
            cascade="all,delete",
            order_by="Child.order",
        ),
    )

query = session.query(Parent).options(
    contains_eager(Parent.children)
).filter(Parent.parent_id == 99).filter(Child.name == "foo")

Generates the following SQL:

SELECT parent.parent_id, parent.name,
       child.order, child.name,
FROM parent, child
WHERE parent.parent_id = 99 AND parent.name = 'foo'

For some reason,

ORDER BY child.order

is missing, even though it's defined in the relationship(). Any hints?

It works fine if the order_by is specified at query time, but I want to avoid writing the same ordering criteria multiple times.

"Contains eager" tells SQLA "I'll handle the join etc., just fill the relationship from the results". You have not joined or used `order_by`, so they do not appear in the query. — Ilja Everilä, Feb 25 '20 at 16:12
Even if I stick an `.join(Child)` before `.options()` in the query, nothing changes. — Tudor, Feb 25 '20 at 17:09
You must provide the entire query, including order by, if you inform SQLA that you're forming the query yourself. — Ilja Everilä, Feb 25 '20 at 17:18
I guess you mean adding the `.order_by()` in the query. That works, but the ordering would be defined in 2 places. Isn't there any way to make SQLA read the query from the mapping? — Tudor, Feb 25 '20 at 17:32
how about using "ordering_list", you can use this to do this in the model you define rather than the query ? — Raj Verma, Feb 27 '20 at 17:20

score 3 · Accepted Answer · answered Feb 28 '20 at 21:01

The documentation is correct, and it refers to the fact that if using most out-of-the box eager loading methods, the query is modified and might not be optimal.

The suggestion is then to use contains_eager where:

1) the user is responsible for constructing the correct query (including joins, filters, ordering etc)

2) by using contains_eager the user hints SA that the specified relationship is included in the query.

The way to load relationship eagerly would be to use joinedload:

q_joined = (
    session
    .query(Parent)
    .options(joinedload(Parent.children))
    .filter(Parent.parent_id == parent_id)
)

But you cannot apply these additional filters in this case.

Using the contains_eager you would do:

q_manual = (
    session
    .query(Parent)
    .join(Child)  # MUST HAVE THIS
    .options(contains_eager(Parent.children))
    .filter(Parent.parent_id == 99)
    # .filter(Child.name == "foo")  # you can add this, but you are "tricking" SA into believing that only these 'Child' are part of the Parent.children relationship.
    .order_by(Parent.parent_id, Child.order)  # JUST ADD THIS to solve the ordering
)

How to use the order_by defined on the relationship in SQLAlchemy and contains_eager?

1 Answers1