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?
answer
- entity and column side by side
- join along the relationship attribute
- keys come from class and attribute names
- the join adds no columns
basics
~10 sSelect 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 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),
]
)
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
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.
Explain how rows are keyed, why join(Ticket.customer) and join(Customer) build their ON clauses differently, and that a join adds no columns.
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.
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]].