From 7cf3e79991b3d00d53bfb98cfdab267b67a5cdda Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 25 Oct 2021 22:21:33 -0400 Subject: [PATCH] add sections for ORM use with UNION this is a fairly non-obvious part of the new ORM querying style and needs its own sections Change-Id: Iacb176020d580066c1e0b7f2b40bfbbcb3587d76 --- doc/build/core/selectable.rst | 1 + doc/build/orm/queryguide.rst | 108 ++++++++++++++++++++++++++++- doc/build/tutorial/data_select.rst | 77 ++++++++++++++++++++ 3 files changed, 184 insertions(+), 2 deletions(-) diff --git a/doc/build/core/selectable.rst b/doc/build/core/selectable.rst index b6f1001149..bad7dc8090 100644 --- a/doc/build/core/selectable.rst +++ b/doc/build/core/selectable.rst @@ -75,6 +75,7 @@ The classes here are generated using the constructors listed at :members: .. autoclass:: CompoundSelect + :inherited-members: ClauseElement :members: .. autoclass:: CTE diff --git a/doc/build/orm/queryguide.rst b/doc/build/orm/queryguide.rst index 2b1d9ae89b..1703d2b5a1 100644 --- a/doc/build/orm/queryguide.rst +++ b/doc/build/orm/queryguide.rst @@ -292,7 +292,8 @@ passed as well:: spongebob The :class:`_orm.aliased` construct is also central to making use of subqueries -with the ORM; the section :ref:`orm_queryguide_subqueries` discusses this further. +with the ORM; the sections :ref:`orm_queryguide_subqueries` and +:ref:`orm_queryguide_join_subqueries` discusses this further. .. _orm_queryguide_selecting_text: @@ -374,6 +375,109 @@ perspective. method also works with :term:`DML` statements that support RETURNING. +.. _orm_queryguide_subqueries: + +Selecting Entities from Subqueries +----------------------------------- + +The :func:`_orm.aliased` construct discussed in the previous section +can be used with any :class:`_sql.Subuqery` construct that comes from a +method such as :meth:`_sql.Select.subquery` to link ORM entities to the +columns returned by that subquery; there must be a **column correspondence** +relationship between the columns delivered by the subquery and the columns +to which the entity is mapped, meaning, the subquery needs to be ultimately +derived from those entities, such as in the example below:: + + >>> 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) + {opensql} 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') + +.. seealso:: + + :ref:`tutorial_subqueries_orm_aliased` - in the :ref:`unified_tutorial` + + :ref:`orm_queryguide_join_subqueries` + +.. _orm_queryguide_unions: + +Selecting Entities from UNIONs and other set operations +-------------------------------------------------------- + +The :func:`_sql.union` and :func:`_sql.union_all` functions are the most +common set operations, which along with other set operations such as +:func:`_sql.except_`, :func:`_sql.intersect` and others deliver an object known as +a :class:`_sql.CompoundSelect`, which is composed of multiple +:class:`_sql.Select` constructs joined by a set-operation keyword. ORM entities may +be selected from simple compound selects using the :meth:`_sql.Select.from_statement` +method illustrated previously at :ref:`orm_queryguide_selecting_text`. In +this method, the UNION statement is the complete statement that will be +rendered, no additional criteria can be added after :meth:`_sql.Select.from_statement` +is used:: + + >>> 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) + {opensql}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') + +A :class:`_sql.CompoundSelect` construct can be more flexibly used within +a query that can be further modified by organizing it into a subquery +and linking it to an ORM entity using :func:`_orm.aliased`, +as illustrated previously at :ref:`orm_queryguide_subqueries`. In the +example below, we first use :meth:`_sql.CompoundSelect.subquery` to create +a subquery of the UNION ALL statement, we then package that into the +:func:`_orm.aliased` construct where it can be used like any other mapped +entity in a :func:`_sql.select` construct, including that we can add filtering +and order by criteria based on its exported columns:: + + >>> 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) + {opensql}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') + + +.. seealso:: + + :ref:`tutorial_orm_union` - in the :ref:`unified_tutorial` + + .. _orm_queryguide_joins: Joins @@ -591,7 +695,7 @@ and the second being a custom limiting criteria:: The :meth:`_orm.PropComparator.and_` method also works with loader strategies. See the section :ref:`loader_option_criteria` for an example. -.. _orm_queryguide_subqueries: +.. _orm_queryguide_join_subqueries: Joining to Subqueries ^^^^^^^^^^^^^^^^^^^^^^^ diff --git a/doc/build/tutorial/data_select.rst b/doc/build/tutorial/data_select.rst index 0275319ee1..9f7aafc1b2 100644 --- a/doc/build/tutorial/data_select.rst +++ b/doc/build/tutorial/data_select.rst @@ -857,6 +857,8 @@ first argument of the :meth:`_sql.Select.subquery` or :meth:`_sql.Select.cte` me :meth:`_sql.Select.cte` - examples for CTE including how to use RECURSIVE as well as DML-oriented CTEs +.. _tutorial_subqueries_orm_aliased: + ORM Entity Subqueries/CTEs ~~~~~~~~~~~~~~~~~~~~~~~~~~ @@ -925,6 +927,10 @@ Another example follows, which is exactly the same except it makes use of the User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org') {opensql}ROLLBACK{stop} +.. seealso:: + + :ref:`orm_queryguide_subqueries` - in the :ref:`queryguide_toplevel` + .. _tutorial_scalar_subquery: Scalar and Correlated Subqueries @@ -1101,6 +1107,77 @@ collection that may be referred towards in an enclosing :func:`_sql.select`:: {stop}[('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org'), ('spongebob', 'spongebob@sqlalchemy.org')] {opensql}ROLLBACK{stop} +.. _tutorial_orm_union: + +Selecting ORM Entities from Unions +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +The preceding examples illustrated how to construct a UNION given two +:class:`_schema.Table` objects, to then return database rows. If we wanted +to use a UNION or other set operation to select rows that we then receive +as ORM objects, there are two approaches that may be used. In both cases, +we first construct a :func:`_sql.select` or :class:`_sql.CompoundSelect` +object that represents the SELECT / UNION / etc statement we want to +execute; this statement should be composed against the target +ORM entities or their underlying mapped :class:`_schema.Table` objects:: + + >>> stmt1 = select(User).where(User.name == 'sandy') + >>> stmt2 = select(User).where(User.name == 'spongebob') + >>> u = union_all(stmt1, stmt2) + +For a simple SELECT with UNION that is not already nested inside of a +subquery, these +can often be used in an ORM object fetching context by using the +:meth:`_sql.Select.from_statement` method. With this approach, the UNION +statement represents the entire query; no additional +criteria can be added after :meth:`_sql.Select.from_statement` is used:: + + >>> orm_stmt = select(User).from_statement(u) + >>> with Session(engine) as session: + ... for obj in session.execute(orm_stmt).scalars(): + ... print(obj) + {opensql}BEGIN (implicit) + SELECT user_account.id, user_account.name, user_account.fullname + FROM user_account + WHERE user_account.name = ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname + FROM user_account + WHERE user_account.name = ? + [generated in ...] ('sandy', 'spongebob') + {stop}User(id=2, name='sandy', fullname='Sandy Cheeks') + User(id=1, name='spongebob', fullname='Spongebob Squarepants') + {opensql}ROLLBACK{stop} + +To use a UNION or other set-related construct as an entity-related component in +in a more flexible manner, the :class:`_sql.CompoundSelect` construct may be +organized into a subquery using :meth:`_sql.CompoundSelect.subquery`, which +then links to ORM objects using the :func:`_orm.aliased` function. This works +in the same way introduced at :ref:`tutorial_subqueries_orm_aliased`, to first +create an ad-hoc "mapping" of our desired entity to the subquery, then +selecting from that that new entity as though it were any other mapped class. +In the example below, we are able to add additional criteria such as ORDER BY +outside of the UNION itself, as we can filter or order by the columns exported +by the subquery:: + + >>> user_alias = aliased(User, u.subquery()) + >>> orm_stmt = select(user_alias).order_by(user_alias.id) + >>> with Session(engine) as session: + ... for obj in session.execute(orm_stmt).scalars(): + ... print(obj) + {opensql}BEGIN (implicit) + 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.name = ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname + FROM user_account + WHERE user_account.name = ?) AS anon_1 ORDER BY anon_1.id + [generated in ...] ('sandy', 'spongebob') + {stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants') + User(id=2, name='sandy', fullname='Sandy Cheeks') + {opensql}ROLLBACK{stop} + +.. seealso:: + + :ref:`orm_queryguide_unions` - in the :ref:`queryguide_toplevel` .. _tutorial_exists: -- 2.47.2