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

Выбор сущностей 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

Получение результатов 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')

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

Рассмотрим отображение между двумя классами 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(). Атрибут 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, так что выбор подходящего ограничения оказывается неоднозначным.

Присоединения к цели с помощью предложения 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',)]

Использование 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

>>> 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 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

Формы 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