160
171
In theory (and pretty much in practice), nothing you can do to the :class:`.Query` would make it load
161
172
a different set of primary or related objects based on a change in loader strategy.
163
The way eagerloading does this, and in particular how :func:`joinedload`
164
works, is that it creates an anonymous alias of all the joins it adds to your
165
query, so that they can't be referenced by other parts of the query. If the
166
query contains a DISTINCT, or a limit or offset, the statement is first
167
wrapped inside a subquery, and joins are applied to that. As the user, you
168
don't have access to these aliases or subqueries, and you cannot affect what
169
data they will load at query time - a typical beginner misunderstanding is
170
that adding a :meth:`.Query.order_by`, naming the joined relationship, would
171
change the order of the collection, or that the entries in the collection as
172
it is loaded could be affected by :meth:`.Query.filter`. Not the case ! If
173
you'd like to join from one table to another, filtering or ordering on the
174
joined result, you'd use :meth:`.Query.join`. If you then wanted that joined
175
result to populate itself into a related collection, this is also available,
176
via :func:`.contains_eager` option - see :ref:`contains_eager`.
174
How :func:`joinedload` in particular achieves this result of not impacting
175
entity rows returned in any way is that it creates an anonymous alias of the joins it adds to your
176
query, so that they can't be referenced by other parts of the query. For example,
177
the query below uses :func:`.joinedload` to create a LEFT OUTER JOIN from ``users``
178
to ``addresses``, however the ``ORDER BY`` added against ``Address.email_address``
179
is not valid - the ``Address`` entity is not named in the query:
181
.. sourcecode:: python+sql
183
>>> jack = session.query(User).\
184
... options(joinedload(User.addresses)).\
185
... filter(User.name=='jack').\
186
... order_by(Address.email_address).all()
187
{opensql}SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address,
188
addresses_1.user_id AS addresses_1_user_id, users.id AS users_id, users.name AS users_name,
189
users.fullname AS users_fullname, users.password AS users_password
190
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
191
WHERE users.name = ? ORDER BY addresses.email_address <-- this part is wrong !
194
Above, ``ORDER BY addresses.email_address`` is not valid since ``addresses`` is not in the
195
FROM list. The correct way to load the ``User`` records and order by email
196
address is to use :meth:`.Query.join`:
198
.. sourcecode:: python+sql
200
>>> jack = session.query(User).\
201
... join(User.addresses).\
202
... filter(User.name=='jack').\
203
... order_by(Address.email_address).all()
205
SELECT users.id AS users_id, users.name AS users_name,
206
users.fullname AS users_fullname, users.password AS users_password
207
FROM users JOIN addresses ON users.id = addresses.user_id
208
WHERE users.name = ? ORDER BY addresses.email_address
211
The statement above is of course not the same as the previous one, in that the columns from ``addresses``
212
are not included in the result at all. We can add :func:`.joinedload` back in, so that
213
there are two joins - one is that which we are ordering on, the other is used anonymously to
214
load the contents of the ``User.addresses`` collection:
216
.. sourcecode:: python+sql
218
>>> jack = session.query(User).\
219
... join(User.addresses).\
220
... options(joinedload(User.addresses)).\
221
... filter(User.name=='jack').\
222
... order_by(Address.email_address).all()
223
{opensql}SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address,
224
addresses_1.user_id AS addresses_1_user_id, users.id AS users_id, users.name AS users_name,
225
users.fullname AS users_fullname, users.password AS users_password
226
FROM users JOIN addresses ON users.id = addresses.user_id
227
LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
228
WHERE users.name = ? ORDER BY addresses.email_address
231
What we see above is that our usage of :meth:`.Query.join` is to supply JOIN clauses we'd like
232
to use in subsequent query criterion, whereas our usage of :func:`.joinedload` only concerns
233
itself with the loading of the ``User.addresses`` collection, for each ``User`` in the result.
234
In this case, the two joins most probably appear redundant - which they are. If we
235
wanted to use just one JOIN for collection loading as well as ordering, we use the
236
:func:`.contains_eager` option, described in :ref:`contains_eager` below. But
237
to see why :func:`joinedload` does what it does, consider if we were **filtering** on a
238
particular ``Address``:
240
.. sourcecode:: python+sql
242
>>> jack = session.query(User).\
243
... join(User.addresses).\
244
... options(joinedload(User.addresses)).\
245
... filter(User.name=='jack').\
246
... filter(Address.email_address=='someaddress@foo.com').\
248
{opensql}SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address,
249
addresses_1.user_id AS addresses_1_user_id, users.id AS users_id, users.name AS users_name,
250
users.fullname AS users_fullname, users.password AS users_password
251
FROM users JOIN addresses ON users.id = addresses.user_id
252
LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
253
WHERE users.name = ? AND addresses.email_address = ?
254
['jack', 'someaddress@foo.com']
256
Above, we can see that the two JOINs have very different roles. One will match exactly
257
one row, that of the join of ``User`` and ``Address`` where ``Address.email_address=='someaddress@foo.com'``.
258
The other LEFT OUTER JOIN will match *all* ``Address`` rows related to ``User``,
259
and is only used to populate the ``User.addresses`` collection, for those ``User`` objects
262
By changing the usage of :func:`.joinedload` to another style of loading, we can change
263
how the collection is loaded completely independently of SQL used to retrieve
264
the actual ``User`` rows we want. Below we change :func:`.joinedload` into
265
:func:`.subqueryload`:
267
.. sourcecode:: python+sql
269
>>> jack = session.query(User).\
270
... join(User.addresses).\
271
... options(subqueryload(User.addresses)).\
272
... filter(User.name=='jack').\
273
... filter(Address.email_address=='someaddress@foo.com').\
275
{opensql}SELECT users.id AS users_id, users.name AS users_name,
276
users.fullname AS users_fullname, users.password AS users_password
277
FROM users JOIN addresses ON users.id = addresses.user_id
278
WHERE users.name = ? AND addresses.email_address = ?
279
['jack', 'someaddress@foo.com']
281
# ... subqueryload() emits a SELECT in order
282
# to load all address records ...
284
When using joined eager loading, if the
285
query contains a modifier that impacts the rows returned
286
externally to the joins, such as when using DISTINCT, LIMIT, OFFSET
287
or equivalent, the completed statement is first
288
wrapped inside a subquery, and the joins used specifically for joined eager
289
loading are applied to the subquery. SQLAlchemy's
290
joined eager loading goes the extra mile, and then ten miles further, to
291
absolutely ensure that it does not affect the end result of the query, only
292
the way collections and related objects are loaded, no matter what the format of the query is.
178
294
What Kind of Loading to Use ?
179
295
-----------------------------
added
removed
doc/_sources/orm/loading.txt
