Classe « Query »

Méthode sqlalchemy.orm.Query.join

Signature de la méthode join

def join(self, target, *props, **kwargs) 

Description

join.__doc__

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::

            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::

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

        **Legacy Features of Query.join()**

        .. deprecated:: 1.4 The following features are deprecated and will
           be removed in SQLAlchemy 2.0.

        The :meth:`_query.Query.join` method currently supports several
        usage patterns and arguments that are considered to be legacy
        as of SQLAlchemy 1.3.   A deprecation path will follow
        in the 1.4 series for the following features:


        * Joining on relationship names rather than attributes::

            session.query(User).join("addresses")

          **Why it's legacy**: the string name does not provide enough context
          for :meth:`_query.Query.join` to always know what is desired,
          notably in that there is no indication of what the left side
          of the join should be.  This gives rise to flags like
          ``from_joinpoint`` as well as the ability to place several
          join clauses in a single :meth:`_query.Query.join` call
          which don't solve the problem fully while also
          adding new calling styles that are unnecessary and expensive to
          accommodate internally.

          **Modern calling pattern**:  Use the actual relationship,
          e.g. ``User.addresses`` in the above case::

              session.query(User).join(User.addresses)

        * Automatic aliasing with the ``aliased=True`` flag::

            session.query(Node).join(Node.children, aliased=True).\
                filter(Node.name == 'some name')

          **Why it's legacy**:  the automatic aliasing feature of
          :class:`_query.Query` is intensely complicated, both in its internal
          implementation as well as in its observed behavior, and is almost
          never used.  It is difficult to know upon inspection where and when
          its aliasing of a target entity, ``Node`` in the above case, will be
          applied and when it won't, and additionally the feature has to use
          very elaborate heuristics to achieve this implicit behavior.

          **Modern calling pattern**: Use the :func:`_orm.aliased` construct
          explicitly::

            from sqlalchemy.orm import aliased

            n1 = aliased(Node)

            session.query(Node).join(Node.children.of_type(n1)).\
                filter(n1.name == 'some name')

        * Multiple joins in one call::

            session.query(User).join("orders", "items")

            session.query(User).join(User.orders, Order.items)

            session.query(User).join(
                (Order, User.orders),
                (Item, Item.order_id == Order.id)
            )

            session.query(User).join(Order, Item)

            # ... and several more forms actually

          **Why it's legacy**: being able to chain multiple ON clauses in one
          call to :meth:`_query.Query.join` is yet another attempt to solve
          the problem of being able to specify what entity to join from,
          and is the source of a large variety of potential calling patterns
          that are internally expensive and complicated to parse and
          accommodate.

          **Modern calling pattern**:  Use relationship-bound attributes
          or SQL-oriented ON clauses within separate calls, so that
          each call to :meth:`_query.Query.join` knows what the left
          side should be::

            session.query(User).join(User.orders).join(
                Item, Item.order_id == Order.id)


        :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``.

         .. versionadded:: 1.1

        :param 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.

         .. note:: This flag is considered legacy.

        :param aliased=False: If True, indicate that the JOIN target should be
         anonymously aliased.  Subsequent calls to :meth:`_query.Query.filter`
         and similar will adapt the incoming criterion to the target
         alias, until :meth:`_query.Query.reset_joinpoint` is called.

         .. note:: This flag is considered legacy.

        .. seealso::

            :ref:`ormtutorial_joins` in the ORM tutorial.

            :ref:`inheritance_toplevel` for details on how
            :meth:`_query.Query.join` is used for inheritance relationships.

            :func:`_orm.join` - a standalone ORM-level join function,
            used internally by :meth:`_query.Query.join`, which in previous
            SQLAlchemy versions was the primary ORM-level joining interface.