Create a SQL JOIN against this Query object’s criterion
and apply generatively, returning the newly resulting Query.
Simple Relationship Joins
Consider a mapping between two classes User and Address,
with a relationship User.addresses representing a collection
of Address objects associated with each User. The most
common usage of join() is to create a JOIN along this
relationship, using the User.addresses attribute as an indicator
for how this should occur:
q = session.query(User).join(User.addresses)
Where above, the call to join() along User.addresses
will result in SQL equivalent to:
SELECT user.* FROM user JOIN address ON user.id = address.user_id
In the above example we refer to User.addresses as passed to
join() as the on clause, that is, it indicates
how the “ON” portion of the JOIN should be constructed. For a
single-entity query such as the one above (i.e. we start by selecting
only from User and nothing else), the relationship can also be
specified by its string name:
q = session.query(User).join("addresses")
join() can also accommodate multiple
“on clause” arguments to produce a chain of joins, such as below
where a join across four related entities is constructed:
q = session.query(User).join("orders", "items", "keywords")
The above would be shorthand for three separate calls to
join(), each using an explicit attribute to indicate
the source entity:
q = session.query(User).\
join(User.orders).\
join(Order.items).\
join(Item.keywords)
Joins to a Target Entity or Selectable
A second form of join() allows any mapped entity
or core selectable construct as a target. In this usage,
join() will attempt
to create a JOIN along the natural foreign key relationship between
two entities:
q = session.query(User).join(Address)
The above calling form of join() will raise an error if
either there are no foreign keys between the two entities, or if
there are multiple foreign key linkages between them. In the
above calling form, join() is called upon to
create the “on clause” automatically for us. The target can
be any mapped entity or selectable, such as a Table:
q = session.query(User).join(addresses_table)
Joins to a Target with an ON Clause
The third calling form allows both the target entity as well
as the ON clause to be passed explicitly. Suppose for
example we wanted to join to Address twice, using
an alias the second time. We use aliased()
to create a distinct alias of Address, and join
to it using the target, onclause form, so that the
alias can be specified explicitly as the target along with
the relationship to instruct how the ON clause should proceed:
a_alias = aliased(Address)
q = session.query(User).\
join(User.addresses).\
join(a_alias, User.addresses).\
filter(Address.email_address=='ed@foo.com').\
filter(a_alias.email_address=='ed@bar.com')
Where above, the generated SQL would be similar to:
SELECT user.* FROM user
JOIN address ON user.id = address.user_id
JOIN address AS address_1 ON user.id=address_1.user_id
WHERE address.email_address = :email_address_1
AND address_1.email_address = :email_address_2
The two-argument calling form of join()
also allows us to construct arbitrary joins with SQL-oriented
“on clause” expressions, not relying upon configured relationships
at all. Any SQL expression can be passed as the ON clause
when using the two-argument form, which should refer to the target
entity in some way as well as an applicable source entity:
q = session.query(User).join(Address, User.id==Address.user_id)
Advanced Join Targeting and Adaption
There is a lot of flexibility in what the “target” can be when using
join(). As noted previously, it also accepts
Table constructs and other selectables such as
alias() and select() constructs, with either the one
or two-argument forms:
addresses_q = select([Address.user_id]).\
where(Address.email_address.endswith("@bar.com")).\
alias()
q = session.query(User).\
join(addresses_q, addresses_q.c.user_id==User.id)
join() also features the ability to adapt a
relationship() -driven ON clause to the target
selectable. Below we construct a JOIN from User to a subquery
against Address, allowing the relationship denoted by
User.addresses to adapt itself to the altered target:
address_subq = session.query(Address).\
filter(Address.email_address == 'ed@foo.com').\
subquery()
q = session.query(User).join(address_subq, User.addresses)
Producing SQL similar to:
SELECT user.* FROM user
JOIN (
SELECT address.id AS id,
address.user_id AS user_id,
address.email_address AS email_address
FROM address
WHERE address.email_address = :email_address_1
) AS anon_1 ON user.id = anon_1.user_id
The above form allows one to fall back onto an explicit ON
clause at any time:
q = session.query(User).\
join(address_subq, User.id==address_subq.c.user_id)
Controlling what to Join From
While join() exclusively deals with the “right”
side of the JOIN, we can also control the “left” side, in those
cases where it’s needed, using select_from().
Below we construct a query against Address but can still
make usage of User.addresses as our ON clause by instructing
the Query to select first from the User
entity:
q = session.query(Address).select_from(User).\
join(User.addresses).\
filter(User.name == 'ed')
Which will produce SQL similar to:
SELECT address.* FROM user
JOIN address ON user.id=address.user_id
WHERE user.name = :name_1
Constructing Aliases Anonymously
join() can construct anonymous aliases
using the aliased=True flag. This feature is useful
when a query is being joined algorithmically, such as
when querying self-referentially to an arbitrary depth:
q = session.query(Node).\
join("children", "children", aliased=True)
When aliased=True is used, the actual “alias” construct
is not explicitly available. To work with it, methods such as
Query.filter() will adapt the incoming entity to
the last join point:
q = session.query(Node).\
join("children", "children", aliased=True).\
filter(Node.name == 'grandchild 1')
When using automatic aliasing, the from_joinpoint=True
argument can allow a multi-node join to be broken into
multiple calls to join(), so that
each path along the way can be further filtered:
q = session.query(Node).\
join("children", aliased=True).\
filter(Node.name='child 1').\
join("children", aliased=True, from_joinpoint=True).\
filter(Node.name == 'grandchild 1')
The filtering aliases above can then be reset back to the
original Node entity using reset_joinpoint():
q = session.query(Node).\
join("children", "children", aliased=True).\
filter(Node.name == 'grandchild 1').\
reset_joinpoint().\
filter(Node.name == 'parent 1)
For an example of aliased=True, see the distribution
example XML Persistence which illustrates
an XPath-like query system using algorithmic joins.
- Parameters
*props – A collection of one or more join conditions,
each consisting of a relationship-bound attribute or string
relationship name representing an “on clause”, or a single
target entity, or a tuple in the form of (target, onclause).
A special two-argument calling form of the form target, onclause
is also accepted.
aliased=False – If True, indicate that the JOIN target should be
anonymously aliased. Subsequent calls to filter()
and similar will adapt the incoming criterion to the target
alias, until reset_joinpoint() is called.
isouter=False –
If True, the join used will be a left outer join,
just as if the Query.outerjoin() method were called. This
flag is here to maintain consistency with the same flag as accepted
by FromClause.join() and other Core constructs.
full=False –
render FULL OUTER JOIN; implies isouter.
from_joinpoint=False – When using aliased=True, a setting
of True here will cause the join to be from the most recent
joined target, rather than starting back from the original
FROM clauses of the query.