Skip to content

WITH

Jump to bottom
go-jet edited this page Jan 23, 2022 · 7 revisions

Contents

WITH statements provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. More about WITH statements can be found here:
PostgreSQL - https://www.postgresql.org/docs/12/queries-with.html
MySQL - https://dev.mysql.com/doc/refman/8.0/en/with.html
MariaDB - https://mariadb.com/kb/en/with

How to write WITH statement?

  • CTE(Common Table Expression) defined in the WITH statement has to be declared before WITH statement.
  • CTE can be:
    • [posgres] defined as SELECT, INSERT, UPDATE or DELETE statement
    • [mysql, sqlite] defined only as SELECT statements, but the main statement can be either SELECT, UPDATE or DELETE statement
    • [mariadb] CTE and main statement can only consist of SELECT statements
  • CTE exported columns can be (for better readability) defined before WITH statement

PostgreSQL Example

// CTEs declarations
removeDiscontinuedOrders := CTE("remove_discontinued_orders")
updateDiscontinuedPrice := CTE("update_discontinued_price")
logDiscontinuedProducts := CTE("log_discontinued")

// CTE exported column. Can be used in other CTE(updateDiscontinuedPrice) or in the main CTE. 
discontinuedProductID := OrderDetails.ProductID.From(removeDiscontinuedOrders)

stmt := WITH(
    removeDiscontinuedOrders.AS(
        OrderDetails.DELETE().
        WHERE(OrderDetails.ProductID.IN(
                SELECT(Products.ProductID).
                FROM(Products).
                WHERE(Products.Discontinued.EQ(Int(1)))
            ),
        ).RETURNING(OrderDetails.ProductID),
    ),
    updateDiscontinuedPrice.AS(
        Products.UPDATE().
        SET(
            Products.UnitPrice.SET(Float(0.0)),
        ).
        WHERE(Products.ProductID.IN(removeDiscontinuedOrders.SELECT(discontinuedProductID))).
        RETURNING(Products.AllColumns),
    ),
    logDiscontinuedProducts.AS(
        ProductLogs.INSERT(ProductLogs.AllColumns).
        QUERY(
               SELECT(updateDiscontinuedPrice.AllColumns()).
               FROM(updateDiscontinuedPrice),
        ).
        RETURNING(ProductLogs.AllColumns),
    ),
)(
    SELECT(logDiscontinuedProducts.AllColumns()).
    FROM(logDiscontinuedProducts),
)

var resp []model.ProductLogs
err = stmt.Query(tx, &resp)

MySQL and MariaDB Example

salesRep := CTE("sales_rep")
customerSalesRep := CTE("customer_sales_rep")

salesRepStaffID := Staff.StaffID.From(salesRep)
salesRepFullName := StringColumn("sales_rep_full_name").From(salesRep)

stmt := WITH(
    salesRep.AS(
        SELECT(
            Staff.StaffID,
            Staff.FirstName.CONCAT(Staff.LastName).AS(salesRepFullName.Name()),
        ).FROM(Staff),
    ),
    customerSalesRep.AS(
        SELECT(
            Customer.FirstName.CONCAT(Customer.LastName).AS("customer_name"),
            salesRepFullName,
        ).FROM(
            salesRep.
                INNER_JOIN(Store, Store.ManagerStaffID.EQ(salesRepStaffID)).
                INNER_JOIN(Customer, Customer.StoreID.EQ(Store.StoreID)),
        ),
    ),
)(
    SELECT(customerSalesRep.AllColumns()).
    FROM(customerSalesRep),
)

var dest []model.Customer
err := stmt.Query(db, &dest)

How to write WITH RECURSIVE statements?

Using RECURSIVE, a WITH query can refer to its own output. The general form of a recursive WITH query is always a non-recursive term, then UNION (or UNION ALL), then a recursive term, where only the recursive term can contain a reference to the query's own output.

subordinates := CTE("subordinates")

stmt := WITH_RECURSIVE(
	subordinates.AS(
		SELECT(
			Employees.AllColumns,
		).FROM(
			Employees,
		).WHERE(
			Employees.EmployeeID.EQ(Int(2)),
		).UNION(
			SELECT(
				Employees.AllColumns,
			).FROM(
				Employees.
					INNER_JOIN(subordinates, Employees.EmployeeID.From(subordinates).EQ(Employees.ReportsTo)),
			),
		),
	),
)(
	SELECT(
		subordinates.AllColumns(),
	).FROM(
		subordinates,
	),
)
Clone this wiki locally