Реализация LEFT OUTER JOIN в SQLAlchemy на примере Python

Быстрый ответ

Чтобы выполнить Left Outer Join в SqlAlchemy, примените параметр isouter=True в методе join(). Пример приведён ниже:

# Между классами Users и Addresses установлена связь
results = session.query(Users).join(Addresses, Users.id == Addresses.user_id, isouter=True).all()

Этот код позволяет извлечь всех Users и их Addresses, включая пользователей без адресов.

В ORM доступен также метод outerjoin, который упростит выполнение задачи:

results = session.query(Users).outerjoin(Users.addresses).all()

Разбор ключевых компонентов

Давайте подробнее рассмотрим методы работы с более сложными случаями.

Определение связей

Важно точно определить связи между моделями:

# Классы User и Address теперь связываются сложным отношением
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    addresses = relationship("Address", backref="user")

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))

Введение backref позволит установить двунаправленную связь: так, Users смогут обращаться к Addresses и наоборот.

Подробнее об этом расскажет наш спикер на видео

Спецификация условий соединения

Если связь отсутствует или нужно применить условие соединения, outerjoin() отлично справится с этим:

# Задайте свои правила для объединения таблиц
results = session.query(Users).outerjoin(Addresses, Users.id == Addresses.user_id).all()

Исключение пустых соединений

Чтобы отделить успешные пары в SQL 'LEFT OUTER JOIN', применяйте фильтрацию:

# Исключаем пользователей без пары, по образу и подобию отбора в SQL-Тиндер
results = session.query(Users).outerjoin(Addresses).filter(Addresses.user_id != None).all()

Понимание результатов Left Outer Join

Чтобы понять, что представляют собой результаты объединения, нужно знать, чего ожидать. Давайте разберёмся.

Обработка NULL во внешних ключах

Для того чтобы выявить Users, у которых нет адресов, следует обратить внимание на записи с пустыми внешними ключами:

# Сосредоточим внимание на пользователях, у которых нет адреса.
results = session.query(Users).outerjoin(Addresses).filter(Addresses.id == None).all()

Выбор определенных столбцов

Мы можем запросить только те поля таблиц, которые нам необходимы:

# Выбираем только значимые для нас данные
results = session.query(Users.name, Addresses.description).outerjoin(Addresses).all()

Кастомизация результата запроса

Можно настроить left outer join в соответствии с особыми требованиями:

# Для достижения особых результатов допускается применение своих условий в SQL!
results = session.query(Users).outerjoin(Addresses, and_(Users.id == Addresses.user_id, Addresses.type == 'home')).all()

Визуализация

LEFT OUTER JOIN можно представить в виде танца, и не простого, а танго:

🕺 (ЛЕВЫЙ) = Танцор, владеющий положением, точно знает свои движения.

💃 (ПРАВЫЙ) = Танцор, который присоединяется, только когда движения совместимы.

И музыка начинается! 🎶

На танцполе:

🕺💃 = Идеальное сочетание! Синхронные движения в такт.

🕺👣 = Соло-танец! Лидер не останавливается, даже когда партнёр отступает.

В LEFT OUTER JOIN, все замечают полноту исполнения, не пропуская ни одной детали ведущего танцора.

Избегание ошибок и ловушек

С внешними соединениями в SQLAlchemy следует обращаться осторожно, чтобы избежать ошибок. Несколько полезных советов:

Избегание двусмысленности в связях

Будьте точны и однозначны, особенно если в модели есть несколько внешних ключей, ссылающихся на одну таблицу:

# Четкость обеспечивает успех, а также помогает избежать ошибок.
results = session.query(Users).outerjoin(Users.addresses)  # 'addresses' — атрибут в модели 'User'.

Применение алиасов

Для большей ясности используйте алиасы, если требуется соединить одну таблицу несколько раз:

# Использование алиасов помогает избежать дублирования и путаницы
address_alias = aliased(Address)
results = session.query(User).outerjoin(address_alias, isouter=True).all()

Ясность и точность в работе защищают от НЕОЖИДАННЫХ ошибок.

Полезные материалы

1. Query API — Документация SQLAlchemy 1.4 — информация о выполнении LEFT OUTER JOIN в официальной документации SQLAlchemy.
2. Объектно-реляционное руководство — Документация SQLAlchemy 1.3 — детали ORM-запросов с JOINS в руководстве по SQLAlchemy.
3. Понимание соединений в SQLAlchemy — статья, рассматривающая основы выполнения соединений с SQLAlchemy.
4. Быстрый подсчет в SQLAlchemy (избегая подзапроса query.count()) · GitHub — полезный пример понимания выполнения сложных запросов с использованием соединений в SQLAlchemy.
5. SQLAlchemy — pysheeet — краткий обзор запросов и соединений в SQLAlchemy.