Classe « Query »

Méthode sqlalchemy.orm.Query.cte

Signature de la méthode cte

def cte(self, name=None, recursive=False, nesting=False) 

Description

cte.__doc__

Return the full SELECT statement represented by this
        :class:`_query.Query` represented as a common table expression (CTE).

        Parameters and usage are the same as those of the
        :meth:`_expression.SelectBase.cte` method; see that method for
        further details.

        Here is the `PostgreSQL WITH
        RECURSIVE example
        <https://www.postgresql.org/docs/8.4/static/queries-with.html>`_.
        Note that, in this example, the ``included_parts`` cte and the
        ``incl_alias`` alias of it are Core selectables, which
        means the columns are accessed via the ``.c.`` attribute.  The
        ``parts_alias`` object is an :func:`_orm.aliased` instance of the
        ``Part`` entity, so column-mapped attributes are available
        directly::

            from sqlalchemy.orm import aliased

            class Part(Base):
                __tablename__ = 'part'
                part = Column(String, primary_key=True)
                sub_part = Column(String, primary_key=True)
                quantity = Column(Integer)

            included_parts = session.query(
                            Part.sub_part,
                            Part.part,
                            Part.quantity).\
                                filter(Part.part=="our part").\
                                cte(name="included_parts", recursive=True)

            incl_alias = aliased(included_parts, name="pr")
            parts_alias = aliased(Part, name="p")
            included_parts = included_parts.union_all(
                session.query(
                    parts_alias.sub_part,
                    parts_alias.part,
                    parts_alias.quantity).\
                        filter(parts_alias.part==incl_alias.c.sub_part)
                )

            q = session.query(
                    included_parts.c.sub_part,
                    func.sum(included_parts.c.quantity).
                        label('total_quantity')
                ).\
                group_by(included_parts.c.sub_part)

        .. seealso::

            :meth:`_expression.HasCTE.cte`