Join в ORM: запросы по связям
Навигация читает связь у готового объекта, join фильтрует по чужой таблице в одном запросе: все задачи автора по имени.
Редакция Питоники
Два урока мы ходили по связи шагами: достали автора — попросили его задачи; достали задачу — спросили её автора. Навигация объектами покрывает сценарий «вот объект, покажи его связи». Но половина настоящих запросов звучит иначе: «покажи все задачи, у которых автор — Анна», причём Анна не загружена и известна только по имени. Можно сначала достать её select-ом, потом пойти навигацией — два запроса и лишний объект на руках. Базы данных решают такую задачу одним соединением, и в ORM оно называется join.
Идея join та же, что в чистом SQL: две таблицы склеиваются построчно — каждая задача встаёт рядом со своим автором, — и дальше по этой склейке работают фильтры, сортировки и выборка колонок. Разница в том, что путь соединения ORM может найти сама: связь уже описана ForeignKey и relationship, поэтому указывать колонки руками не нужно.
Фильтр по связанной таблице
Типовая задача урока: все задачи Анны. Запрос собирается из трёх знакомых деталей: select описывает, что возвращаем, join — к кому подключаемся, where — по чему фильтруем.
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
def __repr__(self):
return f"User({self.name})"
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
def __repr__(self):
return f"Task({self.title!r})"
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", done=1, user=anna),
Task(title="багрепорт", done=0, user=anna),
Task(title="презентация", done=1, user=boris),
])
s.commit()
q = s.scalars(
select(Task)
.join(Task.user)
.where(User.name == "Анна")
.order_by(Task.id)
).all()
print("задачи Анны:", [t.title for t in q])
задачи Анны: ['отчёт', 'багрепорт']
Разберём соединение по косточкам. .join(Task.user) — «присоедини таблицу автора к задачам, пройдя по связи user»; SQLAlchemy сам превратит это в INNER JOIN users ON tasks.user_id = users.id. Условие where(User.name == "Анна") фильтрует уже по колонке присоединённой таблицы — в одном select свободно смешиваются поля задач и авторов. Возвращаются только объекты Task: соединение служит фильтром, а не выдачей — пользовательская половина пары в результат не попадает.
Важно, что соединение не меняет тип результата: scalars() отдал обычные объекты Task, полноценные во всём остальном — с рабочей навигацией t.user и всеми полями. Join влился в запрос, но не в модель: задача осталась задачей, и дальнейший код не догадывается, что она добыта хитрее обычного select. Поэтому join спокойно комбинируется со всем арсеналом прошлых уроков — where, order_by и limit из урока 13 надстраиваются на соединённый запрос без сюрпризов.
Обрати внимание на порядок: сначала join, потом where. Читается запрос как предложение — «выбери задачи, соедини с автором, оставь тех, чей автор Анна». Если записать where до join, SQLAlchemy честно поднимет ошибку: фильтровать по таблице, которой в запросе ещё нет, нельзя.
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
def __repr__(self):
return f"Task({self.title!r})"
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", done=1, user=anna),
Task(title="багрепорт", done=0, user=anna),
Task(title="презентация", done=1, user=boris),
])
s.commit()
q = s.scalars(
select(Task)
.join(User)
.where(User.name == "Анна", Task.done == 0)
).all()
print("невыполненные Анны:", [t.title for t in q])
невыполненные Анны: ['багрепорт']
Здесь два упрощения сразу. Во-первых, join(User) без указания пути: между tasks и users ровно один внешний ключ, и ORM однозначно догадывается, как соединять. Указывать путь явно (join(Task.user)) стоит в сложных схемах, где связей несколько, — тогда короткой форме просто неоткуда знать, какая из них нужна. Во-вторых, where принял сразу два условия через запятую: SQLAlchemy склеил их через AND. Запрос стал короче, смысл не изменился: задачи Анны, которые ещё не выполнены.
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
def __repr__(self):
return f"Task({self.title!r})"
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", done=1, user=anna),
Task(title="багрепорт", done=0, user=anna),
Task(title="аудит", done=0, user=boris),
])
s.commit()
q = s.scalars(
select(Task)
.join(User)
.where(User.name == "Анна")
.order_by(Task.done, Task.title)
).all()
print("Анны, по статусу и алфавиту:", [t.title for t in q])
Анны, по статусу и алфавиту: ['багрепорт', 'отчёт']
Соединённый запрос позволяет сортировать и по своим, и по чужим колонкам вперемешку: сначала невыполненные, внутри — по алфавиту. Это и есть главная выгода join перед навигацией: база отдаёт готовый, отфильтрованный и упорядоченный список, а Python лишь перебирает результат.
Пары строк: что и с кем связано
До сих пор join работал молча — соединял, но выдавал только задачи. А если нужны сами пары: «кто какую задачу держит»? Тогда в select перечисляют колонки обеих таблиц, и каждая строка результата становится кортежем из двух значений.
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", user=anna),
Task(title="багрепорт", user=anna),
Task(title="презентация", user=boris),
])
s.commit()
rows = s.execute(
select(User.name, Task.title)
.join(User.tasks)
.order_by(Task.id)
)
for name, title in rows:
print(name, "-", title)
Анна - отчёт Анна - багрепорт Борис - презентация
Здесь соединение пошло от родителя: join(User.tasks) — «подключи задачи каждого пользователя». Выборка вернула кортежи, и цикл распаковал их в name, title — привычный приём по урокам про кортежи. Распаковка — не единственный способ: каждая строка результата знает имена своих колонок, и те же значения доступны как row.name и row.title; распаковка короче для циклов, доступ по имени — когда колонок много и порядок в select не хочется держать в голове. Заметь: пара на задачу, а не на пользователя — у Анны две строки, потому что связей у неё две. Join дублирует родителя для каждой дочерней строки, и это нормальное поведение соединения, а не ошибка.
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", user=anna),
Task(title="багрепорт", user=anna),
Task(title="аудит", user=boris),
Task(title="презентация", user=boris),
])
s.commit()
rows = s.execute(
select(Task.title, User.name)
.join(Task.user)
.order_by(User.name, Task.title)
)
for title, name in rows:
print(name, "-", title)
Анна - багрепорт Анна - отчёт Борис - аудит Борис - презентация
Сортировка по имени автора сработала прямо в базе: группы авторов идут по алфавиту, внутри группы задачи тоже. Заметь, как свободно чувствуют себя колонки обеих таблиц в одном order_by — соединённый запрос для базы один, и все её привычные механизмы к нему применимы.
Связи читаются в обе стороны — и join тоже: join(Task.user) идёт от задачи к автору, join(User.tasks) — от автора к задачам. SQLAlchemy построит одинаковое соединение, различается лишь направление повествования: выбирай то, что читается естественнее в конкретном запросе.
Соединения цепляются друг за друга: если бы у задач были ещё и комментарии, запрос дописал бы второй join — и в where вошли бы колонки уже трёх таблиц. Схема одна: каждая связь в модели — потенциальное звено цепочки, и ORM прокладывает его тем же автоматически найденным условием. Ограничений на длину нет; ограничение — читаемость запроса, после которой пора выносить его в отдельные функции-конструкторы.
select(Task.title, Comment.body)
.join(Comment) # задачи -> комментарии
.join(Comment.author) # комментарии -> авторы
.where(User.name == "Анна")
# SELECT task.title, comment.body
# FROM task
# JOIN comment ON comment.task_id = task.id
# JOIN "user" ON "user".id = comment.author_id
# WHERE "user".name = 'Анна'
Явный join или навигация?
Теперь у нас два инструмента для одной связи, и граница между ними простая. Join нужен тогда, когда условие звучит про другую таблицу: навигация читает связь у готового объекта, а join фильтрует её в одном запросе. Пока задача — «показать задачи вот этого автора», навигация из прошлого урока решает всё; как только «показать задачи всех авторов с именем Анна» или «отсортировать задачи по имени автора» — без join не обойтись.
| Задача | Инструмент | Запросов к базе |
|---|---|---|
| задачи конкретного объекта user | навигация user.tasks | 1 при обращении |
| все задачи по имени автора | join + where | 1 |
| пары «кто — что» одним списком | join + select колонок | 1 |
| сортировка по колонке другой таблицы | join + order_by | 1 |
Один нюанс, о который спотыкаются все: where по колонке User без join не работает сам собой. Запрос select(Task).where(User.name == "Анна") SQLAlchemy выполнит как декартово произведение — обе таблицы попадут в FROM без условия связи — и сопроводит его предупреждением. Фильтр по чужой таблице без join — всегда сигнал, что в запросе не хватает соединения: увидел чужую колонку в where — проверь, есть ли сверху join.
Внешнее соединение: кто остался без пары
Обычный join — внутренний: строки без пары исчезают из результата. Пользователь без задач в запрос «кто — что» просто не попадёт, и иногда это скрывает ровно то, что ты искал: пустые списки, забытых авторов. Чтобы остались все строки одной стороны, а недостающая половина превратилась в NULL, берут внешнее соединение — outerjoin.
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
vera = User(name="Вера")
s.add_all([anna, boris, vera])
s.flush()
s.add_all([
Task(title="отчёт", user=anna),
Task(title="презентация", user=boris),
])
s.commit()
rows = s.execute(
select(User.name, Task.title)
.outerjoin(User.tasks)
.order_by(User.id, Task.id)
)
for name, title in rows:
print(name, "|", title)
Анна | отчёт Борис | презентация Вера | None
Вера в списке, хотя у неё ни одной задачи: её строка соединилась с пустотой, и на месте задачи стоит None. Внутренний join показал бы только Анну и Бориса. Практическое правило: «показать всех, даже без связанного» — это всегда outerjoin; отчёты вида «у кого нет ни одной задачи» строятся именно так, а уточняющий фильтр where(Task.id == None) оставляет в результате только пустые половины.
Куда ещё годится внешнее соединение? Вспомни счётчики из реальных интерфейсов: «Борис (1)» рядом с именем, ноль рядом с В Верой. Если считать задачи каждого пользователя обычным join, пользователи без задач выпадут из отчёта — и вместо честного нуля их имена исчезнут со страницы. Outerjoin сохраняет пустые половины, и в следующем уроке мы поверх него посчитаем задачи каждого автора, получив ноль там, где задач нет. Соединение и агрегация работают в паре: join собирает строки, group_by считает их.
SELECT task.title, "user".name
FROM task
INNER JOIN "user" ON task.user_id = "user".id
WHERE "user".name = 'Анна';
-- тот же запрос с outerjoin:
SELECT "user".name, task.title
FROM "user"
LEFT OUTER JOIN task ON task.user_id = "user".id;
Что дальше
Join замкнул картину: связь описывается один раз ForeignKey и relationship, а используется тремя способами — навигацией для чтения у объекта, join для фильтрации и пар, outerjoin для непарных строк. Выбор может идти и по собственным, и по чужим колонкам, а соединений в одном запросе бывает несколько — каждая связь добавляет в запрос ещё одну таблицу.
Следующий логичный вопрос задаёт любой руководитель проекта: «а у кого сколько задач?» Считать по одному join-запросу на каждого автора — расточительство; база умеет группировать строки и считать сама, выдавая готовый отчёт одним запросом. Функции count и sum, группировка group_by и фильтр по группам having — в уроке 12; поверх соединений из сегодняшнего урока они и зарабатывают свою силу.
Соединение описывается один раз — ForeignKey в схеме, — а используется тремя способами: навигация читает, join фильтрует, outerjoin щадит пустые списки.
Сначала предскажи ответ в голове — это главный навык программиста.
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", done=1, user=anna),
Task(title="багрепорт", done=0, user=anna),
Task(title="аудит", done=0, user=boris),
])
s.commit()
q = s.scalars(
select(Task).join(User).where(User.name == "Анна", Task.done == 1)
).all()
print([t.title for t in q])
from sqlalchemy import create_engine, ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
vera = User(name="Вера")
s.add_all([anna, vera])
s.flush()
s.add(Task(title="отчёт", user=anna))
s.commit()
rows = s.execute(
select(User.name, Task.title).outerjoin(User.tasks)
).all()
print(len(rows))
1. Что делает .join(Task.user) в запросе select(Task)?
2. Когда работает короткая форма join(User) без указания пути?
3. Что вернёт select(User.name, Task.title).join(User.tasks)?
4. Чем outerjoin отличается от join?
5. Когда достаточно навигации user.tasks без всякого join?
Даны три автора — Анна, Борис и Вера — и задачи: «отчёт» (выполнена) и «багрепорт» (не выполнена) у Анны, «аудит» (не выполнена) у Бориса; у Веры задач нет. Через join выведи названия всех невыполненных задач автора с именем «Анна» — одним запросом, без загрузки объекта автора. Затем внешним соединением посчитай, сколько строк в списке «кто — что» вместе с пользователями без задач.
Как сделать join в SQLAlchemy ORM?
Дописать к select вызов join: select(Task).join(Task.user).where(User.name == "Анна"). Путь соединения берётся из ForeignKey автоматически, ON-условие писать не нужно. Возвращаются только объекты главной таблицы (Task), а присоединённая работает фильтром и сортировкой.
Чем join(Task.user) отличается от join(User)?
Это одна и та же связь, записанная с разных сторон: явная форма указывает атрибут связи, короткая — только таблицу. Короткая работает, когда путь между таблицами однозначен; если внешних ключей несколько, SQLAlchemy потребует явный путь, иначе не поймёт, какое соединение строить.
Как найти записи без связанных данных, например пользователей без задач?
Внешним соединением: select(User.name, Task.title).outerjoin(User.tasks) вернёт и тех, у кого задач нет, — с None вместо задачи. Добавив where(Task.id == None), оставишь только пустые половины. Обычный join таких строк не показывает вовсе.
Почему where по колонке другой таблицы даёт предупреждение?
Без join SQLAlchemy включает обе таблицы в FROM без условия связи — получается декартово произведение, где каждая задача склеивается с каждым пользователем. Об этом он предупреждает. Лечение одно: добавить join по связи, чтобы фильтр применялся к соединённым строкам.
Понравился урок? Сошлитесь на него
«Join нужен тогда, когда условие звучит про другую таблицу: навигация читает связь у готового объекта, а join фильтрует её в одном запросе.»
Скопируйте готовую ссылку в формате HTML, Markdown или чистый адрес и вставьте в статью на Habr, VC, Telegram-канал или свой блог — так о проекте узнают новые читатели.
Что читать дальше
SQLAlchemy · Урок 9
Связи один-ко-многим: ForeignKey и relationship
Одна таблица — это заметка, две связанные — база: ForeignKey держит ссылку, relationship превращает её в навигацию по объектам.
SQLAlchemy · Урок 10
Обратные связи: back_populates
Связь, читаемая в обе стороны: task.user знает автора, user.tasks — задачи, а back_populates следит, чтобы концы не разошлись.
SQLAlchemy · Урок 12
Агрегации: count, sum и group_by
Отчёт «у кого сколько задач» одним запросом: база сама считает, группирует и фильтрует — Python получает готовые числа.
Похожие уроки по темам
Подобраны автоматически по пересечению тем и ключевых слов.
sqlite3 · Урок 4
WHERE и фильтрация в SQL: условия, LIKE, BETWEEN и IN
Главный фильтр SQL: сравнения и логика с AND, OR, NOT, шаблоны LIKE с % и _, диапазоны BETWEEN, списки IN — и классическая ловушка = NULL.
sql wherewhere sql
Pandas · Урок 8
Новые столбцы в Pandas: apply, map и векторные операции над колонками
Добавляем в DataFrame выручку, скидки и категории: векторные операции, np.where, map и apply — и разбираемся, почему apply по строкам почти всегда лишний.
np.where pandasnp.select pandas
SQLAlchemy · Урок 6
select: первые запросы к таблице
select(Task) возвращает строки-объекты: перебор циклом, одна строка через first(), одно значение через scalar().
sqlalchemy select запросыsqlalchemy select