~ubuntu-branches/debian/jessie/sqlalchemy/jessie

The <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> function has historically

860

been known as <a class="reference internal" href="relationships.html#sqlalchemy.orm.relation" title="sqlalchemy.orm.relation"><tt class="xref py py-func docutils literal">relation()</tt></a>, which is the name that’s

861

available in all versions of SQLAlchemy prior to 0.6beta2, including the 0.5

862

and 0.4 series. <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> is only available

863

starting with SQLAlchemy 0.6beta2. <a class="reference internal" href="relationships.html#sqlalchemy.orm.relation" title="sqlalchemy.orm.relation"><tt class="xref py py-func docutils literal">relation()</tt></a> will

864

remain available in SQLAlchemy for the foreseeable future to enable

865

cross-compatibility.

860

861

SQLAlchemy and earlier.

866

862

</div>

867

863

The <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> function is extremely flexible, and

868

864

could just have easily been defined on the <tt class="docutils literal">User</tt> class:

945

941

COMMIT</div></pre></div>

946

942

</div>

947

943

Querying for Jack, we get just Jack back. No SQL is yet issued for Jack’s addresses:

948

<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a>>>> jack = session.query(User).filter_by(name='jack').one()

944

945

... filter_by(name='jack').one()

949

946

<div class='popup_sql'>BEGIN (implicit)

950

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password

947

SELECT users.id AS users_id, users.name AS users_name,

948

users.fullname AS users_fullname, users.password AS users_password

951

949

FROM users

952

950

WHERE users.name = ?

953

951

('jack',)</div>>>> jack

955

953

</div>

956

954

Let’s look at the <tt class="docutils literal">addresses</tt> collection. Watch the SQL:

957

955

958

<div class='popup_sql'>SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id

956

<div class='popup_sql'>SELECT addresses.id AS addresses_id, addresses.email_address AS

957

addresses_email_address, addresses.user_id AS addresses_user_id

959

958

FROM addresses

960

959

WHERE ? = addresses.user_id ORDER BY addresses.id

961

960

(5,)</div>[<Address('jack@google.com')>, <Address('j25@yahoo.com')>]</pre></div>

991

990

<a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal">joinedload()</tt></a> and its new brother,

992

991

<a class="reference internal" href="loading.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal">subqueryload()</tt></a>. We’ll also see another way to “eagerly”

993

992

load in the next section.

993

994

Note

995

The join created by <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal">joinedload()</tt></a> is anonymously aliased such that

996

it does not affect the query results. An <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.order_by" title="sqlalchemy.orm.query.Query.order_by"><tt class="xref py py-meth docutils literal">Query.order_by()</tt></a>

997

or <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-meth docutils literal">Query.filter()</tt></a> call cannot reference these aliased

998

tables - so-called “user space” joins are constructed using

999

<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal">Query.join()</tt></a>. The rationale for this is that <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal">joinedload()</tt></a> is only

1000

applied in order to affect how related objects or collections are loaded

1001

as an optimizing detail - it can be added or removed with no impact

1002

on actual results. See the section <a class="reference internal" href="loading.html#zen-of-eager-loading">The Zen of Eager Loading</a> for

1003

a detailed description of how this is used, including how to use a single

1004

explicit JOIN for filtering/ordering and eager loading simultaneously.

1005

</div>

994

1006

</div>

995

1007

996

<h2>Querying with Joins<a class="headerlink" href="#querying-with-joins" title="Permalink to this headline">¶</a></h2>

997

While <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal">joinedload()</tt></a> created a JOIN specifically to

1008

<h2>Querying with Joins<a class="headerlink" href="#querying-with-joins" title="Permalink to this headline">¶</a></h2>

1009

1010

specifically to

998

1011

populate a collection, we can also work explicitly with joins in many ways.

999

1012

For example, to construct a simple inner join between <tt class="docutils literal">User</tt> and

1000

1013

<tt class="docutils literal">Address</tt>, we can just <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-meth docutils literal">filter()</tt></a> their

1019

1032

WHERE addresses.email_address = ?

1020

1033

('jack@google.com',)</div>[<User('jack','Jack Bean', 'gjffdd')>]</pre></div>

1021

1034

</div>

1022

<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal">join()</tt></a> knows how to join between <tt class="docutils literal">User</tt> and <tt class="docutils literal">Address</tt> because there’s only one foreign key between them. If there were no foreign keys, or several, <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal">join()</tt></a> works better when one of the following forms are used:

1023

<div class="highlight-python"><div class="highlight"><pre>query.join((Address, User.id==Address.user_id)) # explicit condition (note the tuple)

1035

1036

and <tt class="docutils literal">Address</tt> because there’s only one foreign key between them. If there

1037

were no foreign keys, or several, <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal">join()</tt></a>

1038

works better when one of the following forms are used:

1039

<div class="highlight-python"><div class="highlight"><pre>query.join(Address, User.id==Address.user_id) # explicit condition

1024

1040

query.join(User.addresses) # specify relationship from left to right

1025

query.join((Address, User.addresses)) # same, with explicit target

1041

query.join(Address, User.addresses) # same, with explicit target

1026

1042

query.join('addresses') # same, using a string</pre></div>

1027

1043

</div>

1028

Note that when <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal">join()</tt></a> 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:

1044

As you would expect, the same idea is used for “outer” joins, using the

1045

<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.outerjoin" title="sqlalchemy.orm.query.Query.outerjoin"><tt class="xref py py-meth docutils literal">outerjoin()</tt></a> function:

1046

<div class="highlight-python"><div class="highlight"><pre>query.outerjoin(User.addresses) # LEFT OUTER JOIN</pre></div>

1047

</div>

1048

1049

explicit target as well as an ON clause, we use a tuple as the argument. This

1050

is so that multiple joins can be chained together, as in:

1029

1051

1030

1052

Foo.bars,

1031

1053

(Bat, bar.bats),

1098

1120

>>> adalias2 = aliased(Address)

1099

1121

<a href='#' class='sql_link'>sql</a>>>> for username, email1, email2 in \

1100

1122

... session.query(User.name, adalias1.email_address, adalias2.email_address).\

1101

... join((adalias1, User.addresses), (adalias2, User.addresses)).\

1123

... join(adalias1, User.addresses).\

1124

... join(adalias2, User.addresses).\

1102

1125

... filter(adalias1.email_address=='jack@google.com').\

1103

1126

... filter(adalias2.email_address=='j25@yahoo.com'):

1104

1127

... print username, email1, email2

1139

1162

<tt class="docutils literal">users</tt> at the start of this tutorial. The columns on the statement are

1140

1163

accessible through an attribute called <tt class="docutils literal">c</tt>:

1141

1164

<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a>>>> for u, count in session.query(User, stmt.c.address_count).\

1142

... outerjoin((stmt, User.id==stmt.c.user_id)).order_by(User.id):

1165

... outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id):

1143

1166

... print u, count

1144

1167

<div class='popup_sql'>SELECT users.id AS users_id, users.name AS users_name,

1145

1168

users.fullname AS users_fullname, users.password AS users_password,

1161

1184

to associate an “alias” of a mapped class to a subquery:

1162

1185

<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a>>>> stmt = session.query(Address).filter(Address.email_address != 'j25@yahoo.com').subquery()

1163

1186

>>> adalias = aliased(Address, stmt)

1164

>>> for user, address in session.query(User, adalias).join((adalias, User.addresses)):

1187

>>> for user, address in session.query(User, adalias).join(adalias, User.addresses):

1165

1188

... print user, address

1166

1189

<div class='popup_sql'>SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname,

1167

1190

users.password AS users_password, anon_1.id AS anon_1_id,

1226

1249

</div>

1227

1250

1228

1251

<h3>Common Relationship Operators<a class="headerlink" href="#common-relationship-operators" title="Permalink to this headline">¶</a></h3>

1229

Here’s all the operators which build on relationships:

1252

Here’s all the operators which build on relationships - each one

1253

is linked to its API documentation which includes full details on usage

1254

and behavior:

1230

1255

<ul>

1231

<li>equals (used for many-to-one):

1256

<li><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__"><tt class="xref py py-meth docutils literal">__eq__()</tt></a> (many-to-one “equals” comparison):

1232

1257

1233

1258

</div>

1234

1259

</li>

1235

<li>not equals (used for many-to-one):

1260

<li><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__ne__" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.__ne__"><tt class="xref py py-meth docutils literal">__ne__()</tt></a> (many-to-one “not equals” comparison):

1236

1261

1237

1262

</div>

1238

1263

</li>

1239

<li>IS NULL (used for many-to-one):

1264

<li>IS NULL (many-to-one comparison, also uses <a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__"><tt class="xref py py-meth docutils literal">__eq__()</tt></a>):

1240

1265

1241

1266

</div>

1242

1267

</li>

1243

<li>contains (used for one-to-many and many-to-many collections):

1268

<li><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains"><tt class="xref py py-meth docutils literal">contains()</tt></a> (used for one-to-many collections):

1244

1269

<div class="highlight-python"><div class="highlight"><pre>query.filter(User.addresses.contains(someaddress))</pre></div>

1245

1270

</div>

1246

1271

</li>

1247

<li>any (used for one-to-many and many-to-many collections):

1272

<li><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.any"><tt class="xref py py-meth docutils literal">any()</tt></a> (used for collections):

1248

1273

<div class="highlight-python"><div class="highlight"><pre>query.filter(User.addresses.any(Address.email_address == 'bar'))

1249

1274

1250

1275

# also takes keyword arguments:

1251

1276

query.filter(User.addresses.any(email_address='bar'))</pre></div>

1252

1277

</div>

1253

1278

</li>

1254

<li>has (used for many-to-one):

1279

<li><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.has" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.has"><tt class="xref py py-meth docutils literal">has()</tt></a> (used for scalar references):

1255

1280

<div class="highlight-python"><div class="highlight"><pre>query.filter(Address.user.has(name='ed'))</pre></div>

1256

1281

</div>

1257

1282

</li>

1258

<li>with_parent (used for any relationship):

1283

<li><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.with_parent" title="sqlalchemy.orm.query.Query.with_parent"><tt class="xref py py-meth docutils literal">Query.with_parent()</tt></a> (used for any relationship):

1259

1284

<div class="highlight-python"><div class="highlight"><pre>session.query(Address).with_parent(someuser, 'addresses')</pre></div>

1260

1285

</div>

1261

1286

</li>

1325

1350

>>> mapper(Address, addresses_table)

1326

1351

1327

1352

</div>

1328

Now when we load Jack (below using <tt class="xref py py-meth docutils literal">get()</tt>, which loads by primary key),

1353

Now when we load Jack (below using <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.get" title="sqlalchemy.orm.query.Query.get"><tt class="xref py py-meth docutils literal">get()</tt></a>, which loads by primary key),

1329

1354

removing an address from his <tt class="docutils literal">addresses</tt> collection will result in that

1330

1355

<tt class="docutils literal">Address</tt> being deleted:

1331

1356

<div class="highlight-python+sql"><div class="highlight"><pre># load Jack by primary key

Older »