Написание операторов SELECT для отображаемых классов ORM

О данном документе

В данном разделе используются отображения ORM, впервые проиллюстрированные в разделе Унифицированный учебник по SQLAlchemy, показанные в разделе Объявление сопоставленных классов.

View the ORM setup for this page.

Операторы SELECT формируются функцией select(), которая возвращает объект Select. Сущности и/или SQL-выражения, которые необходимо вернуть (например, предложение «columns»), передаются в функцию позиционно. Далее для формирования полного оператора используются дополнительные методы, например, метод Select.where(), показанный ниже:

>>> from sqlalchemy import select
>>> stmt = select(User).where(User.name == "spongebob")

При наличии заполненного объекта Select для его выполнения в ORM с целью получения строк обратно, объект передается в Session.execute(), где возвращается объект Result:

>>> result = session.execute(stmt)
{execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',){stop}
>>> for user_obj in result.scalars():
...     print(f"{user_obj.name} {user_obj.fullname}")
spongebob Spongebob Squarepants

Выбор сущностей и атрибутов ORM

Конструкция select() принимает сущности ORM, включая отображенные классы, а также атрибуты уровня класса, представляющие отображенные столбцы, которые при построении преобразуются в элементы ORM-annotated FromClause и ColumnElement.

Объект Select, содержащий ORM-аннотированные сущности, обычно выполняется с использованием объекта Session, а не объекта Connection, чтобы могли действовать возможности, связанные с ORM, в том числе возвращаться экземпляры ORM-mapped объектов. При использовании Connection непосредственно строки результата будут содержать данные только на уровне столбцов.

Выбор сущностей ORM

Ниже мы выбираем из сущности User, создавая Select, который выбирает из сопоставленного Table, к которому сопоставлен User:

>>> result = session.execute(select(User).order_by(User.id))
{execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()

При выборе из сущностей ORM сама сущность возвращается в результат в виде строки с одним элементом, а не в виде ряда отдельных столбцов; например, выше Result возвращает объекты Row, имеющие только один элемент в строке, который держится на объекте User:

>>> result.all()
[(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),),
 (User(id=2, name='sandy', fullname='Sandy Cheeks'),),
 (User(id=3, name='patrick', fullname='Patrick Star'),),
 (User(id=4, name='squidward', fullname='Squidward Tentacles'),),
 (User(id=5, name='ehkrabs', fullname='Eugene H. Krabs'),)]

При выборе списка одноэлементных строк, содержащих сущности ORM, типично пропустить генерацию объектов Row и вместо этого получить непосредственно сущности ORM. Этого проще всего добиться, используя для выполнения метод Session.scalars(), а не метод Session.execute(), так что возвращается объект ScalarResult, который дает отдельные элементы, а не строки:

>>> session.scalars(select(User).order_by(User.id)).all()
{execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
{stop}[User(id=1, name='spongebob', fullname='Spongebob Squarepants'),
 User(id=2, name='sandy', fullname='Sandy Cheeks'),
 User(id=3, name='patrick', fullname='Patrick Star'),
 User(id=4, name='squidward', fullname='Squidward Tentacles'),
 User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')]

Вызов метода Session.scalars() эквивалентен вызову метода Session.execute() для получения объекта Result, а затем вызову метода Result.scalars() для получения объекта ScalarResult.

Одновременный выбор нескольких сущностей ORM

Функция select() принимает любое количество классов ORM и/или выражений столбцов одновременно, в том числе может быть запрошено несколько классов ORM. При SELECT из нескольких ORM-классов они именуются в каждой строке результатов в соответствии с именем класса. В приведенном ниже примере строки результатов для SELECT по отношению к User и Address будут ссылаться на них под именами User и Address:

>>> stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)
>>> for row in session.execute(stmt):
...     print(f"{row.User.name} {row.Address.email_address}")
{execsql}SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] (){stop}
spongebob spongebob@sqlalchemy.org
sandy sandy@sqlalchemy.org
sandy squirrel@squirrelpower.org
patrick pat999@aol.com
squidward stentcl@sqlalchemy.org

Если бы мы хотели присвоить этим сущностям в строках разные имена, то использовали бы конструкцию aliased() с параметром aliased.name для псевдонима с явным именем:

>>> from sqlalchemy.orm import aliased
>>> user_cls = aliased(User, name="user_cls")
>>> email_cls = aliased(Address, name="email")
>>> stmt = (
...     select(user_cls, email_cls)
...     .join(user_cls.addresses.of_type(email_cls))
...     .order_by(user_cls.id, email_cls.id)
... )
>>> row = session.execute(stmt).first()
{execsql}SELECT user_cls.id, user_cls.name, user_cls.fullname,
email.id AS id_1, email.user_id, email.email_address
FROM user_account AS user_cls JOIN address AS email
ON user_cls.id = email.user_id ORDER BY user_cls.id, email.id
[...] ()
{stop}>>> print(f"{row.user_cls.name} {row.email.email_address}")
spongebob spongebob@sqlalchemy.org

Приведенная выше алиасная форма рассматривается далее в Использование Relationship для объединения смежных целей.

Существующая конструкция Select также может быть дополнена ORM-классами и/или выражениями столбцов с помощью метода Select.add_columns(). С помощью этой формы мы можем получить то же самое утверждение, что и выше:

>>> stmt = (
...     select(User).join(User.addresses).add_columns(Address).order_by(User.id, Address.id)
... )
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id

Выбор отдельных атрибутов

Атрибуты сопоставленного класса, такие как User.name и Address.email_address, могут использоваться так же, как Column или другие объекты SQL-выражений при передаче в select(). При создании select(), направленного против определенных столбцов, будут возвращаться объекты Row, а не сущности, такие как User или Address. Каждый Row будет иметь каждый столбец, представленный отдельно:

>>> result = session.execute(
...     select(User.name, Address.email_address)
...     .join(User.addresses)
...     .order_by(User.id, Address.id)
... )
{execsql}SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] (){stop}

Приведенный выше оператор возвращает объекты Row со столбцами name и email_address, как показано в приведенной ниже демонстрации времени выполнения:

>>> for row in result:
...     print(f"{row.name}  {row.email_address}")
spongebob  spongebob@sqlalchemy.org
sandy  sandy@sqlalchemy.org
sandy  squirrel@squirrelpower.org
patrick  pat999@aol.com
squidward  stentcl@sqlalchemy.org

Группировка выбранных атрибутов с помощью пакетов

Конструкция Bundle - это расширяемая ORM-конструкция, позволяющая группировать наборы выражений столбцов в строках результатов:

>>> from sqlalchemy.orm import Bundle
>>> stmt = select(
...     Bundle("user", User.name, User.fullname),
...     Bundle("email", Address.email_address),
... ).join_from(User, Address)
>>> for row in session.execute(stmt):
...     print(f"{row.user.name} {row.user.fullname} {row.email.email_address}")
{execsql}SELECT user_account.name, user_account.fullname, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] (){stop}
spongebob Spongebob Squarepants spongebob@sqlalchemy.org
sandy Sandy Cheeks sandy@sqlalchemy.org
sandy Sandy Cheeks squirrel@squirrelpower.org
patrick Patrick Star pat999@aol.com
squidward Squidward Tentacles stentcl@sqlalchemy.org

Bundle потенциально полезен для создания облегченных представлений и пользовательских группировок столбцов. Bundle также может быть подклассифицирован для возврата альтернативных структур данных; пример см. в Bundle.create_row_processor().

Выбор псевдонимов ORM

Как уже говорилось в учебном пособии Использование псевдонимов, для создания SQL-псевдонима сущности ORM используется конструкция aliased() относительно сопоставленного класса:

>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User)
>>> print(select(u1).order_by(u1.id))
{printsql}SELECT user_account_1.id, user_account_1.name, user_account_1.fullname
FROM user_account AS user_account_1 ORDER BY user_account_1.id

Как и в случае использования Table.alias(), SQL-псевдоним имеет анонимное имя. Для случая выбора сущности из строки с явным именем можно передать и параметр aliased.name:

>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User, name="u1")
>>> stmt = select(u1).order_by(u1.id)
>>> row = session.execute(stmt).first()
{execsql}SELECT u1.id, u1.name, u1.fullname
FROM user_account AS u1 ORDER BY u1.id
[...] (){stop}
>>> print(f"{row.u1.name}")
spongebob

См.также

Конструкция aliased является центральной для нескольких вариантов использования, в том числе:

Получение результатов ORM из текстовых высказываний

ORM поддерживает загрузку сущностей из операторов SELECT, поступающих из других источников. Типичным случаем является текстовый оператор SELECT, который в SQLAlchemy представлен с помощью конструкции text(). Конструкция text() может быть дополнена информацией о столбцах, отображаемых в ORM, которые будут загружены этим оператором; затем эта информация может быть связана с самой сущностью ORM, чтобы объекты ORM могли быть загружены на основе этого оператора.

Дан текстовый SQL-оператор, который мы хотим загрузить из:

>>> from sqlalchemy import text
>>> textual_sql = text("SELECT id, name, fullname FROM user_account ORDER BY id")

Мы можем добавить информацию о столбцах в оператор с помощью метода TextClause.columns(); при его вызове объект TextClause преобразуется в объект TextualSelect, который принимает на себя роль, сравнимую с конструкцией Select. Обычно методу TextClause.columns() передаются объекты Column или эквивалентные им, и в этом случае мы можем использовать ORM-атрибуты класса User напрямую:

>>> textual_sql = textual_sql.columns(User.id, User.name, User.fullname)

Теперь у нас есть ORM-конфигурированная SQL-конструкция, которая, как указано, может загружать отдельно столбцы «id», «name» и «fullname». Чтобы использовать этот оператор SELECT в качестве источника полных сущностей User, мы можем связать эти столбцы с обычной конструкцией Select, поддерживающей ORM, используя метод Select.from_statement():

>>> orm_sql = select(User).from_statement(textual_sql)
>>> for user_obj in session.execute(orm_sql).scalars():
...     print(user_obj)
{execsql}SELECT id, name, fullname FROM user_account ORDER BY id
[...] (){stop}
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

Этот же объект TextualSelect можно также преобразовать в подзапрос с помощью метода TextualSelect.subquery() и связать с сущностью User с помощью конструкции aliased(), аналогично тому, как это рассматривается ниже в Выбор сущностей из подзапросов:

>>> orm_subquery = aliased(User, textual_sql.subquery())
>>> stmt = select(orm_subquery)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
{execsql}SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT id, name, fullname FROM user_account ORDER BY id) AS anon_1
[...] (){stop}
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

Разница между использованием TextualSelect непосредственно с Select.from_statement() и использованием aliased() заключается в том, что в первом случае в результирующем SQL не создается подзапрос. В некоторых случаях это может быть выгодно с точки зрения производительности или сложности.

Выбор сущностей из подзапросов

Конструкция aliased(), рассмотренная в предыдущем разделе, может быть использована с любой конструкцией Subuqery, исходящей из метода типа Select.subquery(), для связи сущностей ORM со столбцами, возвращаемыми этим подзапросом; между столбцами, получаемыми подзапросом, и столбцами, на которые сопоставлена сущность, должно быть отношение соответствия столбцов, то есть подзапрос должен быть в конечном итоге получен из этих сущностей, как в примере ниже:

>>> inner_stmt = select(User).where(User.id < 7).order_by(User.id)
>>> subq = inner_stmt.subquery()
>>> aliased_user = aliased(User, subq)
>>> stmt = select(aliased_user)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
{execsql} SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? ORDER BY user_account.id) AS anon_1
[generated in ...] (7,)
{stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

Выбор сущностей из UNION и других операций над множествами

Функции union() и union_all() являются наиболее распространенными операциями над множествами, которые наряду с другими операциями над множествами, такими как except_(), intersect() и другими, дают объект, известный как CompoundSelect, который состоит из нескольких конструкций Select, объединенных ключевым словом set-operation. ORM-сущности могут быть выбраны из простых составных селектов с помощью метода Select.from_statement(), проиллюстрированного ранее в Получение результатов ORM из текстовых высказываний. В этом методе оператор UNION является полным оператором, который будет выведен на экран, никакие дополнительные критерии не могут быть добавлены после использования Select.from_statement():

>>> from sqlalchemy import union_all
>>> u = union_all(
...     select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).order_by(User.id)
>>> stmt = select(User).from_statement(u)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
{execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = ? ORDER BY id
[generated in ...] (2, 3)
{stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')

Конструкция CompoundSelect может быть более гибко использована в запросе, который в дальнейшем может быть модифицирован путем организации его в подзапрос и связывания с сущностью ORM с помощью aliased(), как это было показано ранее в Выбор сущностей из подзапросов. В приведенном ниже примере мы сначала используем CompoundSelect.subquery() для создания подзапроса оператора UNION ALL, затем упаковываем его в конструкцию aliased(), где он может быть использован как любая другая сопоставленная сущность в конструкции select(), в том числе мы можем добавить фильтрацию и порядок по критериям на основе его экспортированных столбцов:

>>> subq = union_all(
...     select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).subquery()
>>> user_alias = aliased(User, subq)
>>> stmt = select(user_alias).order_by(user_alias.id)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
{execsql}SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id = ?) AS anon_1 ORDER BY anon_1.id
[generated in ...] (2, 3)
{stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')

Присоединяйтесь к

Методы Select.join() и Select.join_from() используются для построения SQL JOIN на основе оператора SELECT.

В данном разделе будут подробно описаны случаи использования этих методов в ORM. Общий обзор их использования с точки зрения Core приведен в Явные предложения FROM и JOIN в Унифицированный учебник по SQLAlchemy.

Использование Select.join() в контексте ORM для запросов 2.0 style в основном эквивалентно, за вычетом унаследованных случаев использования, использованию метода Query.join() в запросах 1.x style.

Простые соединения отношений

Рассмотрим отображение между двумя классами User и Address, с отношением User.addresses, представляющим собой коллекцию объектов Address, связанных с каждым User. Наиболее частое использование Select.join() заключается в создании JOIN вдоль этого отношения, используя атрибут User.addresses в качестве индикатора того, как это должно происходить:

>>> stmt = select(User).join(User.addresses)

Там, где выше, вызов Select.join() вместе с User.addresses приведет к SQL, примерно эквивалентному:

>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

В приведенном примере мы называем User.addresses, переданный в Select.join(), «клаузой включения», то есть он указывает, как должна быть построена часть JOIN «ON».

Совет

Обратите внимание, что использование Select.join() для JOIN от одной сущности к другой влияет на предложение FROM оператора SELECT, но не на предложение columns; оператор SELECT в данном примере будет продолжать возвращать строки только из сущности User. Для одновременного выбора столбцов/сущностей из User и Address необходимо, чтобы сущность Address также была названа в функции select(), либо добавлена в конструкцию Select после этого с помощью метода Select.add_columns(). Примеры обеих этих форм см. в разделе Одновременный выбор нескольких сущностей ORM.

Объединение нескольких соединений

Для построения цепочки соединений можно использовать несколько вызовов Select.join(). Атрибут relationship-bound подразумевает сразу и левую, и правую сторону соединения. Рассмотрим дополнительные сущности Order и Item, где отношение User.orders ссылается на сущность Order, а отношение Order.items ссылается на сущность Item через ассоциативную таблицу order_items. Два вызова Select.join() приведут к тому, что будет создан JOIN сначала из User в Order, а затем из Order в Item. Однако, поскольку Order.items является отношением many to many, оно приводит к двум отдельным элементам JOIN, что в общей сложности составляет три элемента JOIN в результирующем SQL:

>>> stmt = select(User).join(User.orders).join(Order.items)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id

Порядок обращения к методу Select.join() важен лишь в той степени, в какой «левая» сторона того, к чему мы хотим присоединиться, должна присутствовать в списке FROM до того, как мы укажем новую цель. <<<Например, Select.join() не будет знать, как правильно присоединиться, если мы укажем select(User).join(Order.items).join(User.orders), и выдаст ошибку. В правильной практике метод Select.join() вызывается таким образом, чтобы соответствовать тому, как мы хотели бы видеть клаузулы JOIN в SQL, и каждый вызов должен представлять собой четкую связь с тем, что ему предшествует.

Все элементы, которые мы указали в предложении FROM, остаются доступными в качестве потенциальных точек для продолжения присоединения FROM. Мы можем продолжать добавлять другие элементы для присоединения к сущности FROM User, например, добавить к нашей цепочке присоединений отношение User.addresses:

>>> stmt = select(User).join(User.orders).join(Order.items).join(User.addresses)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id
JOIN address ON user_account.id = address.user_id

Присоединения к целевой сущности

Вторая форма Select.join() позволяет использовать в качестве цели любую сопоставленную сущность или основную селектируемую конструкцию. В этом случае Select.join() попытается включить предложение ON для JOIN, используя естественную связь по внешнему ключу между двумя сущностями:

>>> stmt = select(User).join(Address)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

В приведенной форме вызова вызывается Select.join() для автоматического вывода «on clause». Эта форма вызова в конечном итоге приведет к ошибке, если между двумя сопоставленными конструкциями Table либо нет установки ForeignKeyConstraint, либо между ними существует несколько связей ForeignKeyConstraint, так что выбор подходящего ограничения оказывается неоднозначным.

Примечание

При использовании Select.join() или Select.join_from() без указания условия ON сконфигурированные в ORM конструкции relationship() не принимаются во внимание. При попытке вывода условия ON для JOIN учитываются только сконфигурированные отношения ForeignKeyConstraint между сущностями на уровне отображаемых объектов Table.

Присоединения к цели с помощью предложения ON

Третья форма вызова позволяет передавать как целевую сущность, так и предложение ON в явном виде. Пример, включающий SQL-выражение в качестве условия ON, выглядит следующим образом:

>>> stmt = select(User).join(Address, User.id == Address.user_id)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

Предложение ON, основанное на выражении, также может быть relationship()-связанным атрибутом, подобно тому, как это используется в Простые соединения отношений:

>>> stmt = select(User).join(Address, User.addresses)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

Приведенный пример кажется избыточным, поскольку указывает на цель Address двумя разными способами; однако полезность этой формы становится очевидной при присоединении к алиасным сущностям; пример см. в разделе Использование Relationship для объединения смежных целей.

Объединение отношений с пользовательскими критериями ON

Предложение ON, создаваемое конструкцией relationship(), может быть дополнено дополнительными критериями. Это полезно как для быстрого ограничения области действия конкретного соединения по пути отношения, так и для таких случаев, как настройка стратегий загрузчика, например joinedload() и selectinload(). Метод PropComparator.and_() позиционно принимает серию SQL-выражений, которые будут присоединены к предложению ON JOIN через AND. Например, если мы хотим выполнить JOIN от User до Address, но при этом ограничить критерий ON только определенными адресами электронной почты:

>>> stmt = select(User.fullname).join(
...     User.addresses.and_(Address.email_address == "squirrel@squirrelpower.org")
... )
>>> session.execute(stmt).all()
{execsql}SELECT user_account.fullname
FROM user_account
JOIN address ON user_account.id = address.user_id AND address.email_address = ?
[...] ('squirrel@squirrelpower.org',){stop}
[('Sandy Cheeks',)]

См.также

Метод PropComparator.and_() также работает с такими стратегиями загрузчика, как joinedload() и selectinload(). См. раздел Добавление критериев к параметрам загрузчика.

Использование Relationship для объединения смежных целей

При построении объединений с использованием атрибутов relationship()-bound для указания условия ON синтаксис с двумя аргументами, показанный в Присоединения к цели с помощью предложения ON, может быть расширен для работы с конструкцией aliased(), для указания SQL-псевдонима в качестве цели соединения, при этом используя атрибут relationship()-bound для указания условия ON, как в приведенном ниже примере, где сущность User дважды присоединяется к двум различным конструкциям aliased() против сущности Address: :

>>> address_alias_1 = aliased(Address)
>>> address_alias_2 = aliased(Address)
>>> stmt = (
...     select(User)
...     .join(address_alias_1, User.addresses)
...     .where(address_alias_1.email_address == "patrick@aol.com")
...     .join(address_alias_2, User.addresses)
...     .where(address_alias_2.email_address == "patrick@gmail.com")
... )
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

Та же схема может быть выражена более лаконично с помощью модификатора PropComparator.of_type(), который можно применить к атрибуту relationship()-bound, передав ему целевую сущность, чтобы указать цель за один шаг. В приведенном ниже примере с помощью атрибута PropComparator.of_type() создается такой же SQL-оператор, как и только что проиллюстрированный:

>>> print(
...     select(User)
...     .join(User.addresses.of_type(address_alias_1))
...     .where(address_alias_1.email_address == "patrick@aol.com")
...     .join(User.addresses.of_type(address_alias_2))
...     .where(address_alias_2.email_address == "patrick@gmail.com")
... )
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

Чтобы использовать relationship() для построения соединения из псевдосущности, атрибут доступен из конструкции aliased() непосредственно:

>>> user_alias_1 = aliased(User)
>>> print(select(user_alias_1.name).join(user_alias_1.addresses))
{printsql}SELECT user_account_1.name
FROM user_account AS user_account_1
JOIN address ON user_account_1.id = address.user_id

Присоединение к подзапросам

Целью объединения может быть любая «выбираемая» сущность, включая подзапросы. При использовании ORM, как правило, эти цели указываются в виде конструкции aliased(), однако это не является обязательным, особенно если объединенная сущность не возвращается в результатах. Например, для соединения сущности User с сущностью Address, где сущность Address представлена в виде подзапроса, ограниченного строками, мы сначала строим объект Subquery с помощью Select.subquery(), который затем может быть использован в качестве цели метода Select.join():

>>> subq = select(Address).where(Address.email_address == "pat999@aol.com").subquery()
>>> stmt = select(User).join(subq, User.id == subq.c.user_id)
>>> print(stmt)
{printsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = :email_address_1) AS anon_1
ON user_account.id = anon_1.user_id{stop}

Приведенный выше оператор SELECT при вызове через Session.execute() вернет строки, содержащие сущности User, но не содержащие сущностей Address. Для того чтобы включить сущности Address в набор сущностей, возвращаемых в наборе результатов, мы создаем объект aliased() на основе сущности Address и объекта Subquery. Мы также можем пожелать применить имя к конструкции aliased(), например, "address", используемое ниже, чтобы мы могли ссылаться на нее по имени в строке результатов:

>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(address_subq)
>>> for row in session.execute(stmt):
...     print(f"{row.User} {row.address}")
{execsql}SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('pat999@aol.com',){stop}
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')

Присоединение к подзапросам по путям отношений

Форма подзапроса, проиллюстрированная в предыдущем разделе, может быть выражена более конкретно с помощью атрибута relationship()-bound, используя одну из форм, указанных в Использование Relationship для объединения смежных целей. Например, чтобы создать то же самое присоединение, обеспечив при этом присоединение вдоль конкретного relationship(), можно воспользоваться методом PropComparator.of_type(), передав конструкцию aliased(), содержащую объект Subquery, являющийся целью присоединения:

>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(User.addresses.of_type(address_subq))
>>> for row in session.execute(stmt):
...     print(f"{row.User} {row.address}")
{execsql}SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('pat999@aol.com',){stop}
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')

Подзапросы, относящиеся к нескольким сущностям

Подзапрос, содержащий столбцы, охватывающие более одной сущности ORM, может применяться одновременно к нескольким конструкциям aliased() и использоваться в одной и той же конструкции Select для каждой сущности в отдельности. Рендеринг SQL будет продолжать рассматривать все такие конструкции aliased() как один и тот же подзапрос, однако с точки зрения ORM / Python различные возвращаемые значения и атрибуты объектов могут быть упомянуты с помощью соответствующей конструкции aliased().

Например, задан подзапрос, который ссылается как на User, так и на Address:

>>> user_address_subq = (
...     select(User.id, User.name, User.fullname, Address.id, Address.email_address)
...     .join_from(User, Address)
...     .where(Address.email_address.in_(["pat999@aol.com", "squirrel@squirrelpower.org"]))
...     .subquery()
... )

Мы можем создать конструкции aliased() против User и Address, которые ссылаются на один и тот же объект:

>>> user_alias = aliased(User, user_address_subq, name="user")
>>> address_alias = aliased(Address, user_address_subq, name="address")

Конструкция Select, выбирающая из обеих сущностей, выводит подзапрос один раз, но в контексте строки результата может возвращать объекты классов User и Address одновременно:

>>> stmt = select(user_alias, address_alias).where(user_alias.name == "sandy")
>>> for row in session.execute(stmt):
...     print(f"{row.user} {row.address}")
{execsql}SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.id_1, anon_1.email_address
FROM (SELECT user_account.id AS id, user_account.name AS name,
user_account.fullname AS fullname, address.id AS id_1,
address.email_address AS email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE address.email_address IN (?, ?)) AS anon_1
WHERE anon_1.name = ?
[...] ('pat999@aol.com', 'squirrel@squirrelpower.org', 'sandy'){stop}
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='squirrel@squirrelpower.org')

Установка крайнего левого предложения FROM в объединении

В тех случаях, когда левая часть текущего состояния Select не соответствует тому, к чему мы хотим присоединиться, может быть использован метод Select.join_from():

>>> stmt = select(Address).join_from(User, User.addresses).where(User.name == "sandy")
>>> print(stmt)
{printsql}SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Метод Select.join_from() принимает два или три аргумента, либо в виде (<join from>, <onclause>), либо (<join from>, <join to>, [<onclause>]):

>>> stmt = select(Address).join_from(User, Address).where(User.name == "sandy")
>>> print(stmt)
{printsql}SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Для настройки начального предложения FROM для SELECT таким образом, чтобы в дальнейшем можно было использовать Select.join(), можно также использовать метод Select.select_from():

>>> stmt = select(Address).select_from(User).join(Address).where(User.name == "sandy")
>>> print(stmt)
{printsql}SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Совет

На самом деле метод Select.select_from() не имеет решающего значения для порядка следования таблиц в предложении FROM. Если в операторе также имеется ссылка на конструкцию Join, которая ссылается на существующие таблицы в другом порядке, то приоритет имеет конструкция Join. Когда мы используем такие методы, как Select.join() и Select.join_from(), эти методы в конечном итоге создают такой объект Join. Поэтому мы можем видеть, как содержимое Select.select_from() переопределяется в случае, подобном этому:

>>> stmt = select(Address).select_from(User).join(Address.user).where(User.name == "sandy")
>>> print(stmt)
{printsql}SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Там, где выше, мы видим, что в предложении FROM стоит address JOIN user_account, хотя сначала было указано select_from(User). Из-за вызова метода .join(Address.user) оператор в конечном итоге эквивалентен следующему:

>>> from sqlalchemy.sql import join
>>>
>>> user_table = User.__table__
>>> address_table = Address.__table__
>>>
>>> j = address_table.join(user_table, user_table.c.id == address_table.c.user_id)
>>> stmt = (
...     select(address_table)
...     .select_from(user_table)
...     .select_from(j)
...     .where(user_table.c.name == "sandy")
... )
>>> print(stmt)
{printsql}SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Приведенная выше конструкция Join добавляется как еще одна запись в список Select.select_from(), которая заменяет предыдущую запись.

Операторы отношения WHERE

Помимо использования конструкций relationship() в методах Select.join() и Select.join_from(), relationship() также играет роль в построении SQL-выражений, которые обычно используются в предложении WHERE, с помощью метода Select.where().

Формы EXISTS: has() / any()

Конструкция Exists впервые была представлена в Унифицированный учебник по SQLAlchemy в разделе подзапросы EXISTS. Этот объект используется для визуализации ключевого слова SQL EXISTS в сочетании со скалярным подзапросом. Конструкция relationship() предусматривает некоторые вспомогательные методы, которые могут быть использованы для генерации некоторых распространенных стилей запросов в терминах отношения EXISTS.

Для отношения «один-ко-многим», например User.addresses, с помощью метода PropComparator.any() можно получить EXISTS для таблицы address, которая коррелирует с таблицей user_account. Этот метод принимает необязательный критерий WHERE для ограничения строк, сопоставленных подзапросу:

>>> stmt = select(User.fullname).where(
...     User.addresses.any(Address.email_address == "squirrel@squirrelpower.org")
... )
>>> session.execute(stmt).all()
{execsql}SELECT user_account.fullname
FROM user_account
WHERE EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id AND address.email_address = ?)
[...] ('squirrel@squirrelpower.org',){stop}
[('Sandy Cheeks',)]

Поскольку EXISTS, как правило, более эффективен для отрицательного поиска, часто встречающийся запрос состоит в том, чтобы найти сущности, в которых нет ни одной связанной сущности. Для этого используется фраза типа ~User.addresses.any(), чтобы выбрать для User сущностей, которые не имеют связанных строк Address:

>>> stmt = select(User.fullname).where(~User.addresses.any())
>>> session.execute(stmt).all()
{execsql}SELECT user_account.fullname
FROM user_account
WHERE NOT (EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id))
[...] (){stop}
[('Eugene H. Krabs',)]

Метод PropComparator.has() работает в основном так же, как PropComparator.any(), за исключением того, что он используется для отношений «многие-к-одному», например, если бы мы хотели найти все объекты Address, принадлежащие «sandy»:

>>> stmt = select(Address.email_address).where(Address.user.has(User.name == "sandy"))
>>> session.execute(stmt).all()
{execsql}SELECT address.email_address
FROM address
WHERE EXISTS (SELECT 1
FROM user_account
WHERE user_account.id = address.user_id AND user_account.name = ?)
[...] ('sandy',){stop}
[('sandy@sqlalchemy.org',), ('squirrel@squirrelpower.org',)]

Операторы сравнения экземпляров отношений

Атрибут relationship()-bound также предлагает несколько реализаций SQL-конструкций, ориентированных на фильтрацию атрибута relationship()-bound в терминах конкретного экземпляра связанного объекта, которые могут распаковывать соответствующие значения атрибутов из данного экземпляра объекта persistent (или реже detached) и строить критерии WHERE в терминах целевого relationship().

  • Сравнение многих с одним - конкретный экземпляр объекта можно сравнить с отношением «многие к одному», чтобы выбрать строки, в которых внешний ключ целевой сущности совпадает со значением первичного ключа заданного объекта:

    >>> user_obj = session.get(User, 1)
    {execsql}SELECT ...{stop}
    >>> print(select(Address).where(Address.user == user_obj))
    {printsql}SELECT address.id, address.user_id, address.email_address
    FROM address
    WHERE :param_1 = address.user_id
  • сравнение многих с одним не равно - также может быть использован оператор not equals:

    >>> print(select(Address).where(Address.user != user_obj))
    {printsql}SELECT address.id, address.user_id, address.email_address
    FROM address
    WHERE address.user_id != :user_id_1 OR address.user_id IS NULL
  • объект содержится в коллекции «один-ко-многим « - по сути, это версия сравнения «один-ко-многим», выбирающая строки, в которых первичный ключ равен значению внешнего ключа в связанном объекте:

    >>> address_obj = session.get(Address, 1)
    {execsql}SELECT ...{stop}
    >>> print(select(User).where(User.addresses.contains(address_obj)))
    {printsql}SELECT user_account.id, user_account.name, user_account.fullname
    FROM user_account
    WHERE user_account.id = :param_1
  • Объект имеет определенного родителя с точки зрения один-ко-многим - функция with_parent() производит сравнение, возвращающее строки, на которые ссылается данный родитель, что, по сути, аналогично использованию оператора == со стороной многие-ко-многим:

    >>> from sqlalchemy.orm import with_parent
    >>> print(select(Address).where(with_parent(user_obj, User.addresses)))
    {printsql}SELECT address.id, address.user_id, address.email_address
    FROM address
    WHERE :param_1 = address.user_id
Вернуться на верх