A SQLAlchemy query select(Customer).join(Customer.tickets).where(Ticket.status == 'open').options(contains_eager(Customer.tickets)) demands unique(), and once fixed some customers still show closed tickets; why?
answer
- your join, the ORM's routing
- one parent per child row
- loaded attributes are not overwritten
- populate_existing execution option
basics
~20 scontains_eager() fills Customer.tickets from your own join, so each customer repeats once per ticket row and the ORM refuses to return them until unique() is called. Customers already loaded in the Session keep their old, unfiltered collection unless populate_existing=True is set.
solid answer
~40 s`contains_eager()` tells the ORM that the statement's explicit join already carries the related rows, and routes those columns into `Customer.tickets` instead of adding a join of its own. For a collection this is a joined eager load, so the same `Customer` arrives once per ticket row; results from `Session.execute()` are not deduplicated implicitly, and iterating raises `InvalidRequestError` ("The unique() method must be invoked on this Result...") until you call `session.scalars(stmt).unique().all()`. The closed tickets come from the identity map: a `Customer` that the `Session` had already loaded with its full collection is not overwritten, because the ORM does not overwrite loaded attributes unless told to. Add `.execution_options(populate_existing=True)` so the filtered rows replace the collection. The filtered collection is not sticky either: after commit or expiry it reloads in full.
code
python · 58 linesfrom datetime import datetime
from sqlalchemy import ForeignKey, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship
class Base(DeclarativeBase):
pass
class Customer(Base):
__tablename__ = "customer"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
tickets: Mapped[list["Ticket"]] = relationship(back_populates="customer")
class Ticket(Base):
__tablename__ = "ticket"
id: Mapped[int] = mapped_column(primary_key=True)
subject: Mapped[str]
status: Mapped[str]
created_at: Mapped[datetime]
customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id"))
customer: Mapped[Customer] = relationship(back_populates="tickets")
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
acme, globex = Customer(name="Acme"), Customer(name="Globex")
session.add_all(
[
Ticket(subject="Login fails", status="open", created_at=datetime(2026, 10, 1), customer=acme),
Ticket(subject="Invoice wrong", status="closed", created_at=datetime(2026, 10, 3), customer=acme),
Ticket(subject="Export slow", status="open", created_at=datetime(2026, 10, 2), customer=globex),
]
)
from sqlalchemy.orm import contains_eager
with Session(engine) as session:
acme = session.scalars(select(Customer).where(Customer.name == "Acme")).one()
print("loaded first:", [t.subject for t in acme.tickets])
stmt = (
select(Customer)
.join(Customer.tickets)
.where(Ticket.status == "open")
.options(contains_eager(Customer.tickets))
.order_by(Customer.id)
)
customers = session.scalars(stmt).unique().all()
print("no populate_existing:", [(c.name, [t.subject for t in c.tickets]) for c in customers])
customers = (
session.scalars(stmt.execution_options(populate_existing=True)).unique().all()
)
print("populate_existing:", [(c.name, [t.subject for t in c.tickets]) for c in customers])go deeper
Recall that contains_eager() fills a relationship from a join you wrote yourself and does not add one of its own.
Explain why a collection loaded from joined rows needs unique(), and why a many-to-one contains_eager() does not.
Diagnose results that depend on what the Session loaded earlier, apply populate_existing deliberately, and catch contains_eager() without a join in review.
Decide whether partially loaded collections are acceptable in the data layer at all, or whether filtered children should come back as a separate query.
## What contains_eager() does An ORM loader option decides how a relationship is filled. Most options, such as `joinedload()`, write their own SQL. **`contains_eager()`** writes none: it tells the ORM that the statement you built already joins the related table, and asks it to **route the joined columns into the relationship**. That is why it is always paired with `join()` or `outerjoin()`: ```python stmt = ( select(Customer) .join(Customer.tickets) .where(Ticket.status == "open") .options(contains_eager(Customer.tickets)) ) ``` The SELECT now lists both the customer and the ticket columns, and the ORM populates each customer's `tickets` collection from the ticket columns. Because you wrote the join and the WHERE clause, the collection holds only the tickets your SQL returned, here the open ones. This is the documented way to load a **custom-filtered collection**. ## Why the result demands unique() A join from a customer to its tickets returns one row per ticket, so a customer with two open tickets appears in two rows. With a collection loaded from a join, SQLAlchemy uses its joined eager loading machinery, and the ORM installs a check on the `Result`: 1. Results of `Session.execute()` are **not deduplicated implicitly**; legacy `Query` did that silently, 2.0-style results do not. 2. When joined rows feed a collection, the ORM raises `InvalidRequestError: The unique() method must be invoked on this Result, as it contains results that include joined eager loads against collections`. 3. Calling `unique()` applies a filter that keeps the first occurrence of each entity by **primary key identity**: `session.scalars(stmt).unique().all()`. `unique()` deduplicates the customers returned, not the tickets inside each collection; the collection is assembled from the rows either way. A many-to-one `contains_eager(Ticket.customer)` does not multiply rows and does not need `unique()`. ## Why closed tickets still appear The `Session` keeps an **identity map**: one object per primary key. If an earlier step in the same `Session` loaded Acme and touched `acme.tickets`, that collection is already populated with every ticket. When the filtered query returns Acme again, the ORM hands back the **same object** and, by design, **does not overwrite attributes and collections that are already loaded**. Acme still shows its closed ticket, while a customer seen for the first time gets the filtered list. The behaviour depends on what ran earlier, which is why it looks random. The fix is the `populate_existing` execution option: - `stmt.execution_options(populate_existing=True)` makes the query refresh every object it returns, including collections, from the rows it loaded. - It **resets all attributes that were already present, including pending changes**, so make sure pending changes are flushed first; the default autoflush before the query does that. The documentation also notes that a filtered collection is **not sticky**: when the object is expired, which `Session.commit()` and `Session.rollback()` do under default settings, the next access reloads the full collection. ## The many-to-one case The same option fills a scalar reference. On a ticket list joined to customers for filtering, `options(contains_eager(Ticket.customer))` populates `ticket.customer` from the join's columns, so a template reading `ticket.customer.name` emits no further SQL. Without it, the join only filters, and the first read of each customer the `Session` does not already hold issues its own SELECT. A many-to-one join returns one row per ticket, so no parent repeats and `unique()` is not required here. ## contains_eager() compared with the alternatives | Approach | Who writes the join | Collection contents | |---|---|---| | plain `join()` | you | not loaded; lazy-loaded on first access | | `joinedload()` | SQLAlchemy, with an anonymous alias | always the full collection | | `contains_eager()` | you | exactly the rows your join and WHERE returned | The point of `contains_eager()` is reuse: the join you already need for filtering doubles as the source of the related objects, and no second join to the same table is added. The cost is that correctness is now yours: if the WHERE clause filters children, the collection is filtered too, whether or not the code reading it expects that. ## What to check in review | Symptom | Cause | Fix | |---|---|---| | `InvalidRequestError` about `unique()` | collection filled from joined rows | `.unique()` on the result | | some parents show unfiltered children | collection already loaded in the Session | `populate_existing=True` | | `SAWarning`: cartesian product | `contains_eager()` with no `join()` | add the join | | cartesian warning and every ticket in every collection, on an aliased join | path names the base class, so the unaliased table is added | `contains_eager(Customer.tickets.of_type(alias))` | - **No join at all**: `select(Ticket).options(contains_eager(Ticket.customer))` selects `FROM ticket, customer`; SQLAlchemy warns of a cartesian product and that multiple rows were returned for a `uselist=False` attribute. - **Deeper paths** must be spelled from the starting entity, for example `contains_eager(Customer.tickets).contains_eager(...)` for a chain. - **A filtered collection is a view**, not the relationship's truth: code that appends to it and flushes is working on a partial list.
- What happens if you add contains_eager(Ticket.customer) but forget the join()?Nothing joins the two tables, so the statement selects `FROM ticket, customer`, a cartesian product. SQLAlchemy warns that the SELECT has a cartesian product and that multiple rows were returned for the many-to-one attribute, and the results are wrong. `contains_eager()` never adds a join; always pair it with `join()` or `outerjoin()`.
- How do you use contains_eager() when the joined table is aliased?Name the alias in both places with `of_type()`: `.outerjoin(Customer.tickets.of_type(t))` and `.options(contains_eager(Customer.tickets.of_type(t)))`, where `t = aliased(Ticket)`. The option then reads the alias's columns. Without `of_type()` it reads the unaliased `ticket` table, which lands in the FROM list unjoined: a cartesian product, and every ticket in every collection.
- Does the filtered collection stay filtered for the rest of the unit of work?Only until the object is expired. With default settings `Session.commit()` and `Session.rollback()` expire everything, so the next access to `customer.tickets` reloads the full collection. Treat a filtered collection as a read-only view for the current request.
contains_eager() is handing a filing clerk a folder you have already filled: the clerk files exactly what you gave, and, unless told to replace it, leaves any folder already on the shelf untouched.
- contains_eager() adds its own LEFT OUTER JOIN, just like joinedload().
- unique() removes duplicate tickets inside each customer's collection.
- Session.execute() deduplicates customers automatically when a collection is eager-loaded.
- A filtered contains_eager() always replaces collections the Session already loaded.
- Once loaded through a filtered join, customer.tickets stays filtered after commit.