skip to content

In SQLAlchemy, how do you return each customer's newest ticket as Ticket objects, and why must the second reference to Ticket be built with aliased()?

level: middleimportance: should knowfreq 36%

answer

  1. one table, two roles
  2. AliasedClass with the same attributes
  3. outer join to a newer row
  4. aliased() over a subquery
  5. name= sets the row key

basics

~20 s

aliased(Ticket) creates a second, separately named reference to the ticket table, so one select() can compare each ticket with its customer's newer tickets. Without it, joining Ticket to itself raises InvalidRequestError, because SQLAlchemy cannot tell the two sides apart.

solid answer

~40 s

`aliased(Ticket)` returns an `AliasedClass` that has the same mapped attributes as `Ticket` but renders as `ticket AS ticket_1`, or as the `name=` you give it, so the table can appear twice in one FROM clause. For each customer's newest ticket, outer-join an alias `newer` on the same `customer_id` with a later `created_at`, and keep the rows where `newer.id IS NULL`; the selected `Ticket` stays an ordinary entity, and every result is a real `Ticket` object. Without the alias, `select(Ticket).outerjoin(Ticket, ...)` raises `InvalidRequestError` with "Don't know how to join to", since both sides are the same entity. `aliased()` also wraps a subquery: `latest = aliased(Ticket, subq)` turns a ranked subquery back into `Ticket` objects. `name=` sets the key in each `Row`, and `Ticket.customer.of_type(alias)` joins along a relationship to an alias.

code

python · 56 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),
        ]
    )
from sqlalchemy import and_
from sqlalchemy.orm import aliased

with Session(engine) as session:
    newer = aliased(Ticket, name="newer")
    stmt = (
        select(Customer.name, Ticket)
        .join(Ticket.customer)
        .outerjoin(
            newer,
            and_(newer.customer_id == Ticket.customer_id, newer.created_at > Ticket.created_at),
        )
        .where(newer.id.is_(None))
        .order_by(Customer.name)
    )
    print(stmt)
    for customer_name, ticket in session.execute(stmt):
        print(customer_name, "->", ticket.subject)

go deeper

for a junior

Recall that aliased() gives a mapped class a second name in one query, and that objects loaded through it are still ordinary instances.

for a middle

Explain why a self-join needs an alias, how aliased(Ticket, subq) maps a subquery back to Ticket objects, and what name= and of_type() control.

for a senior

Review self-joins for the wrong entity used in a comparison and for ties on the ordering column, and check the emitted SQL rather than trusting the Python.

for a principal

Judge when a per-group query belongs in an ORM select returning entities and when a plain column projection or a database view is the clearer contract.

## What aliased() creates `sqlalchemy.orm.aliased()` takes a mapped class and returns an **`AliasedClass`**: an object with the same mapped attributes as the class (`newer.id`, `newer.created_at`, `newer.customer`) whose SQL renders against an **alias** of the class's table. By default the alias is anonymous (`ticket AS ticket_1`); with `aliased(Ticket, name="newer")` it renders as `ticket AS newer`, and the entity is keyed `newer` in result rows. Objects loaded through an alias are ordinary instances of the class. A `Ticket` loaded via `aliased(Ticket)` is a `Ticket`, lives in the same identity map, and behaves like any other. The alias only changes **which FROM element** the columns are read from. ## Why one class needs two names A query that compares a row with other rows of the same table has to mention the table twice. In SQL that needs two names for it, and the ORM is no different. Writing: ```python select(Ticket).outerjoin(Ticket, Ticket.created_at > Ticket.created_at) ``` does not give SQLAlchemy two tickets to compare; both sides are the same entity. It raises `InvalidRequestError` with the message "Don't know how to join to <Mapper ...; Ticket>", suggesting `select_from()` and an explicit ON clause. Even if it compiled, `Ticket.created_at > Ticket.created_at` compares a column with itself. An alias gives the second role its own columns: `newer.created_at > Ticket.created_at`. ## Newest ticket per customer: the outer-join form The support dashboard wants each customer's most recent ticket. One way: for every ticket, look for a newer ticket of the same customer; if none exists, this ticket is the newest. ```python newer = aliased(Ticket, name="newer") stmt = ( select(Customer.name, Ticket) .join(Ticket.customer) .outerjoin( newer, and_(newer.customer_id == Ticket.customer_id, newer.created_at > Ticket.created_at), ) .where(newer.id.is_(None)) ) ``` The rendered FROM clause reads `ticket JOIN customer ON ... LEFT OUTER JOIN ticket AS newer ON newer.customer_id = ticket.customer_id AND newer.created_at > ticket.created_at`, with `WHERE newer.id IS NULL`. Points to note: - The unaliased `Ticket` is the one returned; `newer` exists only in the FROM and WHERE clauses. - Two tickets with the same `created_at` both survive, so add a tie-breaker (for example `id`) if that matters. - How the database plans this anti-join is a SQL subject; the SQLAlchemy part is giving the table a second name. ## The subquery form: aliased(Ticket, subq) `aliased()` also accepts a **subquery** as its second argument and maps the class onto the subquery's columns. A ranked subquery works well for "top one per group": 1. Build `subq = select(Ticket, rn).subquery()`, where `rn` is `func.row_number().over(partition_by=Ticket.customer_id, order_by=Ticket.created_at.desc()).label("rn")`. 2. Wrap it: `latest = aliased(Ticket, subq, name="latest")`. 3. Select it: `select(Customer.name, latest).join(Customer.tickets.of_type(latest)).where(subq.c.rn == 1)`. Each row's `latest` element is a real `Ticket` object built from the subquery's columns; no second SELECT is issued. By default `aliased()` matches subquery columns to mapped columns by **column correspondence**, which works because the subquery was derived from `Ticket`. For a subquery built from literals or other tables, pass `adapt_on_names=True` to match by name instead. ## Naming aliases and joining along relationships | Goal | Form | |---|---| | control the SQL alias and row key | `aliased(Customer, name="owner")` | | join along a relationship to an alias | `.join(Ticket.customer.of_type(owner))` | | same, with the alias as target | `.join(owner, Ticket.customer)` | | join from an alias | `select(newer).join(newer.customer)` | The `of_type()` form and the two-argument form produce the same SQL. ## Joining the same related class twice Aliases are also how one statement joins along the same relationship twice with different conditions. To find customers who have both an open and a closed ticket: ```python open_t = aliased(Ticket) closed_t = aliased(Ticket) stmt = ( select(Customer) .join(Customer.tickets.of_type(open_t)) .where(open_t.status == "open") .join(Customer.tickets.of_type(closed_t)) .where(closed_t.status == "closed") ) ``` Each alias renders as its own `ticket AS ticket_1` and `ticket AS ticket_2` join, so each WHERE condition applies to a different ticket row. With a single unaliased join, both conditions would apply to the same row and nothing could match. A customer with several matching pairs comes back once per pair, because 2.0-style results are not deduplicated implicitly. ## Pitfalls - Using the base class where the alias was meant (`Ticket.created_at` instead of `newer.created_at`) silently compares the wrong rows. - Forgetting `name=` leaves an anonymous key, so rows are easier to read by position than by name. - `aliased()` never creates a table or a subclass; it is a query-time construct only.

  • How do you get the same result from a subquery that ranks each customer's tickets?
    Label `func.row_number().over(partition_by=Ticket.customer_id, order_by=Ticket.created_at.desc())` as `rn`, build `subq = select(Ticket, rn).subquery()`, wrap it with `latest = aliased(Ticket, subq)`, and select `latest` where `subq.c.rn == 1`. Each row then holds a real `Ticket` built from the subquery's columns, with no extra query.
  • When do you need aliased(..., adapt_on_names=True)?
    When the subquery's columns were not derived from the class's own table, for example literal or computed values labelled to match attribute names. By default `aliased()` links subquery columns to mapped columns by correspondence; `adapt_on_names=True` links them by name instead.

  • select(Ticket).join(Ticket, ...) works because SQLAlchemy aliases the second Ticket itself.
  • aliased(Ticket) defines a new mapped subclass with a table of its own.
  • Objects loaded through aliased(Ticket) are a different type from Ticket.
  • A ranked subquery can only return plain rows, never Ticket objects.
  • aliased(Customer, name='owner') renames the customer table in the database.