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

1 files changed, 77 insertions, 0 deletions

diff --git a/doc/build/tutorial/data_select.rst b/doc/build/tutorial/data_select.rst
index 0275319ee..9f7aafc1b 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: