Vous êtes un professionnel et vous avez besoin d'une formation ? Machine Learning
avec Scikit-Learn Voir le programme détaillé

Classe « Query »

Méthode sqlalchemy.orm.Query.join

Signature de la méthode join

def join(self, target: '_JoinTargetArgument', onclause: 'Optional[_OnClauseArgument]' = None, *, isouter: 'bool' = False, full: 'bool' = False) -> 'Self' 

Description

help(Query.join)

Create a SQL JOIN against this :class:`_query.Query`
object's criterion
and apply generatively, returning the newly resulting
:class:`_query.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 :meth:`_query.Query.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 :meth:`_query.Query.join` along
``User.addresses`` will result in SQL approximately equivalent to:

.. sourcecode:: sql

    SELECT user.id, user.name
    FROM user JOIN address ON user.id = address.user_id

In the above example we refer to ``User.addresses`` as passed to
:meth:`_query.Query.join` as the "on clause", that is, it indicates
how the "ON" portion of the JOIN should be constructed.

To construct a chain of joins, multiple :meth:`_query.Query.join`
calls may be used.  The relationship-bound attribute implies both
the left and right side of the join at once::

    q = (
        session.query(User)
        .join(User.orders)
        .join(Order.items)
        .join(Item.keywords)
    )

.. note:: as seen in the above example, **the order in which each
   call to the join() method occurs is important**.    Query would not,
   for example, know how to join correctly if we were to specify
   ``User``, then ``Item``, then ``Order``, in our chain of joins; in
   such a case, depending on the arguments passed, it may raise an
   error that it doesn't know how to join, or it may produce invalid
   SQL in which case the database will raise an error. In correct
   practice, the
   :meth:`_query.Query.join` method is invoked in such a way that lines
   up with how we would want the JOIN clauses in SQL to be
   rendered, and each call should represent a clear link from what
   precedes it.

**Joins to a Target Entity or Selectable**

A second form of :meth:`_query.Query.join` allows any mapped entity or
core selectable construct as a target.   In this usage,
:meth:`_query.Query.join` will attempt to create a JOIN along the
natural foreign key relationship between two entities::

    q = session.query(User).join(Address)

In the above calling form, :meth:`_query.Query.join` is called upon to
create the "on clause" automatically for us.  This calling form will
ultimately raise an error if either there are no foreign keys between
the two entities, or if there are multiple foreign key linkages between
the target entity and the entity or entities already present on the
left side such that creating a join requires more information.  Note
that when indicating a join to a target without any ON clause, ORM
configured relationships are not taken into account.

**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.    A example that includes
a SQL expression as the ON clause is as follows::

    q = session.query(User).join(Address, User.id == Address.user_id)

The above form may also use a relationship-bound attribute as the
ON clause as well::

    q = session.query(User).join(Address, User.addresses)

The above syntax can be useful for the case where we wish
to join to an alias of a particular target entity.  If we wanted
to join to ``Address`` twice, it could be achieved using two
aliases set up using the :func:`~sqlalchemy.orm.aliased` function::

    a1 = aliased(Address)
    a2 = aliased(Address)

    q = (
        session.query(User)
        .join(a1, User.addresses)
        .join(a2, User.addresses)
        .filter(a1.email_address == "ed@foo.com")
        .filter(a2.email_address == "ed@bar.com")
    )

The relationship-bound calling form can also specify a target entity
using the :meth:`_orm.PropComparator.of_type` method; a query
equivalent to the one above would be::

    a1 = aliased(Address)
    a2 = aliased(Address)

    q = (
        session.query(User)
        .join(User.addresses.of_type(a1))
        .join(User.addresses.of_type(a2))
        .filter(a1.email_address == "ed@foo.com")
        .filter(a2.email_address == "ed@bar.com")
    )

**Augmenting Built-in ON Clauses**

As a substitute for providing a full custom ON condition for an
existing relationship, the :meth:`_orm.PropComparator.and_` function
may be applied to a relationship attribute to augment additional
criteria into the ON clause; the additional criteria will be combined
with the default criteria using AND::

    q = session.query(User).join(
        User.addresses.and_(Address.email_address != "foo@bar.com")
    )

.. versionadded:: 1.4

**Joining to Tables and Subqueries**


The target of a join may also be any table or SELECT statement,
which may be related to a target entity or not.   Use the
appropriate ``.subquery()`` method in order to make a subquery
out of a query::

    subq = (
        session.query(Address)
        .filter(Address.email_address == "ed@foo.com")
        .subquery()
    )


    q = session.query(User).join(subq, User.id == subq.c.user_id)

Joining to a subquery in terms of a specific relationship and/or
target entity may be achieved by linking the subquery to the
entity using :func:`_orm.aliased`::

    subq = (
        session.query(Address)
        .filter(Address.email_address == "ed@foo.com")
        .subquery()
    )

    address_subq = aliased(Address, subq)

    q = session.query(User).join(User.addresses.of_type(address_subq))

**Controlling what to Join From**

In cases where the left side of the current state of
:class:`_query.Query` is not in line with what we want to join from,
the :meth:`_query.Query.select_from` method may be used::

    q = (
        session.query(Address)
        .select_from(User)
        .join(User.addresses)
        .filter(User.name == "ed")
    )

Which will produce SQL similar to:

.. sourcecode:: sql

    SELECT address.* FROM user
        JOIN address ON user.id=address.user_id
        WHERE user.name = :name_1

.. seealso::

    :meth:`_sql.Select.join` - v2 equivalent method.

:param \*props: Incoming arguments for :meth:`_query.Query.join`,
 the props collection in modern use should be considered to be a  one
 or two argument form, either as a single "target" entity or ORM
 attribute-bound relationship, or as a target entity plus an "on
 clause" which  may be a SQL expression or ORM attribute-bound
 relationship.

:param isouter=False: If True, the join used will be a left outer join,
 just as if the :meth:`_query.Query.outerjoin` method were called.

:param full=False: render FULL OUTER JOIN; implies ``isouter``.

Vous êtes un professionnel et vous avez besoin d'une formation ? Deep Learning avec Python
et Keras et Tensorflow Voir le programme détaillé