skip to content

With a SQLAlchemy 2.1 Session, how do you list open tickets with each customer's name in one query, and what does each result row contain?

level: middleimportance: must knowfreq 58%

answer

  1. entity and column side by side
  2. join along the relationship attribute
  3. keys come from class and attribute names
  4. the join adds no columns

basics

~10 s

Select both and join along the relationship: select(Ticket, Customer.name).join(Ticket.customer). Session.execute() then yields Row objects pairing a Ticket object with a string, read by unpacking or as row.Ticket and row.name.

solid answer

~40 s

`select()` accepts entities and column attributes together, so `select(Ticket, Customer.name).join(Ticket.customer).where(Ticket.status == "open")` gives one row per open ticket. Through `session.execute()` each `Row` holds a full `Ticket` object and a plain string: unpack it with `for ticket, name in result`, or read `row.Ticket` and `row.name`, since entities are keyed by class name and columns by attribute name. Joining along `Ticket.customer` takes the ON clause from the relationship, while `join(Customer)` infers it from foreign keys and ignores `relationship()`. The join changes only the FROM clause: customer columns appear because you selected one, and `ticket.customer` is not filled by it. In SQLAlchemy 2.1 a type checker sees `Row[Ticket, str]`, though `row.name` itself stays typed `Any`.

code

python · 50 lines
python
from 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),
        ]
    )

with Session(engine) as session:
    stmt = (
        select(Ticket, Customer.name)
        .join(Ticket.customer)
        .where(Ticket.status == "open")
        .order_by(Ticket.id)
    )
    print(stmt)
    for ticket, customer_name in session.execute(stmt):
        print(ticket.subject, "-", customer_name)
    print(session.execute(stmt).first()._fields)

go deeper

for a junior

Recall that select() can name an entity and a column together, and that each row then unpacks into a Ticket object and a plain value.

for a middle

Explain how rows are keyed, why join(Ticket.customer) and join(Customer) build their ON clauses differently, and that a join adds no columns.

for a senior

Notice when a join used only for filtering leaves relationships unloaded, so later attribute reads fire extra SELECTs, and choose a column or contains_eager() accordingly.

for a principal

Decide where list pages should select plain columns rather than tracked objects, weighing typed rows in 2.1 against the convenience of full entities.

## Selecting an entity and a column together SQLAlchemy's `select()` takes any mix of mapped classes and mapped attributes as its columns clause. For the support dashboard, the list of open tickets needs each ticket plus its customer's name: ```python stmt = ( select(Ticket, Customer.name) .join(Ticket.customer) .where(Ticket.status == "open") .order_by(Ticket.id) ) ``` The emitted SQL selects every mapped `ticket` column and one `customer` column: ```sql SELECT ticket.id, ticket.subject, ticket.status, ticket.created_at, ticket.customer_id, customer.name FROM ticket JOIN customer ON customer.id = ticket.customer_id WHERE ticket.status = :status_1 ORDER BY ticket.id ``` Run it with `session.execute(stmt)`. Because each row carries two values, `session.scalars()` is the wrong call here: it would keep only the `Ticket` and drop the names. ## What each Row holds A **`Row`** is SQLAlchemy's tuple-like result row. For this statement every row has two elements: - element 0 is a **`Ticket` object**, built from the ticket columns and tracked by the `Session`; - element 1 is a **plain `str`**, the customer's name, not tracked by anything. You can read a row three ways: 1. Unpack it: `for ticket, name in session.execute(stmt):`. 2. By key: `row.Ticket` and `row.name`. An entity is keyed by its **class name**, a column by its **attribute name**. 3. By position: `row[0]` and `row[1]`. Keys can collide or be unclear. Two columns called `id` (from `Ticket.id` and `Customer.id`) come back with a generated second key, so give them names with `.label("customer_id")`. An entity selected through `aliased(Customer, name="owner")` is keyed `owner`. ## Three ways to write the join | Form | ON clause comes from | Typical use | |---|---|---| | `.join(Ticket.customer)` | the `relationship()` | the normal case | | `.join(Customer)` | foreign keys between the tables | quick joins; fails if zero or several FKs link them | | `.join(Customer, Ticket.customer_id == Customer.id)` | the expression you pass | non-standard conditions | The SQLAlchemy documentation notes that `join(Customer)` without an ON clause does **not** consult `relationship()` at all; only the `ForeignKeyConstraint` objects on the mapped tables are used. Joining along the relationship attribute states intent, and once `relationship()` names its foreign key it keeps working when a second foreign key between the tables makes `join(Customer)` ambiguous. `join_from(Customer, Customer.tickets)` sets the left side explicitly when the select's first entity is not where the join should start. ## What the join does not do A join affects the **FROM clause**, not the columns clause and not the loaded objects: - `select(Ticket).join(Ticket.customer).where(Customer.name == "Acme")` still returns only `Ticket` objects; the join is there to filter. - No customer columns are selected unless you name them in `select()` or add them with `Select.add_columns()`. - `ticket.customer` is **not populated** by the join. The first read of it lazy-loads the customer with a separate SELECT, unless that customer is already in the `Session`. To fill the relationship from the same join, add the `contains_eager(Ticket.customer)` option. Selecting `Customer.name` as a column is often the simplest choice for a list page: the template needs a string, not a second tracked object. ## Typing in SQLAlchemy 2.1 SQLAlchemy 2.0 typed this statement as `Select[Tuple[Ticket, str]]`. **SQLAlchemy 2.1** uses PEP 646 and types it as `Select[Ticket, str]`; executing it yields `Result[Ticket, str]` and rows typed `Row[Ticket, str]`, so unpacking gives a checker `Ticket` and `str` directly. Two limits remain: named access such as `row.name` is still typed `Any`, and only the first ten expressions of a `select()` are typed individually. The old `row._t` workaround for typing a row as a real tuple is no longer needed. ## Two entities in one row The columns clause may also name two whole entities. `select(Ticket, Customer).join(Ticket.customer)` returns rows keyed `Ticket` and `Customer`, each element a tracked object. That differs from selecting `Customer.name` in three ways: - every customer column is loaded, not just the name; - the `Customer` objects enter the `Session`'s identity map, so a ticket's `customer` relationship, when first read, can be resolved from that map without a new SELECT; - a customer with several open tickets appears in several rows, but as **the same object** each time, because the `Session` keeps one object per primary key. Choose the column when the page only displays the name, and the entity when the code goes on to read or change customer attributes. ## Checklist for mixed rows - Use `execute()` and unpack; never `scalars()` on a multi-value select. - Join along the relationship attribute unless you need a custom ON clause. - Label columns whose names clash. - Remember the join neither adds columns nor fills relationships.

  • What happens if a template then reads ticket.customer.name on those Ticket objects?
    The join did not populate `ticket.customer`, so the first read lazy-loads the customer with its own SELECT, once for each distinct customer the `Session` does not already hold. Selecting `Customer.name` as a column avoids that for display; to fill the relationship from the same join, add `contains_eager(Ticket.customer)`.
  • How do you return the whole Customer object instead of only its name?
    Name the entity: `select(Ticket, Customer).join(Ticket.customer)`, or add it to an existing statement with `.add_columns(Customer)`. Each row then holds two tracked objects keyed `Ticket` and `Customer`. Use `aliased(Customer, name="owner")` if you want a different key in the row.

  • select(Ticket).join(Ticket.customer) also returns customer columns, because the join brings them in.
  • Joining along Ticket.customer fills ticket.customer, so reading it later costs no query.
  • join(Customer) builds its ON clause from the relationship() configuration.
  • Each row of select(Ticket, Customer.name) is a Ticket with a name attribute attached.
  • SQLAlchemy 2.1 still types these rows as Row[Tuple[Ticket, str]], so row._t is needed.

Assumes SQLAlchemy 2.1, which types these rows as Row[Ticket, str]; 2.0 typed them as Row[Tuple[Ticket, str]].