From 9520c878b6f350081fd8c95c6ed3fdf1d2d7d244 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 10 Mar 2010 18:29:32 -0500 Subject: [PATCH] - emphasized query.join() in ORM tutorial as per [ticket:1708] - cleaned up tutorial w.r.t. eagerload, added a section for contains_eager as this function is equally important - added better linkages in sqlalchemy.orm reference documentation, updated antiquated docs for contains_eager(), got aliased()/AliasedClass documented as well as Sphinx will allow us --- doc/build/mappers.rst | 1 - doc/build/ormtutorial.rst | 75 +++++++++++++++++++++---------- doc/build/reference/orm/query.rst | 2 + lib/sqlalchemy/orm/__init__.py | 66 +++++++++++++++++++-------- lib/sqlalchemy/orm/util.py | 20 +++++---- 5 files changed, 114 insertions(+), 50 deletions(-) diff --git a/doc/build/mappers.rst b/doc/build/mappers.rst index 420ed38b17..f20042a00a 100644 --- a/doc/build/mappers.rst +++ b/doc/build/mappers.rst @@ -1197,7 +1197,6 @@ To add criterion to multiple points along a longer join, use ``from_joinpoint=Tr Configuring Eager Loading ~~~~~~~~~~~~~~~~~~~~~~~~~~ - Eager loading of relations occurs using joins or outerjoins from parent to child table during a normal query operation, such that the parent and its child collection can be populated from a single SQL statement. SQLAlchemy's eager loading uses aliased tables in all cases when joining to related items, so it is compatible with self-referential joining. However, to use eager loading with a self-referential relation, SQLAlchemy needs to be told how many levels deep it should join; otherwise the eager load will not take place. This depth setting is configured via ``join_depth``: .. sourcecode:: python+sql diff --git a/doc/build/ormtutorial.rst b/doc/build/ormtutorial.rst index 1754885701..a3157781ac 100644 --- a/doc/build/ormtutorial.rst +++ b/doc/build/ormtutorial.rst @@ -807,13 +807,15 @@ Let's look at the ``addresses`` collection. Watch the SQL: When we accessed the ``addresses`` collection, SQL was suddenly issued. This is an example of a **lazy loading relation**. The ``addresses`` collection is now loaded and behaves just like an ordinary list. -If you want to reduce the number of queries (dramatically, in many cases), we can apply an **eager load** to the query operation. With the same query, we may apply an **option** to the query, indicating that we'd like ``addresses`` to load "eagerly". SQLAlchemy then constructs an outer join between the ``users`` and ``addresses`` tables, and loads them at once, populating the ``addresses`` collection on each ``User`` object if it's not already populated: +If you want to reduce the number of queries (dramatically, in many cases), we can apply an **eager load** to the query operation, using the :func:`~sqlalchemy.orm.eagerload` function. This function is a **query option** that gives additional instructions to the query on how we would like it to load, in this case we'd like to indicate that we'd like ``addresses`` to load "eagerly". SQLAlchemy then constructs an outer join between the ``users`` and ``addresses`` tables, and loads them at once, populating the ``addresses`` collection on each ``User`` object if it's not already populated: .. sourcecode:: python+sql >>> from sqlalchemy.orm import eagerload - {sql}>>> jack = session.query(User).options(eagerload('addresses')).filter_by(name='jack').one() #doctest: +NORMALIZE_WHITESPACE + {sql}>>> jack = session.query(User).\ + ... options(eagerload('addresses')).\ + ... filter_by(name='jack').one() #doctest: +NORMALIZE_WHITESPACE SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password, addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id @@ -827,12 +829,12 @@ If you want to reduce the number of queries (dramatically, in many cases), we ca >>> jack.addresses [, ] -SQLAlchemy has the ability to control exactly which attributes and how many levels deep should be joined together in a single SQL query. More information on this feature is available in :ref:`advdatamapping_relation`. +See :func:`~sqlalchemy.orm.eagerload` for further detail. We'll also see another way to "eagerly" load in the next section. Querying with Joins ==================== -While the eager load created a JOIN specifically to populate a collection, we can also work explicitly with joins in many ways. For example, to construct a simple inner join between ``User`` and ``Address``, we can just :meth:`~sqlalchemy.orm.query.Query.filter()` their related columns together. Below we load the ``User`` and ``Address`` entities at once using this method: +While :func:`~sqlalchemy.orm.eagerload` created a JOIN specifically to populate a collection, we can also work explicitly with joins in many ways. For example, to construct a simple inner join between ``User`` and ``Address``, we can just :meth:`~sqlalchemy.orm.query.Query.filter()` their related columns together. Below we load the ``User`` and ``Address`` entities at once using this method: .. sourcecode:: python+sql @@ -847,12 +849,11 @@ While the eager load created a JOIN specifically to populate a collection, we ca ('jack@google.com',) {stop} -Or we can make a real JOIN construct; one way to do so is to use the ORM :func:`~sqlalchemy.orm.join()` function, and tell :class:`~sqlalchemy.orm.query.Query` to "select from" this join: +Or we can make a real JOIN construct; the most common way is to use :meth:`~sqlalchemy.orm.query.Query.join`: .. sourcecode:: python+sql - >>> from sqlalchemy.orm import join - {sql}>>> session.query(User).select_from(join(User, Address)).\ + {sql}>>> session.query(User).join(Address).\ ... filter(Address.email_address=='jack@google.com').all() #doctest: +NORMALIZE_WHITESPACE SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users JOIN addresses ON users.id = addresses.user_id @@ -860,39 +861,67 @@ Or we can make a real JOIN construct; one way to do so is to use the ORM :func:` ('jack@google.com',) {stop}[] -:func:`~sqlalchemy.orm.join()` knows how to join between ``User`` and ``Address`` because there's only one foreign key between them. If there were no foreign keys, or several, :func:`~sqlalchemy.orm.join()` would require a third argument indicating the ON clause of the join, in one of the following forms: +:meth:`~sqlalchemy.orm.query.Query.join` knows how to join between ``User`` and ``Address`` because there's only one foreign key between them. If there were no foreign keys, or several, :meth:`~sqlalchemy.orm.query.Query.join` works better when one of the following forms are used:: -.. sourcecode:: python+sql + query.join((Address, User.id==Address.user_id)) # explicit condition (note the tuple) + query.join(User.addresses) # specify relation from left to right + query.join((Address, User.addresses)) # same, with explicit target + query.join('addresses') # same, using a string - join(User, Address, User.id==Address.user_id) # explicit condition - join(User, Address, User.addresses) # specify relation from left to right - join(User, Address, 'addresses') # same, using a string +Note that when :meth:`~sqlalchemy.orm.query.Query.join` is called with an explicit target as well as an ON clause, we use a tuple as the argument. This is so that multiple joins can be chained together, as in:: -The functionality of :func:`~sqlalchemy.orm.join()` is also available generatively from :class:`~sqlalchemy.orm.query.Query` itself using :meth:`~sqlalchemy.orm.query.Query.join()`. This is most easily used with just the "ON" clause portion of the join, such as: + session.query(Foo).join( + Foo.bars, + (Bat, bar.bats), + (Widget, (Bat.widget_id==Widget.id)) + ) + +The above would produce SQL something like ``foo JOIN bars ON JOIN bats ON JOIN widgets ON ``. + +The general functionality of :meth:`~sqlalchemy.orm.query.Query.join()` is also available as a standalone function :func:`~sqlalchemy.orm.join`, which is an ORM-enabled version of the same function present in the SQL expression language. This function accepts two or three arguments (left side, right side, optional ON clause) and can be used in conjunction with +the :meth:`~sqlalchemy.orm.query.Query.select_from` method to set an explicit FROM clause: .. sourcecode:: python+sql - {sql}>>> session.query(User).join(User.addresses).\ - ... filter(Address.email_address=='jack@google.com').all() #doctest: +NORMALIZE_WHITESPACE + >>> from sqlalchemy.orm import join + {sql}>>> session.query(User).\ + ... select_from(join(User, Address, User.addresses)).\ + ... filter(Address.email_address=='jack@google.com').all() #doctest: +NORMALIZE_WHITESPACE SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users JOIN addresses ON users.id = addresses.user_id WHERE addresses.email_address = ? ('jack@google.com',) {stop}[] -To explicitly specify the target of the join, use tuples to form an argument list similar to the standalone join. This becomes more important when using aliases and similar constructs: - -.. sourcecode:: python+sql - - session.query(User).join((Address, User.addresses)) +Using join() to Eagerly Load Collections/Attributes +------------------------------------------------------- -Multiple joins can be created by passing a list of arguments: +The "eager loading" capabilities of the :func:`~sqlalchemy.orm.eagerload` function and the join-construction capabilities of :meth:`~sqlalchemy.orm.query.Query.join()` or an equivalent can be combined together using the :func:`~sqlalchemy.orm.contains_eager` option. This is typically used +for a query that is already joining to some related entity (more often than not via many-to-one), and you'd like the related entity to also be loaded onto the resulting objects +in one step without the need for additional queries and without the "automatic" join embedded +by the :func:`~sqlalchemy.orm.eagerload` function: .. sourcecode:: python+sql - session.query(Foo).join(Foo.bars, Bar.bats, (Bat, 'widgets')) + >>> from sqlalchemy.orm import contains_eager + {sql}>>> for address in session.query(Address).\ + ... join(Address.user).\ + ... filter(User.name=='jack').\ + ... options(contains_eager(Address.user)): #doctest: +NORMALIZE_WHITESPACE + ... print address, address.user + SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, + users.password AS users_password, addresses.id AS addresses_id, + addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id + FROM addresses JOIN users ON users.id = addresses.user_id + WHERE users.name = ? + ('jack',) + {stop} + + +Note that above the join was used both to limit the rows to just those ``Address`` objects which +had a related ``User`` object with the name "jack". It's safe to have the ``Address.user`` attribute populated with this user using an inner join. However, when filtering on a join that +is filtering on a particular member of a collection, using :func:`~sqlalchemy.orm.contains_eager` to populate a related collection may populate the collection with only part of what it actually references, since the collection itself is filtered. -The above would produce SQL something like ``foo JOIN bars ON JOIN bats ON JOIN widgets ON ``. Using Aliases ------------- diff --git a/doc/build/reference/orm/query.rst b/doc/build/reference/orm/query.rst index 8a33492a76..b209f6fef4 100644 --- a/doc/build/reference/orm/query.rst +++ b/doc/build/reference/orm/query.rst @@ -23,6 +23,8 @@ ORM-Specific Query Constructs .. autoclass:: aliased +.. autoclass:: sqlalchemy.orm.util.AliasedClass + .. autofunction:: join .. autofunction:: outerjoin diff --git a/lib/sqlalchemy/orm/__init__.py b/lib/sqlalchemy/orm/__init__.py index 96a08ea4ef..37baa03bef 100644 --- a/lib/sqlalchemy/orm/__init__.py +++ b/lib/sqlalchemy/orm/__init__.py @@ -895,7 +895,7 @@ def extension(ext): ``MapperExtension`` to the beginning of the list of extensions that will be called in the context of the ``Query``. - Used with ``query.options()``. + Used with :meth:`~sqlalchemy.orm.query.Query.options`. """ return ExtensionOption(ext) @@ -905,7 +905,7 @@ def eagerload(*keys, **kw): """Return a ``MapperOption`` that will convert the property of the given name into an eager load. - Used with ``query.options()``. + Used with :meth:`~sqlalchemy.orm.query.Query.options`. examples:: @@ -920,8 +920,15 @@ def eagerload(*keys, **kw): # to eagerload across both, use eagerload_all() query(Order).options(eagerload_all(Order.items, Item.keywords)) - The keyword arguments accept a flag `innerjoin=True|False` which will - override the value of the `innerjoin` flag specified on the relation(). + :func:`eagerload` also accepts a keyword argument `innerjoin=True` which + indicates using an inner join instead of an outer:: + + query(Order).options(eagerload(Order.user, innerjoin=True)) + + Note that the join created by :func:`eagerload` is aliased such that + no other aspects of the query will affect what it loads. To use eager + loading with a join that is constructed manually using :meth:`~sqlalchemy.orm.query.Query.join` + or :func:`~sqlalchemy.orm.join`, see :func:`contains_eager`. """ innerjoin = kw.pop('innerjoin', None) @@ -938,7 +945,7 @@ def eagerload_all(*keys, **kw): """Return a ``MapperOption`` that will convert all properties along the given dot-separated path into an eager load. - Used with ``query.options()``. + Used with :meth:`~sqlalchemy.orm.query.Query.options`. For example:: @@ -969,7 +976,7 @@ def lazyload(*keys): """Return a ``MapperOption`` that will convert the property of the given name into a lazy load. - Used with ``query.options()``. + Used with :meth:`~sqlalchemy.orm.query.Query.options`. """ return strategies.EagerLazyOption(keys, lazy=True) @@ -978,7 +985,7 @@ def noload(*keys): """Return a ``MapperOption`` that will convert the property of the given name into a non-load. - Used with ``query.options()``. + Used with :meth:`~sqlalchemy.orm.query.Query.options`. """ return strategies.EagerLazyOption(keys, lazy=None) @@ -996,29 +1003,52 @@ def contains_alias(alias): @sa_util.accepts_a_list_as_starargs(list_deprecation='deprecated') def contains_eager(*keys, **kwargs): """Return a ``MapperOption`` that will indicate to the query that - the given attribute will be eagerly loaded. + the given attribute should be eagerly loaded from columns currently + in the query. - Used when feeding SQL result sets directly into ``query.instances()``. - Also bundles an ``EagerLazyOption`` to turn on eager loading in case it - isn't already. + Used with :meth:`~sqlalchemy.orm.query.Query.options`. - `alias` is the string name of an alias, **or** an ``sql.Alias`` object, - which represents the aliased columns in the query. This argument is - optional. + The option is used in conjunction with an explicit join that loads + the desired rows, i.e.:: + + sess.query(Order).\\ + join(Order.user).\\ + options(contains_eager(Order.user)) + + The above query would join from the ``Order`` entity to its related + ``User`` entity, and the returned ``Order`` objects would have the + ``Order.user`` attribute pre-populated. + + :func:`contains_eager` also accepts an `alias` argument, which + is the string name of an alias, an :func:`~sqlalchemy.sql.expression.alias` + construct, or an :func:`~sqlalchemy.orm.aliased` construct. Use this + when the eagerly-loaded rows are to come from an aliased table:: + + user_alias = aliased(User) + sess.query(Order).\\ + join((user_alias, Order.user)).\\ + options(contains_eager(Order.user, alias=user_alias)) + + See also :func:`eagerload` for the "automatic" version of this + functionality. """ alias = kwargs.pop('alias', None) if kwargs: raise exceptions.ArgumentError("Invalid kwargs for contains_eager: %r" % kwargs.keys()) - return (strategies.EagerLazyOption(keys, lazy=False, propagate_to_loaders=False), strategies.LoadEagerFromAliasOption(keys, alias=alias)) + return ( + strategies.EagerLazyOption(keys, lazy=False, propagate_to_loaders=False), + strategies.LoadEagerFromAliasOption(keys, alias=alias) + ) @sa_util.accepts_a_list_as_starargs(list_deprecation='deprecated') def defer(*keys): """Return a ``MapperOption`` that will convert the column property of the given name into a deferred load. - Used with ``query.options()`` + Used with :meth:`~sqlalchemy.orm.query.Query.options`. + """ return strategies.DeferredOption(keys, defer=True) @@ -1027,7 +1057,7 @@ def undefer(*keys): """Return a ``MapperOption`` that will convert the column property of the given name into a non-deferred (regular column) load. - Used with ``query.options()``. + Used with :meth:`~sqlalchemy.orm.query.Query.options`. """ return strategies.DeferredOption(keys, defer=False) @@ -1036,7 +1066,7 @@ def undefer_group(name): """Return a ``MapperOption`` that will convert the given group of deferred column properties into a non-deferred (regular column) load. - Used with ``query.options()``. + Used with :meth:`~sqlalchemy.orm.query.Query.options`. """ return strategies.UndeferGroupOption(name) diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py index 919f980b6e..f00400bc28 100644 --- a/lib/sqlalchemy/orm/util.py +++ b/lib/sqlalchemy/orm/util.py @@ -275,16 +275,20 @@ class ORMAdapter(sql_util.ColumnAdapter): return None class AliasedClass(object): - """Represents an 'alias'ed form of a mapped class for usage with Query. + """Represents an "aliased" form of a mapped class for usage with Query. - The ORM equivalent of a :class:`~sqlalchemy.sql.expression.Alias` - object, this object mimics the mapped class using a + The ORM equivalent of a :func:`sqlalchemy.sql.expression.alias` + construct, this object mimics the mapped class using a __getattr__ scheme and maintains a reference to a - real Alias object. It indicates to Query that the - selectable produced for this class should be aliased, - and also adapts PropComparators produced by the class' - InstrumentedAttributes so that they adapt the - "local" side of SQL expressions against the alias. + real :class:`~sqlalchemy.sql.expression.Alias` object. + + Usage is via the :class:`~sqlalchemy.orm.aliased()` synonym:: + + # find all pairs of users with the same name + user_alias = aliased(User) + session.query(User, user_alias).\\ + join((user_alias, User.id > user_alias.id)).\\ + filter(User.name==user_alias.name) """ def __init__(self, cls, alias=None, name=None): -- 2.47.3