ORM Querying Guide

This page is part of the ORM Querying Guide.

Previous: ORM Querying Guide | Next: Writing SELECT statements for Inheritance Mappings

Writing SELECT statements for ORM Mapped Classes

About this Document

This section makes use of ORM mappings first illustrated in the SQLAlchemy Unified Tutorial, shown in the section Declaring Mapped Classes.

View the ORM setup for this page.

SELECT statements are produced by the select() function which returns a Select object. The entities and/or SQL expressions to return (i.e. the “columns” clause) are passed positionally to the function. From there, additional methods are used to generate the complete statement, such as the Select.where() method illustrated below:

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

Given a completed Select object, in order to execute it within the ORM to get rows back, the object is passed to Session.execute(), where a Result object is then returned:

  1. >>> result = session.execute(stmt)
  2. SELECT user_account.id, user_account.name, user_account.fullname
  3. FROM user_account
  4. WHERE user_account.name = ?
  5. [...] ('spongebob',)
  6. >>> for user_obj in result.scalars():
  7. ...     print(f"{user_obj.name} {user_obj.fullname}")
  8. spongebob Spongebob Squarepants

Selecting ORM Entities and Attributes

The select() construct accepts ORM entities, including mapped classes as well as class-level attributes representing mapped columns, which are converted into ORM-annotated FromClause and ColumnElement elements at construction time.

A Select object that contains ORM-annotated entities is normally executed using a Session object, and not a Connection object, so that ORM-related features may take effect, including that instances of ORM-mapped objects may be returned. When using the Connection directly, result rows will only contain column-level data.

Selecting ORM Entities

Below we select from the User entity, producing a Select that selects from the mapped Table to which User is mapped:

  1. >>> result = session.execute(select(User).order_by(User.id))
  2. SELECT user_account.id, user_account.name, user_account.fullname
  3. FROM user_account ORDER BY user_account.id
  4. [...] ()

When selecting from ORM entities, the entity itself is returned in the result as a row with a single element, as opposed to a series of individual columns; for example above, the Result returns Row objects that have just a single element per row, that element holding onto a User object:

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

When selecting a list of single-element rows containing ORM entities, it is typical to skip the generation of Row objects and instead receive ORM entities directly. This is most easily achieved by using the Session.scalars() method to execute, rather than the Session.execute() method, so that a ScalarResult object which yields single elements rather than rows is returned:

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

Calling the Session.scalars() method is the equivalent to calling upon Session.execute() to receive a Result object, then calling upon Result.scalars() to receive a ScalarResult object.

Selecting Multiple ORM Entities Simultaneously

The select() function accepts any number of ORM classes and/or column expressions at once, including that multiple ORM classes may be requested. When SELECTing from multiple ORM classes, they are named in each result row based on their class name. In the example below, the result rows for a SELECT against User and Address will refer to them under the names User and Address:

  1. >>> stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)
  2. >>> for row in session.execute(stmt):
  3. ...     print(f"{row.User.name} {row.Address.email_address}")
  4. SELECT user_account.id, user_account.name, user_account.fullname,
  5. address.id AS id_1, address.user_id, address.email_address
  6. FROM user_account JOIN address ON user_account.id = address.user_id
  7. ORDER BY user_account.id, address.id
  8. [...] ()
  9. spongebob spongebob@sqlalchemy.org
  10. sandy sandy@sqlalchemy.org
  11. sandy squirrel@squirrelpower.org
  12. patrick pat999@aol.com
  13. squidward stentcl@sqlalchemy.org

If we wanted to assign different names to these entities in the rows, we would use the aliased() construct using the aliased.name parameter to alias them with an explicit name:

  1. >>> from sqlalchemy.orm import aliased
  2. >>> user_cls = aliased(User, name="user_cls")
  3. >>> email_cls = aliased(Address, name="email")
  4. >>> stmt = (
  5. ...     select(user_cls, email_cls)
  6. ...     .join(user_cls.addresses.of_type(email_cls))
  7. ...     .order_by(user_cls.id, email_cls.id)
  8. ... )
  9. >>> row = session.execute(stmt).first()
  10. SELECT user_cls.id, user_cls.name, user_cls.fullname,
  11. email.id AS id_1, email.user_id, email.email_address
  12. FROM user_account AS user_cls JOIN address AS email
  13. ON user_cls.id = email.user_id ORDER BY user_cls.id, email.id
  14. [...] ()
  15. >>> print(f"{row.user_cls.name} {row.email.email_address}")
  16. spongebob spongebob@sqlalchemy.org

The aliased form above is discussed further at Using Relationship to join between aliased targets.

An existing Select construct may also have ORM classes and/or column expressions added to its columns clause using the Select.add_columns() method. We can produce the same statement as above using this form as well:

  1. >>> stmt = (
  2. ...     select(User).join(User.addresses).add_columns(Address).order_by(User.id, Address.id)
  3. ... )
  4. >>> print(stmt)
  5. SELECT user_account.id, user_account.name, user_account.fullname,
  6. address.id AS id_1, address.user_id, address.email_address
  7. FROM user_account JOIN address ON user_account.id = address.user_id
  8. ORDER BY user_account.id, address.id

Selecting Individual Attributes

The attributes on a mapped class, such as User.name and Address.email_address, have a similar behavior as that of the entity class itself such as User in that they are automatically converted into ORM-annotated Core objects when passed to select(). They may be used in the same way as table columns are used:

  1. >>> result = session.execute(
  2. ...     select(User.name, Address.email_address)
  3. ...     .join(User.addresses)
  4. ...     .order_by(User.id, Address.id)
  5. ... )
  6. SELECT user_account.name, address.email_address
  7. FROM user_account JOIN address ON user_account.id = address.user_id
  8. ORDER BY user_account.id, address.id
  9. [...] ()

ORM attributes, themselves known as InstrumentedAttribute objects, can be used in the same way as any ColumnElement, and are delivered in result rows just the same way, such as below where we refer to their values by column name within each row:

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

Grouping Selected Attributes with Bundles

The Bundle construct is an extensible ORM-only construct that allows sets of column expressions to be grouped in result rows:

  1. >>> from sqlalchemy.orm import Bundle
  2. >>> stmt = select(
  3. ...     Bundle("user", User.name, User.fullname),
  4. ...     Bundle("email", Address.email_address),
  5. ... ).join_from(User, Address)
  6. >>> for row in session.execute(stmt):
  7. ...     print(f"{row.user.name} {row.user.fullname} {row.email.email_address}")
  8. SELECT user_account.name, user_account.fullname, address.email_address
  9. FROM user_account JOIN address ON user_account.id = address.user_id
  10. [...] ()
  11. spongebob Spongebob Squarepants spongebob@sqlalchemy.org
  12. sandy Sandy Cheeks sandy@sqlalchemy.org
  13. sandy Sandy Cheeks squirrel@squirrelpower.org
  14. patrick Patrick Star pat999@aol.com
  15. squidward Squidward Tentacles stentcl@sqlalchemy.org

The Bundle is potentially useful for creating lightweight views and custom column groupings. Bundle may also be subclassed in order to return alternate data structures; see Bundle.create_row_processor() for an example.

See also

Bundle

Bundle.create_row_processor()

Selecting ORM Aliases

As discussed in the tutorial at Using Aliases, to create a SQL alias of an ORM entity is achieved using the aliased() construct against a mapped class:

  1. >>> from sqlalchemy.orm import aliased
  2. >>> u1 = aliased(User)
  3. >>> print(select(u1).order_by(u1.id))
  4. SELECT user_account_1.id, user_account_1.name, user_account_1.fullname
  5. FROM user_account AS user_account_1 ORDER BY user_account_1.id

As is the case when using Table.alias(), the SQL alias is anonymously named. For the case of selecting the entity from a row with an explicit name, the aliased.name parameter may be passed as well:

  1. >>> from sqlalchemy.orm import aliased
  2. >>> u1 = aliased(User, name="u1")
  3. >>> stmt = select(u1).order_by(u1.id)
  4. >>> row = session.execute(stmt).first()
  5. SELECT u1.id, u1.name, u1.fullname
  6. FROM user_account AS u1 ORDER BY u1.id
  7. [...] ()
  8. >>> print(f"{row.u1.name}")
  9. spongebob

See also

The aliased construct is central for several use cases, including:

Getting ORM Results from Textual Statements

The ORM supports loading of entities from SELECT statements that come from other sources. The typical use case is that of a textual SELECT statement, which in SQLAlchemy is represented using the text() construct. A text() construct can be augmented with information about the ORM-mapped columns that the statement would load; this can then be associated with the ORM entity itself so that ORM objects can be loaded based on this statement.

Given a textual SQL statement we’d like to load from:

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

We can add column information to the statement by using the TextClause.columns() method; when this method is invoked, the TextClause object is converted into a TextualSelect object, which takes on a role that is comparable to the Select construct. The TextClause.columns() method is typically passed Column objects or equivalent, and in this case we can make use of the ORM-mapped attributes on the User class directly:

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

We now have an ORM-configured SQL construct that as given, can load the “id”, “name” and “fullname” columns separately. To use this SELECT statement as a source of complete User entities instead, we can link these columns to a regular ORM-enabled Select construct using the Select.from_statement() method:

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

The same TextualSelect object can also be converted into a subquery using the TextualSelect.subquery() method, and linked to the User entity to it using the aliased() construct, in a similar manner as discussed below in Selecting Entities from Subqueries:

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

The difference between using the TextualSelect directly with Select.from_statement() versus making use of aliased() is that in the former case, no subquery is produced in the resulting SQL. This can in some scenarios be advantageous from a performance or complexity perspective.

Selecting Entities from Subqueries

The aliased() construct discussed in the previous section can be used with any Subuqery construct that comes from a method such as 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:

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

See also

ORM Entity Subqueries/CTEs - in the SQLAlchemy Unified Tutorial

Joining to Subqueries

Selecting Entities from UNIONs and other set operations

The union() and union_all() functions are the most common set operations, which along with other set operations such as except_(), intersect() and others deliver an object known as a CompoundSelect, which is composed of multiple Select constructs joined by a set-operation keyword. ORM entities may be selected from simple compound selects using the Select.from_statement() method illustrated previously at Getting ORM Results from Textual Statements. In this method, the UNION statement is the complete statement that will be rendered, no additional criteria can be added after Select.from_statement() is used:

  1. >>> from sqlalchemy import union_all
  2. >>> u = union_all(
  3. ...     select(User).where(User.id < 2), select(User).where(User.id == 3)
  4. ... ).order_by(User.id)
  5. >>> stmt = select(User).from_statement(u)
  6. >>> for user_obj in session.execute(stmt).scalars():
  7. ...     print(user_obj)
  8. SELECT user_account.id, user_account.name, user_account.fullname
  9. FROM user_account
  10. WHERE user_account.id < ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
  11. FROM user_account
  12. WHERE user_account.id = ? ORDER BY id
  13. [generated in ...] (2, 3)
  14. User(id=1, name='spongebob', fullname='Spongebob Squarepants')
  15. User(id=3, name='patrick', fullname='Patrick Star')

A 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 aliased(), as illustrated previously at Selecting Entities from Subqueries. In the example below, we first use CompoundSelect.subquery() to create a subquery of the UNION ALL statement, we then package that into the aliased() construct where it can be used like any other mapped entity in a select() construct, including that we can add filtering and order by criteria based on its exported columns:

  1. >>> subq = union_all(
  2. ...     select(User).where(User.id < 2), select(User).where(User.id == 3)
  3. ... ).subquery()
  4. >>> user_alias = aliased(User, subq)
  5. >>> stmt = select(user_alias).order_by(user_alias.id)
  6. >>> for user_obj in session.execute(stmt).scalars():
  7. ...     print(user_obj)
  8. SELECT anon_1.id, anon_1.name, anon_1.fullname
  9. FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
  10. FROM user_account
  11. WHERE user_account.id < ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
  12. FROM user_account
  13. WHERE user_account.id = ?) AS anon_1 ORDER BY anon_1.id
  14. [generated in ...] (2, 3)
  15. User(id=1, name='spongebob', fullname='Spongebob Squarepants')
  16. User(id=3, name='patrick', fullname='Patrick Star')

See also

Selecting ORM Entities from Unions - in the SQLAlchemy Unified Tutorial

Joins

The Select.join() and Select.join_from() methods are used to construct SQL JOINs against a SELECT statement.

This section will detail ORM use cases for these methods. For a general overview of their use from a Core perspective, see Explicit FROM clauses and JOINs in the SQLAlchemy Unified Tutorial.

The usage of Select.join() in an ORM context for 2.0 style queries is mostly equivalent, minus legacy use cases, to the usage of the Query.join() method in 1.x style queries.

Simple Relationship Joins

Consider a mapping between two classes User and Address, with a relationship User.addresses representing a collection of Address objects associated with each User. The most common usage of Select.join() is to create a JOIN along this relationship, using the User.addresses attribute as an indicator for how this should occur:

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

Where above, the call to Select.join() along User.addresses will result in SQL approximately equivalent to:

  1. >>> print(stmt)
  2. SELECT user_account.id, user_account.name, user_account.fullname
  3. FROM user_account JOIN address ON user_account.id = address.user_id

In the above example we refer to User.addresses as passed to Select.join() as the “on clause”, that is, it indicates how the “ON” portion of the JOIN should be constructed.

Tip

Note that using Select.join() to JOIN from one entity to another affects the FROM clause of the SELECT statement, but not the columns clause; the SELECT statement in this example will continue to return rows from only the User entity. To SELECT columns / entities from both User and Address at the same time, the Address entity must also be named in the select() function, or added to the Select construct afterwards using the Select.add_columns() method. See the section Selecting Multiple ORM Entities Simultaneously for examples of both of these forms.

Chaining Multiple Joins

To construct a chain of joins, multiple Select.join() calls may be used. The relationship-bound attribute implies both the left and right side of the join at once. Consider additional entities Order and Item, where the User.orders relationship refers to the Order entity, and the Order.items relationship refers to the Item entity, via an association table order_items. Two Select.join() calls will result in a JOIN first from User to Order, and a second from Order to Item. However, since Order.items is a many to many relationship, it results in two separate JOIN elements, for a total of three JOIN elements in the resulting SQL:

  1. >>> stmt = select(User).join(User.orders).join(Order.items)
  2. >>> print(stmt)
  3. SELECT user_account.id, user_account.name, user_account.fullname
  4. FROM user_account
  5. JOIN user_order ON user_account.id = user_order.user_id
  6. JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
  7. JOIN item ON item.id = order_items_1.item_id

The order in which each call to the Select.join() method is significant only to the degree that the “left” side of what we would like to join from needs to be present in the list of FROMs before we indicate a new target. Select.join() would not, for example, know how to join correctly if we were to specify select(User).join(Order.items).join(User.orders), and would raise an error. In correct practice, the Select.join() method is invoked in such a way that lines up with how we would want the JOIN clauses in SQL to be rendered, and each call should represent a clear link from what precedes it.

All of the elements that we target in the FROM clause remain available as potential points to continue joining FROM. We can continue to add other elements to join FROM the User entity above, for example adding on the User.addresses relationship to our chain of joins:

  1. >>> stmt = select(User).join(User.orders).join(Order.items).join(User.addresses)
  2. >>> print(stmt)
  3. SELECT user_account.id, user_account.name, user_account.fullname
  4. FROM user_account
  5. JOIN user_order ON user_account.id = user_order.user_id
  6. JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
  7. JOIN item ON item.id = order_items_1.item_id
  8. JOIN address ON user_account.id = address.user_id

Joins to a Target Entity

A second form of Select.join() allows any mapped entity or core selectable construct as a target. In this usage, Select.join() will attempt to infer the ON clause for the JOIN, using the natural foreign key relationship between two entities:

  1. >>> stmt = select(User).join(Address)
  2. >>> print(stmt)
  3. SELECT user_account.id, user_account.name, user_account.fullname
  4. FROM user_account JOIN address ON user_account.id = address.user_id

In the above calling form, Select.join() is called upon to infer the “on clause” automatically. This calling form will ultimately raise an error if either there are no ForeignKeyConstraint setup between the two mapped Table constructs, or if there are multiple ForeignKeyConstraint linkages between them such that the appropriate constraint to use is ambiguous.

Note

When making use of Select.join() or Select.join_from() without indicating an ON clause, ORM configured relationship() constructs are not taken into account. Only the configured ForeignKeyConstraint relationships between the entities at the level of the mapped Table objects are consulted when an attempt is made to infer an ON clause for the JOIN.

Joins to a Target with an ON Clause

The third calling form allows both the target entity as well as the ON clause to be passed explicitly. A example that includes a SQL expression as the ON clause is as follows:

  1. >>> stmt = select(User).join(Address, User.id == Address.user_id)
  2. >>> print(stmt)
  3. SELECT user_account.id, user_account.name, user_account.fullname
  4. FROM user_account JOIN address ON user_account.id = address.user_id

The expression-based ON clause may also be a relationship()-bound attribute, in the same way it’s used in Simple Relationship Joins:

  1. >>> stmt = select(User).join(Address, User.addresses)
  2. >>> print(stmt)
  3. SELECT user_account.id, user_account.name, user_account.fullname
  4. FROM user_account JOIN address ON user_account.id = address.user_id

The above example seems redundant in that it indicates the target of Address in two different ways; however, the utility of this form becomes apparent when joining to aliased entities; see the section Using Relationship to join between aliased targets for an example.

Combining Relationship with Custom ON Criteria

The ON clause generated by the relationship() construct may be augmented with additional criteria. This is useful both for quick ways to limit the scope of a particular join over a relationship path, as well as for cases like configuring loader strategies such as joinedload() and selectinload(). The PropComparator.and_() method accepts a series of SQL expressions positionally that will be joined to the ON clause of the JOIN via AND. For example if we wanted to JOIN from User to Address but also limit the ON criteria to only certain email addresses:

  1. >>> stmt = select(User.fullname).join(
  2. ...     User.addresses.and_(Address.email_address == "squirrel@squirrelpower.org")
  3. ... )
  4. >>> session.execute(stmt).all()
  5. SELECT user_account.fullname
  6. FROM user_account
  7. JOIN address ON user_account.id = address.user_id AND address.email_address = ?
  8. [...] ('squirrel@squirrelpower.org',)
  9. [('Sandy Cheeks',)]

See also

The PropComparator.and_() method also works with loader strategies such as joinedload() and selectinload(). See the section Adding Criteria to loader options.

Using Relationship to join between aliased targets

When constructing joins using relationship()-bound attributes to indicate the ON clause, the two-argument syntax illustrated in Joins to a Target with an ON Clause can be expanded to work with the aliased() construct, to indicate a SQL alias as the target of a join while still making use of the relationship()-bound attribute to indicate the ON clause, as in the example below, where the User entity is joined twice to two different aliased() constructs against the Address entity:

  1. >>> address_alias_1 = aliased(Address)
  2. >>> address_alias_2 = aliased(Address)
  3. >>> stmt = (
  4. ...     select(User)
  5. ...     .join(address_alias_1, User.addresses)
  6. ...     .where(address_alias_1.email_address == "patrick@aol.com")
  7. ...     .join(address_alias_2, User.addresses)
  8. ...     .where(address_alias_2.email_address == "patrick@gmail.com")
  9. ... )
  10. >>> print(stmt)
  11. SELECT user_account.id, user_account.name, user_account.fullname
  12. FROM user_account
  13. JOIN address AS address_1 ON user_account.id = address_1.user_id
  14. JOIN address AS address_2 ON user_account.id = address_2.user_id
  15. WHERE address_1.email_address = :email_address_1
  16. AND address_2.email_address = :email_address_2

The same pattern may be expressed more succinctly using the modifier PropComparator.of_type(), which may be applied to the relationship()-bound attribute, passing along the target entity in order to indicate the target in one step. The example below uses PropComparator.of_type() to produce the same SQL statement as the one just illustrated:

  1. >>> print(
  2. ...     select(User)
  3. ...     .join(User.addresses.of_type(address_alias_1))
  4. ...     .where(address_alias_1.email_address == "patrick@aol.com")
  5. ...     .join(User.addresses.of_type(address_alias_2))
  6. ...     .where(address_alias_2.email_address == "patrick@gmail.com")
  7. ... )
  8. SELECT user_account.id, user_account.name, user_account.fullname
  9. FROM user_account
  10. JOIN address AS address_1 ON user_account.id = address_1.user_id
  11. JOIN address AS address_2 ON user_account.id = address_2.user_id
  12. WHERE address_1.email_address = :email_address_1
  13. AND address_2.email_address = :email_address_2

To make use of a relationship() to construct a join from an aliased entity, the attribute is available from the aliased() construct directly:

  1. >>> user_alias_1 = aliased(User)
  2. >>> print(select(user_alias_1.name).join(user_alias_1.addresses))
  3. SELECT user_account_1.name
  4. FROM user_account AS user_account_1
  5. JOIN address ON user_account_1.id = address.user_id

Joining to Subqueries

The target of a join may be any “selectable” entity which includes subuqeries. When using the ORM, it is typical that these targets are stated in terms of an aliased() construct, but this is not strictly required, particularly if the joined entity is not being returned in the results. For example, to join from the User entity to the Address entity, where the Address entity is represented as a row limited subquery, we first construct a Subquery object using Select.subquery(), which may then be used as the target of the Select.join() method:

  1. >>> subq = select(Address).where(Address.email_address == "pat999@aol.com").subquery()
  2. >>> stmt = select(User).join(subq, User.id == subq.c.user_id)
  3. >>> print(stmt)
  4. SELECT user_account.id, user_account.name, user_account.fullname
  5. FROM user_account
  6. JOIN (SELECT address.id AS id,
  7. address.user_id AS user_id, address.email_address AS email_address
  8. FROM address
  9. WHERE address.email_address = :email_address_1) AS anon_1
  10. ON user_account.id = anon_1.user_id

The above SELECT statement when invoked via Session.execute() will return rows that contain User entities, but not Address entities. In order to include Address entities to the set of entities that would be returned in result sets, we construct an aliased() object against the Address entity and Subquery object. We also may wish to apply a name to the aliased() construct, such as "address" used below, so that we can refer to it by name in the result row:

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

Joining to Subqueries along Relationship paths

The subquery form illustrated in the previous section may be expressed with more specificity using a relationship()-bound attribute using one of the forms indicated at Using Relationship to join between aliased targets. For example, to create the same join while ensuring the join is along that of a particular relationship(), we may use the PropComparator.of_type() method, passing the aliased() construct containing the Subquery object that’s the target of the join:

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

Subqueries that Refer to Multiple Entities

A subquery that contains columns spanning more than one ORM entity may be applied to more than one aliased() construct at once, and used in the same Select construct in terms of each entity separately. The rendered SQL will continue to treat all such aliased() constructs as the same subquery, however from the ORM / Python perspective the different return values and object attributes can be referred towards by using the appropriate aliased() construct.

Given for example a subquery that refers to both User and Address:

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

We can create aliased() constructs against both User and Address that each refer to the same object:

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

A Select construct selecting from both entities will render the subquery once, but in a result-row context can return objects of both User and Address classes at the same time:

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

Setting the leftmost FROM clause in a join

In cases where the left side of the current state of Select is not in line with what we want to join from, the Select.join_from() method may be used:

  1. >>> stmt = select(Address).join_from(User, User.addresses).where(User.name == "sandy")
  2. >>> print(stmt)
  3. SELECT address.id, address.user_id, address.email_address
  4. FROM user_account JOIN address ON user_account.id = address.user_id
  5. WHERE user_account.name = :name_1

The Select.join_from() method accepts two or three arguments, either in the form (<join from>, <onclause>), or (<join from>, <join to>, [<onclause>]):

  1. >>> stmt = select(Address).join_from(User, Address).where(User.name == "sandy")
  2. >>> print(stmt)
  3. SELECT address.id, address.user_id, address.email_address
  4. FROM user_account JOIN address ON user_account.id = address.user_id
  5. WHERE user_account.name = :name_1

To set up the initial FROM clause for a SELECT such that Select.join() can be used subsequent, the Select.select_from() method may also be used:

  1. >>> stmt = select(Address).select_from(User).join(Address).where(User.name == "sandy")
  2. >>> print(stmt)
  3. SELECT address.id, address.user_id, address.email_address
  4. FROM user_account JOIN address ON user_account.id = address.user_id
  5. WHERE user_account.name = :name_1

Tip

The Select.select_from() method does not actually have the final say on the order of tables in the FROM clause. If the statement also refers to a Join construct that refers to existing tables in a different order, the Join construct takes precedence. When we use methods like Select.join() and Select.join_from(), these methods are ultimately creating such a Join object. Therefore we can see the contents of Select.select_from() being overridden in a case like this:

  1. >>> stmt = select(Address).select_from(User).join(Address.user).where(User.name == "sandy")
  2. >>> print(stmt)
  3. SELECT address.id, address.user_id, address.email_address
  4. FROM address JOIN user_account ON user_account.id = address.user_id
  5. WHERE user_account.name = :name_1

Where above, we see that the FROM clause is address JOIN user_account, even though we stated select_from(User) first. Because of the .join(Address.user) method call, the statement is ultimately equivalent to the following:

  1. >>> from sqlalchemy.sql import join
  2. >>>
  3. >>> user_table = User.__table__
  4. >>> address_table = Address.__table__
  5. >>>
  6. >>> j = address_table.join(user_table, user_table.c.id == address_table.c.user_id)
  7. >>> stmt = (
  8. ...     select(address_table)
  9. ...     .select_from(user_table)
  10. ...     .select_from(j)
  11. ...     .where(user_table.c.name == "sandy")
  12. ... )
  13. >>> print(stmt)
  14. SELECT address.id, address.user_id, address.email_address
  15. FROM address JOIN user_account ON user_account.id = address.user_id
  16. WHERE user_account.name = :name_1

The Join construct above is added as another entry in the Select.select_from() list which supersedes the previous entry.

Relationship WHERE Operators

Besides the use of relationship() constructs within the Select.join() and Select.join_from() methods, relationship() also plays a role in helping to construct SQL expressions that are typically for use in the WHERE clause, using the Select.where() method.

EXISTS forms: has() / any()

The Exists construct was first introduced in the SQLAlchemy Unified Tutorial in the section EXISTS subqueries. This object is used to render the SQL EXISTS keyword in conjunction with a scalar subquery. The relationship() construct provides for some helper methods that may be used to generate some common EXISTS styles of queries in terms of the relationship.

For a one-to-many relationship such as User.addresses, an EXISTS against the address table that correlates back to the user_account table can be produced using PropComparator.any(). This method accepts an optional WHERE criteria to limit the rows matched by the subquery:

  1. >>> stmt = select(User.fullname).where(
  2. ...     User.addresses.any(Address.email_address == "squirrel@squirrelpower.org")
  3. ... )
  4. >>> session.execute(stmt).all()
  5. SELECT user_account.fullname
  6. FROM user_account
  7. WHERE EXISTS (SELECT 1
  8. FROM address
  9. WHERE user_account.id = address.user_id AND address.email_address = ?)
  10. [...] ('squirrel@squirrelpower.org',)
  11. [('Sandy Cheeks',)]

As EXISTS tends to be more efficient for negative lookups, a common query is to locate entities where there are no related entities present. This is succinct using a phrase such as ~User.addresses.any(), to select for User entities that have no related Address rows:

  1. >>> stmt = select(User.fullname).where(~User.addresses.any())
  2. >>> session.execute(stmt).all()
  3. SELECT user_account.fullname
  4. FROM user_account
  5. WHERE NOT (EXISTS (SELECT 1
  6. FROM address
  7. WHERE user_account.id = address.user_id))
  8. [...] ()
  9. [('Eugene H. Krabs',)]

The PropComparator.has() method works in mostly the same way as PropComparator.any(), except that it’s used for many-to-one relationships, such as if we wanted to locate all Address objects which belonged to “sandy”:

  1. >>> stmt = select(Address.email_address).where(Address.user.has(User.name == "sandy"))
  2. >>> session.execute(stmt).all()
  3. SELECT address.email_address
  4. FROM address
  5. WHERE EXISTS (SELECT 1
  6. FROM user_account
  7. WHERE user_account.id = address.user_id AND user_account.name = ?)
  8. [...] ('sandy',)
  9. [('sandy@sqlalchemy.org',), ('squirrel@squirrelpower.org',)]

Relationship Instance Comparison Operators

The relationship()-bound attribute also offers a few SQL construction implementations that are geared towards filtering a relationship()-bound attribute in terms of a specific instance of a related object, which can unpack the appropriate attribute values from a given persistent (or less commonly a detached) object instance and construct WHERE criteria in terms of the target relationship().

  • many to one equals comparison - a specific object instance can be compared to many-to-one relationship, to select rows where the foreign key of the target entity matches the primary key value of the object given:

    1. >>> user_obj = session.get(User, 1)
    2. SELECT ...
    3. >>> print(select(Address).where(Address.user == user_obj))
    4. SELECT address.id, address.user_id, address.email_address
    5. FROM address
    6. WHERE :param_1 = address.user_id

  • many to one not equals comparison - the not equals operator may also be used:

    1. >>> print(select(Address).where(Address.user != user_obj))
    2. SELECT address.id, address.user_id, address.email_address
    3. FROM address
    4. WHERE address.user_id != :user_id_1 OR address.user_id IS NULL

  • object is contained in a one-to-many collection - this is essentially the one-to-many version of the “equals” comparison, select rows where the primary key equals the value of the foreign key in a related object:

    1. >>> address_obj = session.get(Address, 1)
    2. SELECT ...
    3. >>> print(select(User).where(User.addresses.contains(address_obj)))
    4. SELECT user_account.id, user_account.name, user_account.fullname
    5. FROM user_account
    6. WHERE user_account.id = :param_1

  • An object has a particular parent from a one-to-many perspective - the with_parent() function produces a comparison that returns rows which are referred towards by a given parent, this is essentially the same as using the == operator with the many-to-one side:

    1. >>> from sqlalchemy.orm import with_parent
    2. >>> print(select(Address).where(with_parent(user_obj, User.addresses)))
    3. SELECT address.id, address.user_id, address.email_address
    4. FROM address
    5. WHERE :param_1 = address.user_id

ORM Querying Guide

Next Query Guide Section: Writing SELECT statements for Inheritance Mappings