Relationship Loading Techniques

A big part of SQLAlchemy is providing a wide range of control over how relatedobjects get loaded when querying. By “related objects” we refer to collectionsor scalar associations configured on a mapper using relationship().This behavior can be configured at mapper construction time using therelationship.lazy parameter to the relationship()function, as well as by using options with the Query object.

The loading of relationships falls into three categories; lazy loading,eager loading, and no loading. Lazy loading refers to objects are returnedfrom a query without the relatedobjects loaded at first. When the given collection or reference isfirst accessed on a particular object, an additional SELECT statementis emitted such that the requested collection is loaded.

Eager loading refers to objects returned from a query with the relatedcollection or scalar reference already loaded up front. The Queryachieves this either by augmenting the SELECT statement it would normallyemit with a JOIN to load in related rows simultaneously, or by emittingadditional SELECT statements after the primary one to load collectionsor scalar references at once.

“No” loading refers to the disabling of loading on a given relationship, eitherthat the attribute is empty and is just never loaded, or that it raisesan error when it is accessed, in order to guard against unwanted lazy loads.

The primary forms of relationship loading are:

  • lazy loading - available via lazy='select' or the lazyload()option, this is the form of loading that emits a SELECT statement atattribute access time to lazily load a related reference on a singleobject at a time. Lazy loading is detailed at Lazy Loading.

  • joined loading - available via lazy='joined' or the joinedload()option, this form of loading applies a JOIN to the given SELECT statementso that related rows are loaded in the same result set. Joined eager loadingis detailed at Joined Eager Loading.

  • subquery loading - available via lazy='subquery' or the subqueryload()option, this form of loading emits a second SELECT statement which re-states theoriginal query embedded inside of a subquery, then JOINs that subquery to therelated table to be loaded to load all members of related collections / scalarreferences at once. Subquery eager loading is detailed at Subquery Eager Loading.

  • select IN loading - available via lazy='selectin' or the selectinload()option, this form of loading emits a second (or more) SELECT statement whichassembles the primary key identifiers of the parent objects into an IN clause,so that all members of related collections / scalar references are loaded at onceby primary key. Select IN loading is detailed at Select IN loading.

  • raise loading - available via lazy='raise', lazy='raise_on_sql',or the raiseload() option, this form of loading is triggered at thesame time a lazy load would normally occur, except it raises an ORM exceptionin order to guard against the application making unwanted lazy loads.An introduction to raise loading is at Preventing unwanted lazy loads using raiseload.

  • no loading - available via lazy='noload', or the noload()option; this loading style turns the attribute into an empty attribute thatwill never load or have any loading effect. “noload” is a fairlyuncommon loader option.

Configuring Loader Strategies at Mapping Time

The loader strategy for a particular relationship can be configuredat mapping time to take place in all cases where an object of the mappedtype is loaded, in the absence of any query-level options that modify it.This is configured using the relationship.lazy parameter torelationship(); common values for this parameterinclude select, joined, subquery and selectin.

For example, to configure a relationship to use joined eager loading whenthe parent object is queried:

  1. class Parent(Base):
  2.     __tablename__ = 'parent'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.     children = relationship("Child", lazy='joined')

Above, whenever a collection of Parent objects are loaded, eachParent will also have its children collection populated, usingrows fetched by adding a JOIN to the query for Parent objects.See Joined Eager Loading for background on this style of loading.

The default value of the relationship.lazy argument is"select", which indicates lazy loading. See Lazy Loading forfurther background.

Relationship Loading with Loader Options

The other, and possibly more common way to configure loading strategiesis to set them up on a per-query basis against specific attributes using theQuery.options() method. Very detailedcontrol over relationship loading is available using loader options;the most common arejoinedload(),subqueryload(), selectinload()and lazyload(). The option accepts eitherthe string name of an attribute against a parent, or for greater specificitycan accommodate a class-bound attribute directly:

  1. # set children to load lazily
  2. session.query(Parent).options(lazyload('children')).all()
  3.  
  4. # same, using class-bound attribute
  5. session.query(Parent).options(lazyload(Parent.children)).all()
  6.  
  7. # set children to load eagerly with a join
  8. session.query(Parent).options(joinedload('children')).all()

The loader options can also be “chained” using method chainingto specify how loading should occur further levels deep:

  1. session.query(Parent).options(
  2.     joinedload(Parent.children).
  3.     subqueryload(Child.subelements)).all()

Chained loader options can be applied against a “lazy” loaded collection.This means that when a collection or association is lazily loaded uponaccess, the specified option will then take effect:

  1. session.query(Parent).options(
  2.     lazyload(Parent.children).
  3.     subqueryload(Child.subelements)).all()

Above, the query will return Parent objects without the childrencollections loaded. When the children collection on a particularParent object is first accessed, it will lazy load the relatedobjects, but additionally apply eager loading to the subelementscollection on each member of children.

Using method chaining, the loader style of each link in the path is explicitlystated. To navigate along a path without changing the existing loader styleof a particular attribute, the defaultload() method/function may be used:

  1. session.query(A).options(
  2.     defaultload(A.atob).
  3.     joinedload(B.btoc)).all()

A similar approach can be used to specify multiple sub-options at once, usingthe Load.options() method:

  1. session.query(A).options(
  2.     defaultload(A.atob).options(
  3.       joinedload(B.btoc),
  4.       joinedload(B.btod)
  5.     )).all()

New in version 1.3.6: added Load.options()

See also

Deferred Loading across Multiple Entities - illustrates examples of combiningrelationship and column-oriented loader options.

Note

The loader options applied to an object’s lazy-loaded collectionsare “sticky” to specific object instances, meaning they will persistupon collections loaded by that specific object for as long as it exists inmemory. For example, given the previous example:

  1. session.query(Parent).options(
  2.     lazyload(Parent.children).
  3.     subqueryload(Child.subelements)).all()

if the children collection on a particular Parent object loaded bythe above query is expired (such as when a Session object’stransaction is committed or rolled back, or Session.expire_all() isused), when the Parent.children collection is next accessed in order tore-load it, the Child.subelements collection will again be loaded usingsubquery eager loading.This stays the case even if the above Parentobject is accessed from a subsequent query that specifies a different set ofoptions.To change the options on an existing object without expunging it andre-loading, they must be set explicitly in conjunction with theQuery.populate_existing() method:

  1. # change the options on Parent objects that were already loaded
  2. session.query(Parent).populate_existing().options(
  3.     lazyload(Parent.children).
  4.     lazyload(Child.subelements)).all()

If the objects loaded above are fully cleared from the Session,such as due to garbage collection or that Session.expunge_all()were used, the “sticky” options will also be gone and the newly createdobjects will make use of new options if loaded again.

A future SQLAlchemy release may add more alternatives to manipulatingthe loader options on already-loaded objects.

Lazy Loading

By default, all inter-object relationships are lazy loading. The scalar orcollection attribute associated with a relationship()contains a trigger which fires the first time the attribute is accessed. Thistrigger typically issues a SQL call at the point of accessin order to load the related object or objects:

  1. >>> jack.addresses
  2. SELECT
  3.     addresses.id AS addresses_id,
  4.     addresses.email_address AS addresses_email_address,
  5.     addresses.user_id AS addresses_user_id
  6. FROM addresses
  7. WHERE ? = addresses.user_id
  8. [5]
  9. [<Address(u'jack@google.com')>, <Address(u'j25@yahoo.com')>]

The one case where SQL is not emitted is for a simple many-to-one relationship, whenthe related object can be identified by its primary key alone and that object is alreadypresent in the current Session. For this reason, while lazy loadingcan be expensive for related collections, in the case that one is loadinglots of objects with simple many-to-ones against a relatively small set ofpossible target objects, lazy loading may be able to refer to these objects locallywithout emitting as many SELECT statements as there are parent objects.

This default behavior of “load upon attribute access” is known as “lazy” or“select” loading - the name “select” because a “SELECT” statement is typically emittedwhen the attribute is first accessed.

Lazy loading can be enabled for a given attribute that is normallyconfigured in some other way using the lazyload() loader option:

  1. from sqlalchemy.orm import lazyload
  2.  
  3. # force lazy loading for an attribute that is set to
  4. # load some other way normally
  5. session.query(User).options(lazyload(User.addresses))

Preventing unwanted lazy loads using raiseload

The lazyload() strategy produces an effect that is one of the mostcommon issues referred to in object relational mapping; theN plus one problem, which states that for any N objects loaded,accessing their lazy-loaded attributes means there will be N+1 SELECTstatements emitted. In SQLAlchemy, the usual mitigation for the N+1 problemis to make use of its very capable eager load system. However, eager loadingrequires that the attributes which are to be loaded be specified with theQuery up front. The problem of code that may access other attributesthat were not eagerly loaded, where lazy loading is not desired, may beaddressed using the raiseload() strategy; this loader strategyreplaces the behavior of lazy loading with an informative error beingraised:

  1. from sqlalchemy.orm import raiseload
  2. session.query(User).options(raiseload(User.addresses))

Above, a User object loaded from the above query will not havethe .addresses collection loaded; if some code later on attempts toaccess this attribute, an ORM exception is raised.

raiseload() may be used with a so-called “wildcard” specifier toindicate that all relationships should use this strategy. For example,to set up only one attribute as eager loading, and all the rest as raise:

  1. session.query(Order).options(
  2.     joinedload(Order.items), raiseload('*'))

The above wildcard will apply to all relationships not just on Orderbesides items, but all those on the Item objects as well. To set upraiseload() for only the Order objects, specify a fullpath with orm.Load:

  1. from sqlalchemy.orm import Load
  2.  
  3. session.query(Order).options(
  4.     joinedload(Order.items), Load(Order).raiseload('*'))

Conversely, to set up the raise for just the Item objects:

  1. session.query(Order).options(
  2.     joinedload(Order.items).raiseload('*'))

See also

Wildcard Loading Strategies

Joined Eager Loading

Joined eager loading is the most fundamental style of eager loading in theORM. It works by connecting a JOIN (by defaulta LEFT OUTER join) to the SELECT statement emitted by a Queryand populates the target scalar/collection from thesame result set as that of the parent.

At the mapping level, this looks like:

  1. class Address(Base):
  2.     # ...
  3.  
  4.     user = relationship(User, lazy="joined")

Joined eager loading is usually applied as an option to a query, rather thanas a default loading option on the mapping, in particular when used forcollections rather than many-to-one-references. This is achievedusing the joinedload() loader option:

  1. >>> jack = session.query(User).\
  2. ... options(joinedload(User.addresses)).\
  3. ... filter_by(name='jack').all()
  4. SELECT
  5.     addresses_1.id AS addresses_1_id,
  6.     addresses_1.email_address AS addresses_1_email_address,
  7.     addresses_1.user_id AS addresses_1_user_id,
  8.     users.id AS users_id, users.name AS users_name,
  9.     users.fullname AS users_fullname,
  10.     users.nickname AS users_nickname
  11. FROM users
  12. LEFT OUTER JOIN addresses AS addresses_1
  13.     ON users.id = addresses_1.user_id
  14. WHERE users.name = ?
  15. ['jack']

The JOIN emitted by default is a LEFT OUTER JOIN, to allow for a lead objectthat does not refer to a related row. For an attribute that is guaranteedto have an element, such as a many-to-onereference to a related object where the referencing foreign key is NOT NULL,the query can be made more efficient by using an inner join; this is availableat the mapping level via the relationship.innerjoin flag:

  1. class Address(Base):
  2.     # ...
  3.  
  4.     user_id = Column(ForeignKey('users.id'), nullable=False)
  5.     user = relationship(User, lazy="joined", innerjoin=True)

At the query option level, via the joinedload.innerjoin flag:

  1. session.query(Address).options(
  2.     joinedload(Address.user, innerjoin=True))

The JOIN will right-nest itself when applied in a chain that includesan OUTER JOIN:

  1. >>> session.query(User).options(
  2. ...     joinedload(User.addresses).
  3. ...     joinedload(Address.widgets, innerjoin=True)).all()
  4. SELECT
  5.     widgets_1.id AS widgets_1_id,
  6.     widgets_1.name AS widgets_1_name,
  7.     addresses_1.id AS addresses_1_id,
  8.     addresses_1.email_address AS addresses_1_email_address,
  9.     addresses_1.user_id AS addresses_1_user_id,
  10.     users.id AS users_id, users.name AS users_name,
  11.     users.fullname AS users_fullname,
  12.     users.nickname AS users_nickname
  13. FROM users
  14. LEFT OUTER JOIN (
  15.     addresses AS addresses_1 JOIN widgets AS widgets_1 ON
  16.     addresses_1.widget_id = widgets_1.id
  17. ) ON users.id = addresses_1.user_id

On older versions of SQLite, the above nested right JOIN may be re-renderedas a nested subquery. Older versions of SQLAlchemy would convert right-nestedjoins into subqueries in all cases.

Joined eager loading and result set batching

A central concept of joined eager loading when applied to collections is thatthe Query object must de-duplicate rows against the leadingentity being queried. Such as above,if the User object we loaded referred to three Address objects, theresult of the SQL statement would have had three rows; yet the Queryreturns only one User object. As additional rows are received for aUser object just loaded in a previous row, the additional columns thatrefer to new Address objects are directed into additional results withinthe User.addresses collection of that particular object.

This process is very transparent, however does imply that joined eagerloading is incompatible with “batched” query results, provided by theQuery.yield_per() method, when used for collection loading. Joinedeager loading used for scalar references is however compatible withQuery.yield_per(). The Query.yield_per() method will resultin an exception thrown if a collection based joined eager loader isin play.

To “batch” queries with arbitrarily large sets of result data while maintainingcompatibility with collection-based joined eager loading, emit multipleSELECT statements, each referring to a subset of rows using the WHEREclause, e.g. windowing. Alternatively, consider using “select IN” eager loadingwhich is potentially compatible with Query.yield_per(), providedthat the database driver in use supports multiple, simultaneous cursors(SQLite, PostgreSQL drivers, not MySQL drivers or SQL Server ODBC drivers).

The Zen of Joined Eager Loading

Since joined eager loading seems to have many resemblances to the use ofQuery.join(), it often produces confusion as to when and how it shouldbe used. It is critical to understand the distinction that whileQuery.join() is used to alter the results of a query, joinedload()goes through great lengths to not alter the results of the query, andinstead hide the effects of the rendered join to only allow for related objectsto be present.

The philosophy behind loader strategies is that any set of loading schemes canbe applied to a particular query, and the results don’t change - only thenumber of SQL statements required to fully load related objects and collectionschanges. A particular query might start out using all lazy loads. After usingit in context, it might be revealed that particular attributes or collectionsare always accessed, and that it would be more efficient to change the loaderstrategy for these. The strategy can be changed with no other modificationsto the query, the results will remain identical, but fewer SQL statements wouldbe emitted. In theory (and pretty much in practice), nothing you can do to theQuery would make it load a different set of primary or relatedobjects based on a change in loader strategy.

How joinedload() in particular achieves this result of not impactingentity rows returned in any way is that it creates an anonymous alias of thejoins it adds to your query, so that they can’t be referenced by other parts ofthe query. For example, the query below uses joinedload() to create aLEFT OUTER JOIN from users to addresses, however the ORDER BY addedagainst Address.email_address is not valid - the Address entity is notnamed in the query:

  1. >>> jack = session.query(User).\
  2. ... options(joinedload(User.addresses)).\
  3. ... filter(User.name=='jack').\
  4. ... order_by(Address.email_address).all()
  5. SELECT
  6.     addresses_1.id AS addresses_1_id,
  7.     addresses_1.email_address AS addresses_1_email_address,
  8.     addresses_1.user_id AS addresses_1_user_id,
  9.     users.id AS users_id,
  10.     users.name AS users_name,
  11.     users.fullname AS users_fullname,
  12.     users.nickname AS users_nickname
  13. FROM users
  14. LEFT OUTER JOIN addresses AS addresses_1
  15.     ON users.id = addresses_1.user_id
  16. WHERE users.name = ?
  17. ORDER BY addresses.email_address   <-- this part is wrong !
  18. ['jack']

Above, ORDER BY addresses.email_address is not valid since addresses is not in theFROM list. The correct way to load the User records and order by emailaddress is to use Query.join():

  1. >>> jack = session.query(User).\
  2. ... join(User.addresses).\
  3. ... filter(User.name=='jack').\
  4. ... order_by(Address.email_address).all()
  6. SELECT
  7.     users.id AS users_id,
  8.     users.name AS users_name,
  9.     users.fullname AS users_fullname,
  10.     users.nickname AS users_nickname
  11. FROM users
  12. JOIN addresses ON users.id = addresses.user_id
  13. WHERE users.name = ?
  14. ORDER BY addresses.email_address
  15. ['jack']

The statement above is of course not the same as the previous one, in that thecolumns from addresses are not included in the result at all. We can addjoinedload() back in, so that there are two joins - one is that which weare ordering on, the other is used anonymously to load the contents of theUser.addresses collection:

  1. >>> jack = session.query(User).\
  2. ... join(User.addresses).\
  3. ... options(joinedload(User.addresses)).\
  4. ... filter(User.name=='jack').\
  5. ... order_by(Address.email_address).all()
  6. SELECT
  7.     addresses_1.id AS addresses_1_id,
  8.     addresses_1.email_address AS addresses_1_email_address,
  9.     addresses_1.user_id AS addresses_1_user_id,
  10.     users.id AS users_id, users.name AS users_name,
  11.     users.fullname AS users_fullname,
  12.     users.nickname AS users_nickname
  13. FROM users JOIN addresses
  14.     ON users.id = addresses.user_id
  15. LEFT OUTER JOIN addresses AS addresses_1
  16.     ON users.id = addresses_1.user_id
  17. WHERE users.name = ?
  18. ORDER BY addresses.email_address
  19. ['jack']

What we see above is that our usage of Query.join() is to supply JOINclauses we’d like to use in subsequent query criterion, whereas our usage ofjoinedload() only concerns itself with the loading of theUser.addresses collection, for each User in the result. In this case,the two joins most probably appear redundant - which they are. If we wanted touse just one JOIN for collection loading as well as ordering, we use thecontains_eager() option, described in Routing Explicit Joins/Statements into Eagerly Loaded Collections below. Butto see why joinedload() does what it does, consider if we werefiltering on a particular Address:

  1. >>> jack = session.query(User).\
  2. ... join(User.addresses).\
  3. ... options(joinedload(User.addresses)).\
  4. ... filter(User.name=='jack').\
  5. ... filter(Address.email_address=='someaddress@foo.com').\
  6. ... all()
  7. SELECT
  8.     addresses_1.id AS addresses_1_id,
  9.     addresses_1.email_address AS addresses_1_email_address,
  10.     addresses_1.user_id AS addresses_1_user_id,
  11.     users.id AS users_id, users.name AS users_name,
  12.     users.fullname AS users_fullname,
  13.     users.nickname AS users_nickname
  14. FROM users JOIN addresses
  15.     ON users.id = addresses.user_id
  16. LEFT OUTER JOIN addresses AS addresses_1
  17.     ON users.id = addresses_1.user_id
  18. WHERE users.name = ? AND addresses.email_address = ?
  19. ['jack', 'someaddress@foo.com']

Above, we can see that the two JOINs have very different roles. One will matchexactly one row, that of the join of User and Address whereAddress.emailaddress=='someaddress@foo.com'. The other LEFT OUTER JOINwill match _all Address rows related to User, and is only used topopulate the User.addresses collection, for those User objects that arereturned.

By changing the usage of joinedload() to another style of loading, wecan change how the collection is loaded completely independently of SQL used toretrieve the actual User rows we want. Below we change joinedload()into subqueryload():

  1. >>> jack = session.query(User).\
  2. ... join(User.addresses).\
  3. ... options(subqueryload(User.addresses)).\
  4. ... filter(User.name=='jack').\
  5. ... filter(Address.email_address=='someaddress@foo.com').\
  6. ... all()
  7. SELECT
  8.     users.id AS users_id,
  9.     users.name AS users_name,
  10.     users.fullname AS users_fullname,
  11.     users.nickname AS users_nickname
  12. FROM users
  13. JOIN addresses ON users.id = addresses.user_id
  14. WHERE
  15.     users.name = ?
  16.     AND addresses.email_address = ?
  17. ['jack', 'someaddress@foo.com']
  19. # ... subqueryload() emits a SELECT in order
  20. # to load all address records ...

When using joined eager loading, if the query contains a modifier that impactsthe rows returned externally to the joins, such as when using DISTINCT, LIMIT,OFFSET or equivalent, the completed statement is first wrapped inside asubquery, and the joins used specifically for joined eager loading are appliedto the subquery. SQLAlchemy’s joined eager loading goes the extra mile, andthen ten miles further, to absolutely ensure that it does not affect the endresult of the query, only the way collections and related objects are loaded,no matter what the format of the query is.

See also

Routing Explicit Joins/Statements into Eagerly Loaded Collections - using contains_eager()

Subquery Eager Loading

Subqueryload eager loading is configured in the same manner as that ofjoined eager loading; for the relationship.lazy parameter,we would specify "subquery" rather than "joined", and forthe option we use the subqueryload() option rather than thejoinedload() option.

The operation of subquery eager loading is to emit a second SELECT statementfor each relationship to be loaded, across all result objects at once.This SELECT statement refers to the original SELECT statement, wrappedinside of a subquery, so that we retrieve the same list of primary keysfor the primary object being returned, then link that to the sum of allthe collection members to load them at once:

  1. >>> jack = session.query(User).\
  2. ... options(subqueryload(User.addresses)).\
  3. ... filter_by(name='jack').all()
  4. SELECT
  5.     users.id AS users_id,
  6.     users.name AS users_name,
  7.     users.fullname AS users_fullname,
  8.     users.nickname AS users_nickname
  9. FROM users
  10. WHERE users.name = ?
  11. ('jack',)
  12. SELECT
  13.     addresses.id AS addresses_id,
  14.     addresses.email_address AS addresses_email_address,
  15.     addresses.user_id AS addresses_user_id,
  16.     anon_1.users_id AS anon_1_users_id
  17. FROM (
  18.     SELECT users.id AS users_id
  19.     FROM users
  20.     WHERE users.name = ?) AS anon_1
  21. JOIN addresses ON anon_1.users_id = addresses.user_id
  22. ORDER BY anon_1.users_id, addresses.id
  23. ('jack',)

The subqueryload strategy has many advantages over joined eager loadingin the area of loading collections. First, it allows the original queryto proceed without changing it at all, not introducing in particular aLEFT OUTER JOIN that may make it less efficient. Secondly, it allowsfor many collections to be eagerly loaded without producing a single querythat has many JOINs in it, which can be even less efficient; each relationshipis loaded in a fully separate query. Finally, because the additional queryonly needs to load the collection items and not the lead object, it canuse an inner JOIN in all cases for greater query efficiency.

Disadvantages of subqueryload include that the complexity of the originalquery is transferred to the relationship queries, which when combined with theuse of a subquery, can on some backends in some cases (notably MySQL) producesignificantly slow queries. Additionally, the subqueryload strategy can onlyload the full contents of all collections at once, is therefore incompatiblewith “batched” loading supplied by Query.yield_per(), both for collectionand scalar relationships.

The newer style of loading provided by selectinload() solves theselimitations of subqueryload().

See also

Select IN loading

The Importance of Ordering

A query which makes use of subqueryload() in conjunction with alimiting modifier such as Query.first(), Query.limit(),or Query.offset() should always include Query.order_by()against unique column(s) such as the primary key, so that the additional queriesemitted by subqueryload() includethe same ordering as used by the parent query. Without it, there is a chancethat the inner query could return the wrong rows:

  1. # incorrect, no ORDER BY
  2. session.query(User).options(
  3.     subqueryload(User.addresses)).first()
  4.  
  5. # incorrect if User.name is not unique
  6. session.query(User).options(
  7.     subqueryload(User.addresses)
  8. ).order_by(User.name).first()
  9.  
  10. # correct
  11. session.query(User).options(
  12.     subqueryload(User.addresses)
  13. ).order_by(User.name, User.id).first()

See also

Why is ORDER BY required with LIMIT (especially with subqueryload())? - detailed example

Select IN loading

Select IN loading is similar in operation to subquery eager loading, howeverthe SELECT statement which is emitted has a much simpler structure thanthat of subquery eager loading. Additionally, select IN loading appliesitself to subsets of the load result at a time, so unlike joined and subqueryeager loading, is compatible with batching of results usingQuery.yield_per(), provided the database driver supports simultaneouscursors.

Overall, especially as of the 1.3 series of SQLAlchemy, selectin loadingis the most simple and efficient way to eagerly load collections of objectsin most cases. The only scenario in which selectin eager loading is not feasibleis when the model is using composite primary keys, and the backend databasedoes not support tuples with IN, which includes SQLite, Oracle andSQL Server.

New in version 1.2.

“Select IN” eager loading is provided using the "selectin" argument torelationship.lazy or by using the selectinload() loaderoption. This style of loading emits a SELECT that refers to the primary keyvalues of the parent object, or in the case of a simple many-to-onerelationship to the those of the child objects, inside of an IN clause, inorder to load related associations:

  1. >>> jack = session.query(User).\
  2. ... options(selectinload('addresses')).\
  3. ... filter(or_(User.name == 'jack', User.name == 'ed')).all()
  4. SELECT
  5.     users.id AS users_id,
  6.     users.name AS users_name,
  7.     users.fullname AS users_fullname,
  8.     users.nickname AS users_nickname
  9. FROM users
  10. WHERE users.name = ? OR users.name = ?
  11. ('jack', 'ed')
  12. SELECT
  13.     addresses.id AS addresses_id,
  14.     addresses.email_address AS addresses_email_address,
  15.     addresses.user_id AS addresses_user_id
  16. FROM addresses
  17. WHERE addresses.user_id IN (?, ?)
  18. ORDER BY addresses.user_id, addresses.id
  19. (5, 7)

Above, the second SELECT refers to addresses.user_id IN (5, 7), where the“5” and “7” are the primary key values for the previous two Userobjects loaded; after a batch of objects are completely loaded, their primarykey values are injected into the IN clause for the second SELECT.Because the relationship between User and Address provides that theprimary key values for User can be derived from Address.user_id, thestatement has no joins or subqueries at all.

Changed in version 1.3: selectin loading can omit the JOIN for a simpleone-to-many collection.

Changed in version 1.3.6: selectin loading can also omit the JOIN for a simplemany-to-one relationship.

For collections, in the case where the primary key of the parent object isn’tpresent in the related row, “selectin” loading will also JOIN to the parenttable so that the parent primary key values are present. This also takes placefor a non-collection, many-to-one load where the related column values are notloaded on the parent objects and would otherwise need to be loaded:

  1. >>> session.query(Address).\
  2. ... options(selectinload('user')).all()
  3. SELECT
  4.     addresses.id AS addresses_id,
  5.     addresses.email_address AS addresses_email_address,
  6.     addresses.user_id AS addresses_user_id
  7.     FROM addresses
  8. SELECT
  9.     addresses_1.id AS addresses_1_id,
  10.     users.id AS users_id,
  11.     users.name AS users_name,
  12.     users.fullname AS users_fullname,
  13.     users.nickname AS users_nickname
  14. FROM addresses AS addresses_1
  15. JOIN users ON users.id = addresses_1.user_id
  16. WHERE addresses_1.id IN (?, ?)
  17. ORDER BY addresses_1.id
  18. (1, 2)

“Select IN” loading is the newest form of eager loading added to SQLAlchemyas of the 1.2 series. Things to know about this kind of loading include:

  • The SELECT statement emitted by the “selectin” loader strategy, unlikethat of “subquery”, does notrequire a subquery nor does it inherit any of the performance limitationsof the original query; the lookup is a simple primary key lookup and shouldhave high performance.

  • The special ordering requirements of subqueryload described atThe Importance of Ordering also don’t apply to selectin loading; selectinis always linking directly to a parent primary key and can’t reallyreturn the wrong result.

  • “selectin” loading, unlike joined or subquery eager loading, always emitsits SELECT in terms of the immediate parent objects just loaded, and not theoriginal type of object at the top of the chain. So if eager loading manylevels deep, “selectin” loading still uses no more than one JOIN, and usuallyno JOINs, in the statement. In comparison, joined and subquery eagerloading always refer to multiple JOINs up to the original parent.

  • “selectin” loading produces a SELECT statement of a predictable structure,independent of that of the original query. As such, taking advantage ofa new feature with ColumnOperators.in_() that allows it to workwith cached queries, the selectin loader makes full use of thesqlalchemy.ext.baked extension to cache generated SQL and greatlycut down on internal function call overhead.

  • The strategy will only query for at most 500 parent primary key values at atime, as the primary keys are rendered into a large IN expression in theSQL statement. Some databases like Oracle have a hard limit on how largean IN expression can be, and overall the size of the SQL string shouldn’tbe arbitrarily large. So for large result sets, “selectin” loadingwill emit a SELECT per 500 parent rows returned. These SELECT statementsemit with minimal Python overhead due to the “baked” queries and also minimalSQL overhead as they query against primary key directly.

  • “selectin” loading is the only eager loading that can work in conjunction withthe “batching” feature provided by Query.yield_per(), providedthe database driver supports simultaneous cursors. As it onlyqueries for related items against specific result objects, “selectin” loadingallows for eagerly loaded collections against arbitrarily large result setswith a top limit on memory use when used with Query.yield_per().

Current database drivers that support simultaneous cursors includeSQLite, PostgreSQL. The MySQL drivers mysqlclient and pymysql currentlydo not support simultaneous cursors, nor do the ODBC drivers forSQL Server.

  • As “selectin” loading relies upon IN, for a mapping with composite primarykeys, it must use the “tuple” form of IN, which looks like WHERE(table.column_a, table.column_b) IN ((?, ?), (?, ?), (?, ?)). This syntaxis not supported on every database; within the dialects that are includedwith SQLAlchemy, it is known to be supported by modern PostgreSQL, MySQL andSQLite versions. Therefore selectin loading is not platform-agnostic forcomposite primary keys. There is no special logic in SQLAlchemy to checkahead of time which platforms support this syntax or not; if run against anon-supporting platform, the database will return an error immediately. Anadvantage to SQLAlchemy just running the SQL out for it to fail is that if aparticular database does start supporting this syntax, it will work withoutany changes to SQLAlchemy.

In general, “selectin” loading is probably superior to “subquery” eager loadingin most ways, save for the syntax requirement with composite primary keysand possibly that it may emit many SELECT statements for larger result sets.As always, developers should spend time looking at thestatements and results generated by their applications in development tocheck that things are working efficiently.

What Kind of Loading to Use ?

Which type of loading to use typically comes down to optimizing the tradeoffbetween number of SQL executions, complexity of SQL emitted, and amount ofdata fetched. Lets take two examples, a relationship()which references a collection, and a relationship() thatreferences a scalar many-to-one reference.

  • One to Many Collection

  • When using the default lazy loading, if you load 100 objects, and then access a collection on each ofthem, a total of 101 SQL statements will be emitted, although each statement will typically be asimple SELECT without any joins.

  • When using joined loading, the load of 100 objects and their collections will emit only one SQLstatement. However, thetotal number of rows fetched will be equal to the sum of the size of all the collections, plus oneextra row for each parent object that has an empty collection. Each row will also contain the fullset of columns represented by the parents, repeated for each collection item - SQLAlchemy does notre-fetch these columns other than those of the primary key, however most DBAPIs (with someexceptions) will transmit the full data of each parent over the wire to the client connection inany case. Therefore joined eager loading only makes sense when the size of the collections arerelatively small. The LEFT OUTER JOIN can also be performance intensive compared to an INNER join.

  • When using subquery loading, the load of 100 objects willemit two SQL statements. The second statement will fetch a total number ofrows equal to the sum of the size of all collections. An INNER JOIN isused, and a minimum of parent columns are requested, only the primary keys.So a subquery load makes sense when the collections are larger.

  • When multiple levels of depth are used with joined or subquery loading, loading collections-within-collections will multiply the total number of rows fetched in a cartesian fashion. Bothjoined and subquery eager loading always join from the original parent class; if loading a collectionfour levels deep, there will be four JOINs out to the parent. selectin loadingon the other hand will always have exactly one JOIN to the immediateparent table.

  • Using selectin loading, the load of 100 objects will also emit two SQLstatements, the second of which refers to the 100 primary keys of theobjects loaded. selectin loading will however render at most 500 primarykey values into a single SELECT statement; so for a lead collection largerthan 500, there will be a SELECT statement emitted for each batch of500 objects selected.

  • Using multiple levels of depth with selectin loading does not incur the“cartesian” issue that joined and subquery eager loading have; the queriesfor selectin loading have the best performance characteristics and thefewest number of rows. The only caveat is that there might be more thanone SELECT emitted depending on the size of the lead result.

  • selectin loading, unlike joined (when using collections) and subquery eagerloading (all kinds of relationships), is potentially compatible with resultset batching provided by Query.yield_per() assuming an appropriatedatabase driver, so may be able to allow batching for large result sets.

  • Many to One Reference

  • When using the default lazy loading, a load of 100 objects will like in the case of the collectionemit as many as 101 SQL statements. However - there is a significant exception to this, in thatif the many-to-one reference is a simple foreign key reference to the target’s primary key, eachreference will be checked first in the current identity map using Query.get(). So here,if the collection of objects references a relatively small set of target objects, or the full setof possible target objects have already been loaded into the session and are strongly referenced,using the default of lazy=’select’ is by far the most efficient way to go.

  • When using joined loading, the load of 100 objects will emit only one SQL statement. The joinwill be a LEFT OUTER JOIN, and the total number of rows will be equal to 100 in all cases.If you know that each parent definitely has a child (i.e. the foreignkey reference is NOT NULL), the joined load can be configured withinnerjoin set to True, which isusually specified within the relationship(). For a load of objects wherethere are many possible target references which may have not been loaded already, joined loadingwith an INNER JOIN is extremely efficient.

  • Subquery loading will issue a second load for all the child objects, so for a load of 100 objectsthere would be two SQL statements emitted. There’s probably not much advantage here overjoined loading, however, except perhaps that subquery loading can use an INNER JOIN in all caseswhereas joined loading requires that the foreign key is NOT NULL.

  • Selectin loading will also issue a second load for all the child objects (and asstated before, for larger results it will emit a SELECT per 500 rows), so for a load of 100 objectsthere would be two SQL statements emitted. The query itself still has toJOIN to the parent table, so again there’s not too much advantage toselectin loading for many-to-one vs. joined eager loading save for theuse of INNER JOIN in all cases.

Polymorphic Eager Loading

Specification of polymorphic options on a per-eager-load basis is supported.See the section Eager Loading of Specific or Polymorphic Subtypes for examplesof the PropComparator.of_type() method in conjunction with theorm.with_polymorphic() function.

Wildcard Loading Strategies

Each of joinedload(), subqueryload(), lazyload(),selectinload(),noload(), and raiseload() can be used to set the defaultstyle of relationship() loadingfor a particular query, affecting all relationship() -mappedattributes not otherwisespecified in the Query. This feature is available by passingthe string '*' as the argument to any of these options:

  1. session.query(MyClass).options(lazyload('*'))

Above, the lazyload('') option will supersede the lazy settingof all relationship() constructs in use for that query,except for those which use the 'dynamic' style of loading.If some relationships specifylazy='joined' or lazy='subquery', for example,using lazyload('') will unilaterallycause all those relationships to use 'select' loading, e.g. emit aSELECT statement when each attribute is accessed.

The option does not supersede loader options stated in thequery, such as eagerload(),subqueryload(), etc. The query below will still use joined loadingfor the widget relationship:

  1. session.query(MyClass).options(
  2.     lazyload('*'),
  3.     joinedload(MyClass.widget)
  4. )

If multiple '*' options are passed, the last one overridesthose previously passed.

Per-Entity Wildcard Loading Strategies

A variant of the wildcard loader strategy is the ability to set the strategyon a per-entity basis. For example, if querying for User and Address,we can instruct all relationships on Address only to use lazy loadingby first applying the Load object, then specifying the * as achained option:

  1. session.query(User, Address).options(
  2.     Load(Address).lazyload('*'))

Above, all relationships on Address will be set to a lazy load.

Routing Explicit Joins/Statements into Eagerly Loaded Collections

The behavior of joinedload() is such that joins arecreated automatically, using anonymous aliases as targets, the results of whichare routed into collections andscalar references on loaded objects. It is often the case that a query alreadyincludes the necessary joins which represent a particular collection or scalarreference, and the joins added by the joinedload feature are redundant - yetyou’d still like the collections/references to be populated.

For this SQLAlchemy supplies the contains_eager()option. This option is used in the same manner as thejoinedload() option except it is assumed that theQuery will specify the appropriate joinsexplicitly. Below, we specify a join between User and Addressand additionally establish this as the basis for eager loading of User.addresses:

  1. class User(Base):
  2.     __tablename__ = 'user'
  3.     id = Column(Integer, primary_key=True)
  4.     addresses = relationship("Address")
  5.  
  6. class Address(Base):
  7.     __tablename__ = 'address'
  8.  
  9.     # ...
  10.  
  11. q = session.query(User).join(User.addresses).\
  12.             options(contains_eager(User.addresses))

If the “eager” portion of the statement is “aliased”, the alias keywordargument to contains_eager() may be used to indicate it.This is sent as a reference to an aliased() or Aliasconstruct:

  1. # use an alias of the Address entity
  2. adalias = aliased(Address)
  3.  
  4. # construct a Query object which expects the "addresses" results
  5. query = session.query(User).\
  6.     outerjoin(adalias, User.addresses).\
  7.     options(contains_eager(User.addresses, alias=adalias))
  8.  
  9. # get results normally
  10. r = query.all()
  11. SELECT
  12.     users.user_id AS users_user_id,
  13.     users.user_name AS users_user_name,
  14.     adalias.address_id AS adalias_address_id,
  15.     adalias.user_id AS adalias_user_id,
  16.     adalias.email_address AS adalias_email_address,
  17.     (...other columns...)
  18. FROM users
  19. LEFT OUTER JOIN email_addresses AS email_addresses_1
  20. ON users.user_id = email_addresses_1.user_id

The path given as the argument to contains_eager() needsto be a full path from the starting entity. For example if we were loadingUsers->orders->Order->items->Item, the string version would look like:

  1. query(User).options(
  2.     contains_eager('orders').
  3.     contains_eager('items'))

Or using the class-bound descriptor:

  1. query(User).options(
  2.     contains_eager(User.orders).
  3.     contains_eager(Order.items))

Using contains_eager() to load a custom-filtered collection result

When we use contains_eager(), we are constructing ourselves theSQL that will be used to populate collections. From this, it naturally followsthat we can opt to modify what values the collection is intended to store,by writing our SQL to load a subset of elements for collections orscalar attributes.

As an example, we can load a User object and eagerly load only particularaddresses into its .addresses collection just by filtering:

  1. q = session.query(User).join(User.addresses).\
  2.             filter(Address.email.like('%ed%')).\
  3.             options(contains_eager(User.addresses))

The above query will load only User objects which contain atleast Address object that contains the substring 'ed' in itsemail field; the User.addresses collection will contain onlythese Address entries, and not any other Address entries that arein fact associated with the collection.

Warning

Keep in mind that when we load only a subset of objects into a collection,that collection no longer represents what’s actually in the database. Ifwe attempted to add entries to this collection, we might find ourselvesconflicting with entries that are already in the database but not locallyloaded.

In addition, the collection will fully reload normally once theobject or attribute is expired. This expiration occurs whenever theSession.commit(), Session.rollback() methods are usedassuming default session settings, or the Session.expire_all()or Session.expire() methods are used.

For these reasons, prefer returning separate fields in a tuple ratherthan artificially altering a collection, when an object plus a customset of related objects is desired:

  1. q = session.query(User, Address).join(User.addresses).\
  2.             filter(Address.email.like('%ed%'))

Advanced Usage with Arbitrary Statements

The alias argument can be more creatively used, in that it can be madeto represent any set of arbitrary names to match up into a statement.Below it is linked to a select() which links a set of column objectsto a string SQL statement:

  1. # label the columns of the addresses table
  2. eager_columns = select([
  3.     addresses.c.address_id.label('a1'),
  4.     addresses.c.email_address.label('a2'),
  5.     addresses.c.user_id.label('a3')
  6. ])
  7.  
  8. # select from a raw SQL statement which uses those label names for the
  9. # addresses table.  contains_eager() matches them up.
  10. query = session.query(User).\
  11.     from_statement("select users.*, addresses.address_id as a1, "
  12.             "addresses.email_address as a2, "
  13.             "addresses.user_id as a3 "
  14.             "from users left outer join "
  15.             "addresses on users.user_id=addresses.user_id").\
  16.     options(contains_eager(User.addresses, alias=eager_columns))

Creating Custom Load Rules

Warning

This is an advanced technique! Great care and testingshould be applied.

The ORM has various edge cases where the value of an attribute is locallyavailable, however the ORM itself doesn’t have awareness of this. Thereare also cases when a user-defined system of loading attributes is desirable.To support the use case of user-defined loading systems, a key functionattributes.set_committed_value() is provided. This function isbasically equivalent to Python’s own setattr() function, except thatwhen applied to a target object, SQLAlchemy’s “attribute history” systemwhich is used to determine flush-time changes is bypassed; the attributeis assigned in the same way as if the ORM loaded it that way from the database.

The use of attributes.set_committed_value() can be combined with anotherkey event known as InstanceEvents.load() to produce attribute-populationbehaviors when an object is loaded. One such example is the bi-directional“one-to-one” case, where loading the “many-to-one” side of a one-to-oneshould also imply the value of the “one-to-many” side. The SQLAlchemy ORMdoes not consider backrefs when loading related objects, and it views a“one-to-one” as just another “one-to-many”, that just happens to be onerow.

Given the following mapping:

  1. from sqlalchemy import Integer, ForeignKey, Column
  2. from sqlalchemy.orm import relationship, backref
  3. from sqlalchemy.ext.declarative import declarative_base
  4.  
  5. Base = declarative_base()
  6.  
  7.  
  8. class A(Base):
  9.     __tablename__ = 'a'
  10.     id = Column(Integer, primary_key=True)
  11.     b_id = Column(ForeignKey('b.id'))
  12.     b = relationship(
  13.         "B",
  14.         backref=backref("a", uselist=False),
  15.         lazy='joined')
  16.  
  17.  
  18. class B(Base):
  19.     __tablename__ = 'b'
  20.     id = Column(Integer, primary_key=True)

If we query for an A row, and then ask it for a.b.a, we will getan extra SELECT:

  1. >>> a1.b.a
  2. SELECT a.id AS a_id, a.b_id AS a_b_id
  3. FROM a
  4. WHERE ? = a.b_id

This SELECT is redundant because b.a is the same value as a1. Wecan create an on-load rule to populate this for us:

  1. from sqlalchemy import event
  2. from sqlalchemy.orm import attributes
  3.  
  4. @event.listens_for(A, "load")
  5. def load_b(target, context):
  6.     if 'b' in target.__dict__:
  7.         attributes.set_committed_value(target.b, 'a', target)

Now when we query for A, we will get A.b from the joined eager load,and A.b.a from our event:

  1. a1 = s.query(A).first()
  2. SELECT
  3.     a.id AS a_id,
  4.     a.b_id AS a_b_id,
  5.     b_1.id AS b_1_id
  6. FROM a
  7. LEFT OUTER JOIN b AS b_1 ON b_1.id = a.b_id
  8.  LIMIT ? OFFSET ?
  9. (1, 0)
  10. assert a1.b.a is a1

Relationship Loader API

  • sqlalchemy.orm.containsalias(_alias)
  • Return a MapperOption that will indicate to the Querythat the main table has been aliased.

This is a seldom-used option to suit thevery rare case that contains_eager()is being used in conjunction with a user-defined SELECTstatement that aliases the parent table. E.g.:

  1. # define an aliased UNION called 'ulist'
  2. ulist = users.select(users.c.user_id==7).\
  3.                 union(users.select(users.c.user_id>7)).\
  4.                 alias('ulist')
  5.  
  6. # add on an eager load of "addresses"
  7. statement = ulist.outerjoin(addresses).\
  8.                 select().apply_labels()
  9.  
  10. # create query, indicating "ulist" will be an
  11. # alias for the main table, "addresses"
  12. # property should be eager loaded
  13. query = session.query(User).options(
  14.                         contains_alias(ulist),
  15.                         contains_eager(User.addresses))
  16.  
  17. # then get results via the statement
  18. results = query.from_statement(statement).all()
  • Parameters
  • alias – is the string name of an alias, or aAlias object representingthe alias.
  • sqlalchemy.orm.containseager(keys, *kw_)
  • Indicate that the given attribute should be eagerly loaded fromcolumns stated manually in the query.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

The option is used in conjunction with an explicit join that loadsthe desired rows, i.e.:

  1. sess.query(Order).\
  2.         join(Order.user).\
  3.         options(contains_eager(Order.user))

The above query would join from the Order entity to its relatedUser entity, and the returned Order objects would have theOrder.user attribute pre-populated.

contains_eager() also accepts an alias argument, which is thestring name of an alias, an alias()construct, or an aliased() construct. Use this whenthe eagerly-loaded rows are to come from an aliased table:

  1. user_alias = aliased(User)
  2. sess.query(Order).\
  3.         join((user_alias, Order.user)).\
  4.         options(contains_eager(Order.user, alias=user_alias))

When using contains_eager() in conjunction with inheritedsubclasses, the RelationshipProperty.of_type() modifier shouldalso be used in order to set up the pathing properly:

  1. sess.query(Company).\
  2.     outerjoin(Company.employees.of_type(Manager)).\
  3.     options(
  4.         contains_eager(
  5.             Company.employees.of_type(Manager),
  6.             alias=Manager)
  7.     )

See also

Relationship Loading Techniques

Routing Explicit Joins/Statements into Eagerly Loaded Collections

  • sqlalchemy.orm.defaultload(*keys)
  • Indicate an attribute should load using its default loader style.

This method is used to link to other loader options further intoa chain of attributes without altering the loader style of the linksalong the chain. For example, to set joined eager loading for anelement of an element:

  1. session.query(MyClass).options(
  2.     defaultload(MyClass.someattribute).
  3.     joinedload(MyOtherClass.someotherattribute)
  4. )

defaultload() is also useful for setting column-level optionson a related class, namely that of defer() and undefer():

  1. session.query(MyClass).options(
  2.     defaultload(MyClass.someattribute).
  3.     defer("some_column").
  4.     undefer("some_other_column")
  5. )

See also

Load.options() - allows for complex hierarchicalloader option structures with less verbosity than with individualdefaultload() directives.

Relationship Loading with Loader Options

Deferred Loading across Multiple Entities

  • sqlalchemy.orm.eagerload(*args, **kwargs)

  • A synonym for joinedload().

  • sqlalchemy.orm.eagerloadall(args, *kwargs_)

  • A synonym for joinedload_all()

  • sqlalchemy.orm.immediateload(*keys)

  • Indicate that the given attribute should be loaded usingan immediate load with a per-attribute SELECT statement.

The immediateload() option is superseded in generalby the selectinload() option, which performs the same taskmore efficiently by emitting a SELECT for all loaded objects.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

See also

Relationship Loading Techniques

Select IN loading

  • sqlalchemy.orm.joinedload(*keys, **kw)
  • Indicate that the given attribute should be loaded using joinedeager loading.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

examples:

  1. # joined-load the "orders" collection on "User"
  2. query(User).options(joinedload(User.orders))
  3.  
  4. # joined-load Order.items and then Item.keywords
  5. query(Order).options(
  6.     joinedload(Order.items).joinedload(Item.keywords))
  7.  
  8. # lazily load Order.items, but when Items are loaded,
  9. # joined-load the keywords collection
  10. query(Order).options(
  11.     lazyload(Order.items).joinedload(Item.keywords))
  • Parameters
  • innerjoin –

if True, indicates that the joined eager load shoulduse an inner join instead of the default of left outer join:

  1. query(Order).options(joinedload(Order.user, innerjoin=True))

In order to chain multiple eager joins together where some may beOUTER and others INNER, right-nested joins are used to link them:

  1. query(A).options(
  2.     joinedload(A.bs, innerjoin=False).
  3.         joinedload(B.cs, innerjoin=True)
  4. )

The above query, linking A.bs via “outer” join and B.cs via “inner” joinwould render the joins as “a LEFT OUTER JOIN (b JOIN c)”. When usingolder versions of SQLite (< 3.7.16), this form of JOIN is translated touse full subqueries as this syntax is otherwise not directly supported.

The innerjoin flag can also be stated with the term "unnested".This indicates that an INNER JOIN should be used, unless the joinis linked to a LEFT OUTER JOIN to the left, in which case itwill render as LEFT OUTER JOIN. For example, supposing A.bsis an outerjoin:

  1. query(A).options(
  2.     joinedload(A.bs).
  3.         joinedload(B.cs, innerjoin="unnested")
  4. )

The above join will render as “a LEFT OUTER JOIN b LEFT OUTER JOIN c”,rather than as “a LEFT OUTER JOIN (b JOIN c)”.

Note

The “unnested” flag does not affect the JOIN renderedfrom a many-to-many association table, e.g. a table configuredas relationship.secondary, to the target table; forcorrectness of results, these joins are always INNER and aretherefore right-nested if linked to an OUTER join.

Changed in version 1.0.0: innerjoin=True now impliesinnerjoin="nested", whereas in 0.9 it impliedinnerjoin="unnested". In order to achieve the pre-1.0 “unnested”inner join behavior, use the value innerjoin="unnested".See Right inner join nesting now the default for joinedload with innerjoin=True.

Note

The joins produced by orm.joinedload() are anonymouslyaliased. The criteria by which the join proceeds cannot bemodified, nor can the Query refer to these joins in any way,including ordering. See The Zen of Joined Eager Loading for furtherdetail.

To produce a specific SQL JOIN which is explicitly available, useQuery.join(). To combine explicit JOINs with eager loadingof collections, use orm.contains_eager(); seeRouting Explicit Joins/Statements into Eagerly Loaded Collections.

See also

Relationship Loading Techniques

Joined Eager Loading

  • sqlalchemy.orm.joinedloadall(keys, *kw_)
  • Produce a standalone “all” option for orm.joinedload().

Deprecated since version 0.9: The joinedload_all() function is deprecated, and will be removedin a future release. Please use method chaining with joinedload()instead, as in:

  1. session.query(MyClass).options(
  2.     joinedload("someattribute").joinedload("anotherattribute")
  3. )
  • sqlalchemy.orm.lazyload(*keys)
  • Indicate that the given attribute should be loaded using “lazy”loading.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

See also

Relationship Loading Techniques

Lazy Loading

  • class sqlalchemy.orm.Load(entity)
  • Bases: sqlalchemy.sql.expression.Generative, sqlalchemy.orm.interfaces.MapperOption

Represents loader options which modify the state of aQuery in order to affect how various mapped attributes areloaded.

The Load object is in most cases used implicitly behind thescenes when one makes use of a query option like joinedload(),defer(), or similar. However, the Load objectcan also be used directly, and in some cases can be useful.

To use Load directly, instantiate it with the target mappedclass as the argument. This style of usage isuseful when dealing with a Query that has multiple entities:

  1. myopt = Load(MyClass).joinedload("widgets")

The above myopt can now be used with Query.options(), where itwill only take effect for the MyClass entity:

  1. session.query(MyClass, MyOtherClass).options(myopt)

One case where Load is useful as public API is when specifying“wildcard” options that only take effect for a certain class:

  1. session.query(Order).options(Load(Order).lazyload('*'))

Above, all relationships on Order will be lazy-loaded, but otherattributes on those descendant objects will load using their normalloader strategy.

See also

Deferred Column Loader Query Options

Deferred Loading across Multiple Entities

Relationship Loading with Loader Options

  • sqlalchemy.orm.noload(*keys)
  • Indicate that the given relationship attribute should remain unloaded.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

orm.noload() applies to relationship() attributes; forcolumn-based attributes, see orm.defer().

See also

Relationship Loading Techniques

  • sqlalchemy.orm.raiseload(*keys, **kw)
  • Indicate that the given relationship attribute should disallow lazy loads.

A relationship attribute configured with orm.raiseload() willraise an InvalidRequestError upon access. Thetypical way this is useful is when an application is attempting to ensurethat all relationship attributes that are accessed in a particular contextwould have been already loaded via eager loading. Instead of havingto read through SQL logs to ensure lazy loads aren’t occurring, thisstrategy will cause them to raise immediately.

  • Parameters
  • sql_only – if True, raise only if the lazy load would emit SQL,but not if it is only checking the identity map, or determining thatthe related value should just be None due to missing keys. When False,the strategy will raise for all varieties of lazyload.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

orm.raiseload() applies to relationship() attributes only.

New in version 1.1.

See also

Relationship Loading Techniques

Preventing unwanted lazy loads using raiseload

  • sqlalchemy.orm.selectinload(*keys)
  • Indicate that the given attribute should be loaded usingSELECT IN eager loading.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

examples:

  1. # selectin-load the "orders" collection on "User"
  2. query(User).options(selectinload(User.orders))
  3.  
  4. # selectin-load Order.items and then Item.keywords
  5. query(Order).options(
  6.     selectinload(Order.items).selectinload(Item.keywords))
  7.  
  8. # lazily load Order.items, but when Items are loaded,
  9. # selectin-load the keywords collection
  10. query(Order).options(
  11.     lazyload(Order.items).selectinload(Item.keywords))

New in version 1.2.

See also

Relationship Loading Techniques

Select IN loading

  • sqlalchemy.orm.selectinloadall(*keys_)
  • Produce a standalone “all” option for orm.selectinload().

Deprecated since version 0.9: The selectinload_all() function is deprecated, and will be removedin a future release. Please use method chaining with selectinload()instead, as in:

  1. session.query(MyClass).options(
  2.     selectinload("someattribute").selectinload("anotherattribute")
  3. )
  • sqlalchemy.orm.subqueryload(*keys)
  • Indicate that the given attribute should be loaded usingsubquery eager loading.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

examples:

  1. # subquery-load the "orders" collection on "User"
  2. query(User).options(subqueryload(User.orders))
  3.  
  4. # subquery-load Order.items and then Item.keywords
  5. query(Order).options(
  6.     subqueryload(Order.items).subqueryload(Item.keywords))
  7.  
  8. # lazily load Order.items, but when Items are loaded,
  9. # subquery-load the keywords collection
  10. query(Order).options(
  11.     lazyload(Order.items).subqueryload(Item.keywords))

See also

Relationship Loading Techniques

Subquery Eager Loading

  • sqlalchemy.orm.subqueryloadall(*keys_)
  • Produce a standalone “all” option for orm.subqueryload().

Deprecated since version 0.9: The subqueryload_all() function is deprecated, and will be removedin a future release. Please use method chaining with subqueryload()instead, as in:

  1. session.query(MyClass).options(
  2.     subqueryload("someattribute").subqueryload("anotherattribute")
  3. )