Method cte

lib/sqlalchemy/orm/query.py:674–735

r"""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. H

(
        self,
        name: Optional[str] = None,
        recursive: bool = False,
        nesting: bool = False,
    )

        return stmt.subquery(name=name)

    def cte(
        self,
        name: Optional[str] = None,
        recursive: bool = False,
        nesting: bool = False,
    ) -> CTE:
        r"""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/current/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:`_sql.Select.cte` - v2 equivalent method.

        """  # noqa: E501
        return (