Using contains_eager with filtered joins

Use contains_eager() when a query already joins the related table and the collection should be populated from those rows — a join plus selectinload() filters the parents but reloads the whole collection, which looks right and is not. This guide belongs to complex joins and relationship loading strategies.

Quick Answer

Joining and filtering a child table decides which parents come back. It does not decide what their collections contain.

Before — the join filters parents, the loader reloads everything:

from sqlalchemy import select
from sqlalchemy.orm import selectinload

from blog.models import Comment, Post

stmt = (
    select(Post)
    .join(Post.comments)
    .where(Comment.approved.is_(True))
    .options(selectinload(Post.comments))      # a second, unfiltered query
    .distinct()
)
# Posts with at least one approved comment — but post.comments contains
# every comment, approved or not.

After — the collection comes from the join:

from sqlalchemy import select
from sqlalchemy.orm import contains_eager

from blog.models import Comment, Post

stmt = (
    select(Post)
    .join(Post.comments.and_(Comment.approved.is_(True)))
    .options(contains_eager(Post.comments))
    .order_by(Post.published_at.desc())
)
# One query. Posts with at least one approved comment, and post.comments
# contains exactly the approved ones.

Putting the filter inside Post.comments.and_(...) keeps it in the join's ON clause, which is what lets isouter=True work later: a filter in WHERE would undo the outer join by discarding the rows where the child columns are NULL.

The collection is now a filtered view of the relationship, which is exactly the point — and the reason it must never be written through.

Execution Context & Async Workflow Integration

contains_eager() does not add anything to the query. It tells the ORM that a join you wrote produces columns belonging to a particular relationship, so the result rows can be used to populate it. That is the whole mechanism, and everything else follows from it.

Because the collection is built from the joined rows, it contains what the join returned — no more. With a filter in the ON clause, that is the filtered subset. Without contains_eager, those same joined columns would be discarded and the relationship would be loaded separately, unfiltered, which is the bug in the first example.

The three eager-loading options therefore answer different questions. selectinload() and joinedload() load the complete relationship, one with an extra IN query and one with its own outer join — the trade-off in using selectinload vs joinedload for N+1 prevention. contains_eager() adds no query and no join, and gives a partial collection.

Under async this matters more than it might appear, because there is no fallback. A relationship that was not populated raises MissingGreenlet on access rather than lazily loading, so a contains_eager that names the wrong relationship — or a code path that reads a different relationship on the same objects — fails immediately rather than quietly issuing queries.

Parent deduplication is worth understanding. The join returns one row per comment, so a post with five approved comments appears five times; the ORM collapses them by identity into one Post with five comments. That is why .distinct() is unnecessary with contains_eager and actively harmful: SELECT DISTINCT over all the selected columns is expensive and can interfere with ordering. Removing the distinct() that was needed for the join-only version is part of the change.

Two other details commonly come up.

An outer join is needed when parents without a matching child must still appear. join(..., isouter=True) with the filter in the ON clause gives those parents an empty collection — which is only correct because the filter is in ON; in WHERE it would eliminate them.

An aliased join is required when the same relationship is joined twice, or when the query also filters on the same table for a different reason. contains_eager(Post.comments.of_type(alias)) names the alias so the ORM knows which join to read.

Resolving Warnings, Errors & Common Mistakes

The write hazard is the one worth dwelling on, because nothing errors. Consider a post loaded with only its approved comments, where Post.comments has cascade="all, delete-orphan":

post = await session.scalar(
    select(Post)
    .where(Post.id == post_id)
    .join(Post.comments.and_(Comment.approved.is_(True)))
    .options(contains_eager(Post.comments))
)
post.comments.remove(post.comments[0])     # removes one approved comment
await session.commit()

The unapproved comments are not in the collection, and the unit of work compares the collection with what it loaded — which was also only the approved ones — so in this exact case nothing else is deleted. But the moment any code assigns to the collection (post.comments = [...]) or the loaded state is refreshed, the filtered-out rows become orphans from the ORM's point of view. Treating filtered collections as read-only avoids reasoning about which variant is safe.

The clean way to make that explicit is a second, viewonly relationship built for the filtered view:

from sqlalchemy.orm import relationship


class Post(Base):
    __tablename__ = "posts"
    # ...
    comments: Mapped[list["Comment"]] = relationship(
        back_populates="post", cascade="all, delete-orphan"
    )
    approved_comments: Mapped[list["Comment"]] = relationship(
        primaryjoin="and_(Post.id == Comment.post_id, Comment.approved.is_(True))",
        viewonly=True,
        order_by="Comment.created_at",
    )

contains_eager(Post.approved_comments) then populates a relationship SQLAlchemy knows is read-only, so a mutation raises a warning rather than corrupting anything. The overlapping-mappings warning this pair could otherwise produce is covered in fixing "relationship will copy column" conflict warnings.

Advanced: Aliases, Nested Paths and Ordering

Three variations cover the cases a real query runs into.

An aliased join is needed whenever the related table appears more than once, or when the join is also used for filtering elsewhere:

from sqlalchemy import select
from sqlalchemy.orm import aliased, contains_eager

from blog.models import Comment, Post

approved = aliased(Comment)

stmt = (
    select(Post)
    .join(approved, (approved.post_id == Post.id) & approved.approved.is_(True), isouter=True)
    .options(contains_eager(Post.comments.of_type(approved)))
    .order_by(Post.published_at.desc(), approved.created_at)
)

of_type(approved) tells the ORM which join's columns belong to the relationship. Without it, SQLAlchemy cannot connect the aliased columns to Post.comments and raises.

A nested path populates two levels from two joins:

from sqlalchemy import select
from sqlalchemy.orm import contains_eager

from blog.models import Author, Comment, Post

stmt = (
    select(Post)
    .join(Post.comments.and_(Comment.approved.is_(True)))
    .join(Comment.author)
    .options(
        contains_eager(Post.comments).contains_eager(Comment.author)
    )
)

Chaining mirrors the join chain. This is also where row multiplication becomes worth watching: a post with fifty comments returns fifty rows, each carrying the post's columns and the author's, so the wire cost grows with the product of the collection sizes. For two collection levels, selectinload on the outer level and contains_eager on the filtered one is usually the better mix.

Ordering the collection is done in the query, not on the relationship. Because the collection is assembled from the joined rows in the order they arrive, the ORDER BY has to include the child's ordering after the parent's:

    .order_by(Post.published_at.desc(), Comment.created_at)

A relationship-level order_by is ignored by contains_eager, which surprises people who set it once and expect it everywhere.

One more option is worth knowing for the case where the join exists only for filtering and the collection should not be loaded at all: raiseload(). Combining a filtering join with raiseload(Post.comments) says "find the matching posts, and fail loudly if anything touches the collection", which is often exactly the right contract for a list endpoint — see using raiseload to catch unexpected lazy loads.

Choosing Between the Loader Options

With four options available, the decision is easier when framed as two questions.

Should a parent with no matching child appear? If yes, the filter belongs in an outer join's ON clause or in a selectinload(...and_(...)). If no, an inner join is the point: it does the filtering.

Should the collection be complete or filtered? Complete means selectinload or joinedload; filtered means contains_eager over a join, and read-only.

The four combinations map cleanly:

from sqlalchemy import select
from sqlalchemy.orm import contains_eager, selectinload

from blog.models import Comment, Post

# 1. Posts that have an approved comment; collections not needed.
matching_posts = (
    select(Post)
    .where(Post.comments.any(Comment.approved.is_(True)))     # EXISTS, no duplication
)

# 2. Posts that have an approved comment, with only those comments loaded.
filtered = (
    select(Post)
    .join(Post.comments.and_(Comment.approved.is_(True)))
    .options(contains_eager(Post.comments))
)

# 3. All posts, with only approved comments loaded.
all_posts_filtered_children = (
    select(Post)
    .options(selectinload(Post.comments.and_(Comment.approved.is_(True))))
)

# 4. Posts that have an approved comment, with every comment loaded.
matching_posts_full_children = (
    select(Post)
    .where(Post.comments.any(Comment.approved.is_(True)))
    .options(selectinload(Post.comments))
)

The first is worth noting as the default: when the collection is not displayed, any() renders an EXISTS and avoids both the duplication and the loading question entirely — the point made in fixing cartesian product warnings in SQLAlchemy joins.

The third is the one people reach for contains_eager to do and do not need to: selectinload accepts and_() on the relationship, filtering the collection without affecting which parents come back, in two clean queries with no row multiplication. For "show every post, with its approved comments", that is simpler and safer than a join.

Which leaves contains_eager for its genuine niche: one query, parents restricted by the join, collections populated from it. That is worth having — a list endpoint that shows matching parents with their matching children in a single round trip — and it is narrower than its prominence in older tutorials suggests. When in doubt, start with any() plus selectinload, measure, and reach for contains_eager when the extra query is the thing that needs removing.

Frequently Asked Questions

Related

• Complex Joins and Relationship Loading Strategies — The parent guide: joins, loaders and N+1.
• Using selectinload vs joinedload for N+1 prevention — The two options that load complete collections.
• Fixing cartesian product warnings in SQLAlchemy joins — Why any() is often better than a join.
• Filtering soft-deleted rows with with_loader_criteria — Criteria applied to every load of an entity.