This page is part of the .

    Previous: ORM Querying Guide | Next:

    Writing SELECT statements for ORM Mapped Classes

    About this Document

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

    .

    SELECT statements are produced by the select() function which returns a 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:

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

    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

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

    A object that contains ORM-annotated entities is normally executed using a Session object, and not a 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.

    Below we select from the User entity, producing a Select that selects from the mapped 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 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 method to execute, rather than the Session.execute() method, so that a 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 to receive a Result object, then calling upon 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 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 .

    An existing Select construct may also have ORM classes and/or column expressions added to its columns clause using the 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 . 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 , 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 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. may also be subclassed in order to return alternate data structures; see Bundle.create_row_processor() for an example.

    See also

    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 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 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}")

    See also

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

    Getting ORM Results from Textual Statements

    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 object is converted into a TextualSelect object, which takes on a role that is comparable to the construct. The TextClause.columns() method is typically passed 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 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 method, and linked to the User entity to it using the aliased() construct, in a similar manner as discussed below in :

    The difference between using the TextualSelect directly with 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.

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

    - in the SQLAlchemy Unified Tutorial

    Selecting Entities from UNIONs and other set operations

    The and union_all() functions are the most common set operations, which along with other set operations such as , intersect() and others deliver an object known as a , which is composed of multiple Select constructs joined by a set-operation keyword. ORM entities may be selected from simple compound selects using the 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 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 , as illustrated previously at Selecting Entities from Subqueries. In the example below, we first use 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 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

    The 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 in the SQLAlchemy Unified Tutorial.

    The usage of in an ORM context for 2.0 style queries is mostly equivalent, minus legacy use cases, to the usage of the 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 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 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 construct afterwards using the Select.add_columns() method. See the section for examples of both of these forms.

    Chaining Multiple Joins

    To construct a chain of joins, multiple 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 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. 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, 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 setup between the two mapped Table constructs, or if there are multiple linkages between them such that the appropriate constraint to use is ambiguous.

    Note

    When making use of Select.join() or without indicating an ON clause, ORM configured relationship() constructs are not taken into account. Only the configured 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 :

    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.

    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 and selectinload(). See the section .

    Using Relationship to join between aliased targets

    When constructing joins using -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 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 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 -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

    To make use of a 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 object using Select.subquery(), which may then be used as the target of the method:

    1. >>> subq = select(Address).where(Address.email_address == "pat999@aol.com").subquery()
    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 object against the Address entity and Subquery object. We also may wish to apply a name to the construct, such as "address" used below, so that we can refer to it by name in the result row:

    Joining to Subqueries along Relationship paths

    The subquery form illustrated in the previous section may be expressed with more specificity using a -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 , we may use the PropComparator.of_type() method, passing the 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 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 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 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 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 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 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 construct that refers to existing tables in a different order, the Join construct takes precedence. When we use methods like and Select.join_from(), these methods are ultimately creating such a 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 construct above is added as another entry in the Select.select_from() list which supersedes the previous entry.

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

    The Exists construct was first introduced in the in the section EXISTS subqueries. This object is used to render the SQL EXISTS keyword in conjunction with a scalar subquery. The 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 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 -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 ) 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

    • An object has a particular parent from a one-to-many perspective - the 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:

    ORM Querying Guide

    Next Query Guide Section: Writing SELECT statements for Inheritance Mappings