skip to content

In SQLAlchemy 2.1 joined-table inheritance, what does select(Payment) load, and how do selectin_polymorphic() and with_polymorphic() change the SQL?

level: middleimportance: should knowfreq 28%

answer

  1. base table first, subtypes later
  2. one load per object touched
  3. IN on primary keys per subtable
  4. LEFT OUTER JOIN, one statement
  5. only one allows subtype WHERE

basics

~20 s

select(Payment) reads only the payment table; subtype columns load per object on first access. selectin_polymorphic() adds one SELECT per subclass table keyed by IN on primary keys; with_polymorphic() LEFT OUTER JOINs the subtables into one statement.

solid answer

~40 s

Against the base class, SQLAlchemy selects only `payment`; it builds the right subclass from `type`, but `card_last4` and `iban` are unloaded, so reading them costs one SELECT per object. `selectin_polymorphic(Payment, [CardPayment, BankTransfer])` is a loader option: after the base query it emits one SELECT per subclass table present, joined to `payment` and filtered by `payment.id IN (...)`, so N rows of K subtypes cost 1 + K statements. Setting `"polymorphic_load": "selectin"` on each subclass makes that the default. `with_polymorphic(Payment, [...])` instead changes the statement itself into `payment LEFT OUTER JOIN card_payment LEFT OUTER JOIN bank_transfer`, one round trip, and it is the one that lets you filter on `poly.CardPayment.card_last4`. The docs favour selectin for routine loading and with_polymorphic per query when criteria need subtype columns.

code

python · 64 lines
python
from sqlalchemy import ForeignKey, String, create_engine, select
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    Session,
    mapped_column,
    selectin_polymorphic,
    with_polymorphic,
)


class Base(DeclarativeBase):
    pass


class Payment(Base):
    __tablename__ = "payment"
    id: Mapped[int] = mapped_column(primary_key=True)
    amount_cents: Mapped[int]
    type: Mapped[str] = mapped_column(String(20))
    __mapper_args__ = {"polymorphic_on": "type", "polymorphic_identity": "payment"}


class CardPayment(Payment):
    __tablename__ = "card_payment"
    id: Mapped[int] = mapped_column(ForeignKey("payment.id"), primary_key=True)
    card_last4: Mapped[str] = mapped_column(String(4))
    __mapper_args__ = {"polymorphic_identity": "card"}


class BankTransfer(Payment):
    __tablename__ = "bank_transfer"
    id: Mapped[int] = mapped_column(ForeignKey("payment.id"), primary_key=True)
    iban: Mapped[str] = mapped_column(String(34))
    __mapper_args__ = {"polymorphic_identity": "bank_transfer"}


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
    session.add_all(
        [
            CardPayment(amount_cents=1999, card_last4="4242"),
            BankTransfer(amount_cents=50000, iban="DE89370400440532013000"),
            CardPayment(amount_cents=750, card_last4="1111"),
        ]
    )

engine.echo = True
with Session(engine) as session:
    # 1. default: base table only; each subclass attribute loads on access
    rows = session.scalars(select(Payment).order_by(Payment.id)).all()
    print(rows[0].card_last4)

with Session(engine) as session:
    # 2. one extra SELECT per subclass table, keyed by IN on primary keys
    opt = selectin_polymorphic(Payment, [CardPayment, BankTransfer])
    rows = session.scalars(select(Payment).options(opt)).all()

with Session(engine) as session:
    # 3. one statement with LEFT OUTER JOINs; subclass columns usable in WHERE
    poly = with_polymorphic(Payment, [CardPayment, BankTransfer])
    stmt = select(poly).where(poly.CardPayment.card_last4 == "4242")
    rows = session.scalars(stmt).all()

go deeper

for a junior

Remember that a base-class select in a joined hierarchy reads only the base table, and that subtype columns are fetched later unless you ask for them.

for a middle

Explain the three shapes: per-object loads by default, one IN query per subtable with selectin_polymorphic, one LEFT OUTER JOIN statement with with_polymorphic, and which one allows subtype criteria.

for a senior

Show you can read a SQL log, spot per-row subtable SELECTs, and pick polymorphic_load selectin or a per-query with_polymorphic based on whether filtering on subtype columns is needed.

for a principal

Set the team default per subclass so list endpoints stay at a bounded statement count, and decide when wide LEFT OUTER JOINs are acceptable for the planner.

## How joined-table inheritance stores a Payment In **joined-table inheritance** every class in the hierarchy has its own table. The base table `payment` holds the shared columns and the discriminator `type`; `card_payment` and `bank_transfer` hold only their subtype's columns, and each has a primary key that is also a foreign key to `payment.id`. SQLAlchemy treats the base table's primary key as the identity of every object in the hierarchy, so `payment.id` alone locates a `CardPayment`. Querying a **subclass** is straightforward: `select(CardPayment)` renders `FROM payment JOIN card_payment ON payment.id = card_payment.id` and returns only card payments. Querying the **base class** is where the options matter. ## What select(Payment) emits by default By default `select(Payment)` reads **only the `payment` table**. The discriminator tells the ORM which subclass to build for each row, so you do get `CardPayment` and `BankTransfer` instances, but their subtype attributes start **unloaded**. The first time code reads `card_last4` on one of them, the ORM emits a `SELECT` against `card_payment` for that single primary key. - Three rows, all read in full, cost four statements: the base `SELECT` plus one per object. - A page of 200 rows costs up to 201, the classic N+1 shape, but caused by inheritance rather than by a `relationship()`. - Under the asyncio extension that implicit load is not allowed as a plain attribute access (the asyncio material covers the error and its workarounds). - Because only `payment` is in the `FROM` clause, a `WHERE` on `CardPayment.card_last4` cannot be written against this statement without dragging in an unjoined table. ## selectin_polymorphic(): one extra SELECT per subclass table `selectin_polymorphic(Payment, [CardPayment, BankTransfer])` is a **loader option**, passed to `.options()`. After the base `SELECT` returns, it emits one more `SELECT` for each listed subclass that actually appears in the result, joining `payment` to that subclass table and filtering `payment.id IN (...)` by the primary keys just loaded. Three rows of two subtypes cost three statements; two hundred rows of two subtypes still cost three. Points worth knowing: - It changes **loading only**: the main statement is untouched, so you still cannot filter on subtype columns with it. - It takes an explicit list of classes; unlike `with_polymorphic()`, it does not accept `"*"`. - It can be made the default per subclass with `"polymorphic_load": "selectin"` in that subclass's `__mapper_args__`, after which every base-class select behaves as if the option were present. - It chains from a relationship load: `selectinload(Customer.payments).selectin_polymorphic([CardPayment, BankTransfer])`. ## with_polymorphic(): one statement with LEFT OUTER JOINs `with_polymorphic(Payment, [CardPayment, BankTransfer])` builds a special **aliased entity** whose selectable is `payment LEFT OUTER JOIN card_payment LEFT OUTER JOIN bank_transfer`. Selecting it returns every row with every listed subtype's columns in **one** statement, and it exposes namespaces named after each subclass, so `poly.CardPayment.card_last4 == "4242"` is legal SQL criteria while non-card rows can still match other conditions. Pass `"*"` to include every subclass; pass `aliased=True, flat=True` when two polymorphic entities over the same hierarchy appear in one statement. ## Choosing between them | | default | `selectin_polymorphic()` | `with_polymorphic()` | |---|---|---|---| | Statements for N rows of K subtypes | 1 + one per object read | 1 + K | 1 | | Subtype columns in `WHERE` / `ORDER BY` | no | no | yes | | SQL shape | base table only | plain JOIN per subtype table | LEFT OUTER JOIN of all listed tables | | Mapper-level default | none | `polymorphic_load="selectin"` | `polymorphic_load="inline"` | The SQLAlchemy documentation leans towards `selectin_polymorphic()`, or `polymorphic_load="selectin"`, for general loading of subtype columns in a joined hierarchy, because wide LEFT OUTER JOINs are harder on the database planner, and towards `with_polymorphic()` per query when criteria must reach subtype columns. It also warns against the legacy base-mapper `with_polymorphic` argument, whose heuristics rewrite FROM clauses in ways that can break complex statements. ## Mistakes to avoid 1. Treating the per-object loads as harmless because each one is small; count statements in the log. 2. Adding `selectin_polymorphic()` and expecting a subtype filter in `.where()` to work. 3. Listing every subclass in `with_polymorphic()` for a list page that reads none of their columns. 4. Assuming `selectin_polymorphic()` is free: each extra statement still joins `payment` to the subtable, because the option does not yet drop the base table even though its columns are already loaded.

  • How do you load subtype columns for payments reached through Customer.payments rather than selected directly?
    Chain the option from the relationship loader: `selectinload(Customer.payments).selectin_polymorphic([CardPayment, BankTransfer])` adds the per-subtable SELECTs after the collection loads. Alternatively target a polymorphic entity: `selectinload(Customer.payments.of_type(with_polymorphic(Payment, "*")))` loads the collection with the LEFT OUTER JOINs in its one statement.
  • Why not just set with_polymorphic="*" on the base mapper so every query joins every subtable?
    The documentation warns that the mapper-level `with_polymorphic` argument relies on heuristics that rewrite FROM clauses and can interfere with complex statements, especially nested subqueries over the same entity. It also makes every base query pay for every subtable. Prefer `polymorphic_load="selectin"` per subclass, or `"inline"` where you truly want the join, plus `with_polymorphic()` per query.
  • Does selectin_polymorphic() query a subclass table that has no rows in the result?
    No. It emits its extra SELECT only for subclasses whose instances were actually loaded, so a page of card payments only triggers the `card_payment` query even though `BankTransfer` was listed.

Default loading is a clerk who walks to a back-room cabinet once for every folder you open; selectin_polymorphic sends the clerk once per cabinet with the whole list of folder numbers; with_polymorphic tapes every cabinet's sheet to the main folder before handing it over.

  • select(Payment) in a joined hierarchy always JOINs every subclass table.
  • selectin_polymorphic() lets you filter on CardPayment columns in the main query.
  • Subtype attributes come back as None when a base-class select did not load them.
  • with_polymorphic() runs one extra SELECT per subclass table after the main query.
  • Per-object subtype loads are harmless because each statement is tiny.