892
892
many-to-one/one-to-many based on foreign keys.
894
894
.. note:: The :func:`~sqlalchemy.orm.relationship()` function has historically
895
been known as :func:`~sqlalchemy.orm.relation()`, which is the name that's
896
available in all versions of SQLAlchemy prior to 0.6beta2, including the 0.5
897
and 0.4 series. :func:`~sqlalchemy.orm.relationship()` is only available
898
starting with SQLAlchemy 0.6beta2. :func:`~sqlalchemy.orm.relation()` will
899
remain available in SQLAlchemy for the foreseeable future to enable
895
been known as :func:`~sqlalchemy.orm.relation()` in the 0.5 series of
896
SQLAlchemy and earlier.
902
898
The :func:`~sqlalchemy.orm.relationship()` function is extremely flexible, and
903
899
could just have easily been defined on the ``User`` class:
1007
1003
.. sourcecode:: python+sql
1009
{sql}>>> jack = session.query(User).filter_by(name='jack').one() #doctest: +NORMALIZE_WHITESPACE
1005
{sql}>>> jack = session.query(User).\
1006
... filter_by(name='jack').one() #doctest: +NORMALIZE_WHITESPACE
1010
1007
BEGIN (implicit)
1011
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
1008
SELECT users.id AS users_id, users.name AS users_name,
1009
users.fullname AS users_fullname, users.password AS users_password
1013
1011
WHERE users.name = ?
1021
1019
.. sourcecode:: python+sql
1023
1021
{sql}>>> jack.addresses #doctest: +NORMALIZE_WHITESPACE
1024
SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
1022
SELECT addresses.id AS addresses_id, addresses.email_address AS
1023
addresses_email_address, addresses.user_id AS addresses_user_id
1026
1025
WHERE ? = addresses.user_id ORDER BY addresses.id
1065
1064
:func:`~sqlalchemy.orm.subqueryload`. We'll also see another way to "eagerly"
1066
1065
load in the next section.
1067
.. note:: The join created by :func:`.joinedload` is anonymously aliased such that
1068
it **does not affect the query results**. An :meth:`.Query.order_by`
1069
or :meth:`.Query.filter` call **cannot** reference these aliased
1070
tables - so-called "user space" joins are constructed using
1071
:meth:`.Query.join`. The rationale for this is that :func:`.joinedload` is only
1072
applied in order to affect how related objects or collections are loaded
1073
as an optimizing detail - it can be added or removed with no impact
1074
on actual results. See the section :ref:`zen_of_eager_loading` for
1075
a detailed description of how this is used, including how to use a single
1076
explicit JOIN for filtering/ordering and eager loading simultaneously.
1078
.. _ormtutorial_joins:
1068
1080
Querying with Joins
1069
1081
====================
1071
While :func:`~sqlalchemy.orm.joinedload` created a JOIN specifically to
1083
While :func:`~sqlalchemy.orm.joinedload` created an anonymous, non-accessible JOIN
1072
1085
populate a collection, we can also work explicitly with joins in many ways.
1073
1086
For example, to construct a simple inner join between ``User`` and
1074
1087
``Address``, we can just :meth:`~sqlalchemy.orm.query.Query.filter()` their
1101
1114
('jack@google.com',)
1102
1115
{stop}[<User('jack','Jack Bean', 'gjffdd')>]
1104
: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::
1117
:meth:`~sqlalchemy.orm.query.Query.join` knows how to join between ``User``
1118
and ``Address`` because there's only one foreign key between them. If there
1119
were no foreign keys, or several, :meth:`~sqlalchemy.orm.query.Query.join`
1120
works better when one of the following forms are used::
1106
query.join((Address, User.id==Address.user_id)) # explicit condition (note the tuple)
1122
query.join(Address, User.id==Address.user_id) # explicit condition
1107
1123
query.join(User.addresses) # specify relationship from left to right
1108
query.join((Address, User.addresses)) # same, with explicit target
1124
query.join(Address, User.addresses) # same, with explicit target
1109
1125
query.join('addresses') # same, using a string
1111
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::
1127
As you would expect, the same idea is used for "outer" joins, using the
1128
:meth:`~.Query.outerjoin` function::
1130
query.outerjoin(User.addresses) # LEFT OUTER JOIN
1132
Note that when :meth:`~sqlalchemy.orm.query.Query.join` is called with an
1133
explicit target as well as an ON clause, we use a tuple as the argument. This
1134
is so that multiple joins can be chained together, as in::
1113
1136
session.query(Foo).join(
1196
1219
>>> adalias2 = aliased(Address)
1197
1220
{sql}>>> for username, email1, email2 in \
1198
1221
... session.query(User.name, adalias1.email_address, adalias2.email_address).\
1199
... join((adalias1, User.addresses), (adalias2, User.addresses)).\
1222
... join(adalias1, User.addresses).\
1223
... join(adalias2, User.addresses).\
1200
1224
... filter(adalias1.email_address=='jack@google.com').\
1201
1225
... filter(adalias2.email_address=='j25@yahoo.com'):
1202
1226
... print username, email1, email2 # doctest: +NORMALIZE_WHITESPACE
1244
1268
.. sourcecode:: python+sql
1246
1270
{sql}>>> for u, count in session.query(User, stmt.c.address_count).\
1247
... outerjoin((stmt, User.id==stmt.c.user_id)).order_by(User.id): # doctest: +NORMALIZE_WHITESPACE
1271
... outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id): # doctest: +NORMALIZE_WHITESPACE
1248
1272
... print u, count
1249
1273
SELECT users.id AS users_id, users.name AS users_name,
1250
1274
users.fullname AS users_fullname, users.password AS users_password,
1271
1295
{sql}>>> stmt = session.query(Address).filter(Address.email_address != 'j25@yahoo.com').subquery()
1272
1296
>>> adalias = aliased(Address, stmt)
1273
>>> for user, address in session.query(User, adalias).join((adalias, User.addresses)): # doctest: +NORMALIZE_WHITESPACE
1297
>>> for user, address in session.query(User, adalias).join(adalias, User.addresses): # doctest: +NORMALIZE_WHITESPACE
1274
1298
... print user, address
1275
1299
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname,
1276
1300
users.password AS users_password, anon_1.id AS anon_1_id,
1353
1377
Common Relationship Operators
1354
1378
-----------------------------
1356
Here's all the operators which build on relationships:
1380
Here's all the operators which build on relationships - each one
1381
is linked to its API documentation which includes full details on usage
1358
* equals (used for many-to-one)::
1384
* :meth:`~.RelationshipProperty.Comparator.__eq__` (many-to-one "equals" comparison)::
1360
1386
query.filter(Address.user == someuser)
1362
* not equals (used for many-to-one)::
1388
* :meth:`~.RelationshipProperty.Comparator.__ne__` (many-to-one "not equals" comparison)::
1364
1390
query.filter(Address.user != someuser)
1366
* IS NULL (used for many-to-one)::
1392
* IS NULL (many-to-one comparison, also uses :meth:`~.RelationshipProperty.Comparator.__eq__`)::
1368
1394
query.filter(Address.user == None)
1370
* contains (used for one-to-many and many-to-many collections)::
1396
* :meth:`~.RelationshipProperty.Comparator.contains` (used for one-to-many collections)::
1372
1398
query.filter(User.addresses.contains(someaddress))
1374
* any (used for one-to-many and many-to-many collections)::
1400
* :meth:`~.RelationshipProperty.Comparator.any` (used for collections)::
1376
1402
query.filter(User.addresses.any(Address.email_address == 'bar'))
1378
1404
# also takes keyword arguments:
1379
1405
query.filter(User.addresses.any(email_address='bar'))
1381
* has (used for many-to-one)::
1407
* :meth:`~.RelationshipProperty.Comparator.has` (used for scalar references)::
1383
1409
query.filter(Address.user.has(name='ed'))
1385
* with_parent (used for any relationship)::
1411
* :meth:`.Query.with_parent` (used for any relationship)::
1387
1413
session.query(Address).with_parent(someuser, 'addresses')
1465
1491
>>> mapper(Address, addresses_table) # doctest: +ELLIPSIS
1466
1492
<Mapper at 0x...; Address>
1468
Now when we load Jack (below using :meth:`~Query.get`, which loads by primary key),
1494
Now when we load Jack (below using :meth:`~.Query.get`, which loads by primary key),
1469
1495
removing an address from his ``addresses`` collection will result in that
1470
1496
``Address`` being deleted:
added
removed
doc/build/orm/tutorial.rst
