TopolearnFiltering or reporting across tables needs SQL joins, and SQLAlchemy can infer the ON clause from your relationships.
select(User).join(User.posts) joins along a relationship, and SQLAlchemy derives the ON users.id = posts.user_id clause from the mapping. You can also join to an entity, .join(Post), and SQLAlchemy finds the foreign key; or give an explicit condition, .join(Post, Post.user_id == User.id), when there are several or no foreign keys between the tables.
The join lets you filter and select on the joined table: select(User.name, Post.title).join(User.posts).where(Post.title.like("%SQL%")). Note that joining does not load the relationship collection; it only makes the other table available in the query. Loading collections is the job of loader options like selectinload.
.outerjoin(User.posts)or.join(..., isouter=True)keeps users without posts, with NULLs for post columns..select_from(Post).join(Post.author)sets the left side explicitly when the first selected entity isn't where the join should start.- Joining the same table twice requires an alias:
a = aliased(User).
Joining a one-to-many relationship multiplies rows: a user with three posts appears three times. If you select only User objects, the ORM returns duplicates of the same object; use .distinct(), an EXISTS filter (User.posts.any(Post.title == "x")), or aggregate.
Example
from sqlalchemy import ForeignKey, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase): pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
posts: Mapped[list["Post"]] = relationship()
class Post(Base):
__tablename__ = "posts"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str]
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
engine = create_engine("sqlite://"); Base.metadata.create_all(engine)
with Session(engine) as s:
s.add_all([User(name="ada", posts=[Post(title="SQL tips"), Post(title="Joins")]),
User(name="bob")]); s.commit()
print(s.execute(select(User.name, Post.title).join(User.posts)
.where(Post.title.like("%SQL%"))).all()) # [('ada', 'SQL tips')]
print(s.execute(select(User.name, Post.title).outerjoin(User.posts)
.order_by(User.name, Post.title)).all()) # [('ada', 'Joins'), ('ada', 'SQL tips'), ('bob', None)]
print(s.scalars(select(User.name).where(User.posts.any())).all()) # ['ada']Remember
- join(User.posts) infers the ON clause from the relationship
- outerjoin keeps rows with no match
- Joining does not load the relationship collection
- .any() / .has() produce EXISTS filters without duplicating rows
Watch out for
- Joining a collection and getting duplicate parent rows in reports.