Method add_cte

lib/sqlalchemy/sql/selectable.py:2547–2652

r"""Add one or more :class:`_sql.CTE` constructs to this statement. This method will associate the given :class:`_sql.CTE` constructs with the parent statement such that they will each be unconditionally rendered in the WITH clause of the final statement, even if not

(self, *ctes: CTE, nest_here: bool = False)

    @_generative
    def add_cte(self, *ctes: CTE, nest_here: bool = False) -> Self:
        r"""Add one or more :class:`_sql.CTE` constructs to this statement.

        This method will associate the given :class:`_sql.CTE` constructs with
        the parent statement such that they will each be unconditionally
        rendered in the WITH clause of the final statement, even if not
        referenced elsewhere within the statement or any sub-selects.

        The optional :paramref:`.HasCTE.add_cte.nest_here` parameter when set
        to True will have the effect that each given :class:`_sql.CTE` will
        render in a WITH clause rendered directly along with this statement,
        rather than being moved to the top of the ultimate rendered statement,
        even if this statement is rendered as a subquery within a larger
        statement.

        This method has two general uses. One is to embed CTE statements that
        serve some purpose without being referenced explicitly, such as the use
        case of embedding a DML statement such as an INSERT or UPDATE as a CTE
        inline with a primary statement that may draw from its results
        indirectly.  The other is to provide control over the exact placement
        of a particular series of CTE constructs that should remain rendered
        directly in terms of a particular statement that may be nested in a
        larger statement.

        E.g.::

            from sqlalchemy import table, column, select

            t = table("t", column("c1"), column("c2"))

            ins = t.insert().values({"c1": "x", "c2": "y"}).cte()

            stmt = select(t).add_cte(ins)

        Would render:

        .. sourcecode:: sql

            WITH anon_1 AS (
                INSERT INTO t (c1, c2) VALUES (:param_1, :param_2)
            )
            SELECT t.c1, t.c2
            FROM t

        Above, the "anon_1" CTE is not referenced in the SELECT
        statement, however still accomplishes the task of running an INSERT
        statement.

        Similarly in a DML-related context, using the PostgreSQL
        :class:`_postgresql.Insert` construct to generate an "upsert"::

            from sqlalchemy import table, column
            from sqlalchemy.dialects.postgresql import insert

            t = table("t", column("c1"), column("c2"))

            delete_statement_cte = t.delete().where(t.c.c1 < 1).cte("deletions")