cake/combined

A DSL to build combined queries, such as:

UNION
UNION ALL
EXCEPT
EXCEPT ALL
INTERSECT
INTERSECT ALL

Aliases

import cake/combined as c
import cake/select as s
import cake/where as w

Query Lifecycle

flowchart LR
    A[s.new + query builders] --> B{set operation}
    B -->|union| C[c.union / c.unions]
    B -->|union_all| D[c.union_all / c.unions_all]
    B -->|except| E[c.except / c.excepts]
    B -->|except_all| F[c.except_all / c.excepts_all]
    B -->|intersect| G[c.intersect / c.intersects]
    B -->|intersect_all| H[c.intersect_all / c.intersects_all]
    C --> I[limit / offset / order_by]
    D --> I
    E --> I
    F --> I
    G --> I
    H --> I
    I --> J[c.epilog / c.comment]
    J --> K[c.to_query]

Set Operations

All set operations take at least two Select queries (converted via s.to_query) and return a Combined value. The _all variants accept two queries; the plural variants accept a list of additional queries.

`union(a, b) -> Combined` / `unions(a, b, rest) -> Combined`

UNION — distinct rows from both queries.

let q1 =
  s.new()
  |> s.from_table("users")
  |> s.col("name")
  |> s.to_query

let q2 =
  s.new()
  |> s.from_table("customers")
  |> s.col("name")
  |> s.to_query

c.union(q1, q2)
// SELECT name FROM users UNION SELECT name FROM customers

`union_all(a, b) -> Combined` / `unions_all(a, b, rest) -> Combined`

UNION ALL — all rows from both queries (no deduplication).

c.union_all(q1, q2)
// SELECT name FROM users UNION ALL SELECT name FROM customers

`except(a, b) -> Combined` / `excepts(a, b, rest) -> Combined`

EXCEPT — rows in the first query that do not appear in the second.

c.except(q1, q2)
// SELECT name FROM users EXCEPT SELECT name FROM customers

`except_all(a, b) -> Combined` / `excepts_all(a, b, rest) -> Combined`

EXCEPT ALL — rows in the first query that do not appear in the second, including duplicates.

Not supported by 🪶 SQLite.

c.except_all(q1, q2)
// SELECT name FROM users EXCEPT ALL SELECT name FROM customers

`intersect(a, b) -> Combined` / `intersects(a, b, rest) -> Combined`

INTERSECT — rows that appear in both queries.

c.intersect(q1, q2)
// SELECT name FROM users INTERSECT SELECT name FROM customers

`intersect_all(a, b) -> Combined` / `intersects_all(a, b, rest) -> Combined`

INTERSECT ALL — rows that appear in both queries, including duplicates.

Not supported by 🪶 SQLite.

c.intersect_all(q1, q2)
// SELECT name FROM users INTERSECT ALL SELECT name FROM customers

Helper

`get_queries(combined) -> List(Select)`

Extracts the original Select queries from a Combined value.

c.get_queries(combined_query)

LIMIT & OFFSET

Applied to the result of the combined query (not to individual sub-queries).

Function	Effect
`limit(query, n)`	Set LIMIT
`no_limit(query)`	Remove LIMIT
`get_limit(query)`	Get current LIMIT
`offset(query, n)`	Set OFFSET
`no_offset(query)`	Remove OFFSET
`get_offset(query)`	Get current OFFSET

c.union(q1, q2)
|> c.limit(10)
|> c.offset(20)

ORDER BY

Sorts the combined result set.

Direction

Constructor	Description
`c.Asc`	Ascending
`c.Desc`	Descending

Appending

Function	Notes
`order_by_asc(query, col)`	Append ASC
`order_by_asc_nulls_first(query, col)`	ASC NULLS FIRST
`order_by_asc_nulls_last(query, col)`	ASC NULLS LAST
`order_by_desc(query, col)`	Append DESC
`order_by_desc_nulls_first(query, col)`	DESC NULLS FIRST
`order_by_desc_nulls_last(query, col)`	DESC NULLS LAST
`order_by(query, col, direction)`	Custom direction

Replacing

Function	Notes
`replace_order_by_asc(query, col)`	Replace all with ASC
`replace_order_by_asc_nulls_first(query, col)`	Replace with ASC NULLS FIRST
`replace_order_by_asc_nulls_last(query, col)`	Replace with ASC NULLS LAST
`replace_order_by_desc(query, col)`	Replace all with DESC
`replace_order_by_desc_nulls_first(query, col)`	Replace with DESC NULLS FIRST
`replace_order_by_desc_nulls_last(query, col)`	Replace with DESC NULLS LAST
`replace_order_by(query, col, direction)`	Replace with custom direction

Removal / retrieval

Function	Effect
`no_order_by(query)`	Remove ORDER BY
`get_order_by(query)`	Get current ORDER BY

Note: NULLS FIRST / NULLS LAST are not supported out of the box by 🦭 MariaDB or 🐬 MySQL.

c.union(q1, q2)
|> c.order_by_desc("name")
// SELECT ... UNION SELECT ... ORDER BY name DESC

Epilog and Comment

An epilog is appended verbatim to the end of the generated SQL. A comment is placed at the very end as a SQL -- comment.

Function	Effect
`epilog(query, text)`	Append epilog
`no_epilog(query)`	Remove epilog
`get_epilog(query)`	Get current epilog
`comment(query, text)`	Append comment
`no_comment(query)`	Remove comment
`get_comment(query)`	Get current comment

c.union(q1, q2)
|> c.epilog("FOR UPDATE")
|> c.comment("fetching locked rows")
// SELECT ... UNION SELECT ... FOR UPDATE -- fetching locked rows

Converting to a Query

`to_query(combined) -> ReadQuery`

Converts a Combined into a ReadQuery suitable for passing to an adapter.

c.union(q1, q2)
|> c.limit(10)
|> c.to_query

Full Example

import cake/combined as c
import cake/select as s
import cake/where as w

let active_users =
  s.new()
  |> s.from_table("users")
  |> s.select_cols(["id", "name", "active"])
  |> s.where(w.eq(w.col("active"), w.bool(True)))
  |> s.to_query

let active_customers =
  s.new()
  |> s.from_table("customers")
  |> s.select_cols(["id", "name", "active"])
  |> s.where(w.eq(w.col("active"), w.bool(True)))
  |> s.to_query

c.unions(active_users, active_customers, [
  s.new()
  |> s.from_table("admins")
  |> s.select_cols(["id", "name", "active"])
  |> s.where(w.eq(w.col("active"), w.bool(True)))
  |> s.to_query,
])
|> c.order_by_asc("name")
|> c.limit(50)
|> c.to_query
// (SELECT id, name, active FROM users WHERE active = $1)
// UNION
// (SELECT id, name, active FROM customers WHERE active = $2)
// UNION
// (SELECT id, name, active FROM admins WHERE active = $3)
// ORDER BY name ASC
// LIMIT 50

Types

Combined

</>

pub type Combined =
  @internal Combined

Comment

</>

pub type Comment =
  @internal Comment

Direction

</>

Defines the direction of an OrderBy.

pub type Direction {
  Asc
  Desc
}

Constructors

```
Asc
```
```
Desc
```

Epilog

</>

pub type Epilog =
  @internal Epilog

Limit

</>

pub type Limit =
  @internal Limit

Offset

</>

pub type Offset =
  @internal Offset

OrderBy

</>

pub type OrderBy =
  @internal OrderBy

OrderByDirection

</>

pub type OrderByDirection =
  @internal OrderByDirection

ReadQuery

</>

pub type ReadQuery =
  @internal ReadQuery

Select

</>

pub type Select =
  @internal Select

Values

comment

</>

pub fn comment(
  query query: Combined,
  comment comment: String,
) -> Combined

Appends a Comment to the Combined read_query.

epilog

</>

pub fn epilog(
  query query: Combined,
  epilog epilog: String,
) -> Combined

Appends an Epilog to the Combined read_query.

except

</>

pub fn except(
  query_a query_a: Select,
  query_b query_b: Select,
) -> Combined

Creates an EXCEPT query out of two queries as a Combined ReadQuery.

except_all

</>

pub fn except_all(
  query_a query_a: Select,
  query_b query_b: Select,
) -> Combined

Creates an EXCEPT ALL query out of two queries as a Combined ReadQuery.

NOTICE: Not supported by 🪶SQLite.

excepts

</>

pub fn excepts(
  query_a query_a: Select,
  query_b query_b: Select,
  more_queries more_queries: List(Select),
) -> Combined

Creates an EXCEPT query out of two or more queries as a Combined ReadQuery.

excepts_all

</>

pub fn excepts_all(
  query_a query_a: Select,
  query_b query_b: Select,
  more_queries more_queries: List(Select),
) -> Combined

Creates an EXCEPT ALL query out of two or more queries as a Combined ReadQuery.

NOTICE: Not supported by 🪶SQLite.

get_comment

</>

pub fn get_comment(query query: Combined) -> Comment

Gets the Comment from the Combined read_query.

get_epilog

</>

pub fn get_epilog(query query: Combined) -> Epilog

Gets the Epilog from the Combined read_query.

get_limit

</>

pub fn get_limit(query query: Combined) -> Limit

Gets Limit in the Combined ReadQuery.

get_offset

</>

pub fn get_offset(query query: Combined) -> Offset

Gets Offset in the Combined ReadQuery.

get_order_by

</>

pub fn get_order_by(query query: Combined) -> OrderBy

Gets the OrderBy from the Combined read_query.

get_queries

</>

pub fn get_queries(combined combined: Combined) -> List(Select)

Gets the queries from a Combined ReadQuery.

intersect

</>

pub fn intersect(
  query_a query_a: Select,
  query_b query_b: Select,
) -> Combined

Creates an INTERSECT query out of two queries as a Combined ReadQuery.

intersect_all

</>

pub fn intersect_all(
  query_a query_a: Select,
  query_b query_b: Select,
) -> Combined

Creates an INTERSECT ALL query out of two queries as a Combined ReadQuery.

NOTICE: Not supported by 🪶SQLite.

intersects

</>

pub fn intersects(
  query_a query_a: Select,
  query_b query_b: Select,
  more_queries more_queries: List(Select),
) -> Combined

Creates an INTERSECT query out of two or more queries as a Combined ReadQuery.

intersects_all

</>

pub fn intersects_all(
  query_a query_a: Select,
  query_b query_b: Select,
  more_queries more_queries: List(Select),
) -> Combined

Creates an INTERSECT ALL query out of two or more queries as a Combined ReadQuery.

NOTICE: Not supported by 🪶SQLite.

limit

</>

pub fn limit(query query: Combined, limit limit: Int) -> Combined

Sets a Limit in the Combined ReadQuery.

no_comment

</>

pub fn no_comment(query query: Combined) -> Combined

Removes the Comment from the Combined read_query.

no_epilog

</>

pub fn no_epilog(query query: Combined) -> Combined

Removes the Epilog from the Combined read_query.

no_limit

</>

pub fn no_limit(query query: Combined) -> Combined

Removes Limit from the Combined ReadQuery.

no_offset

</>

pub fn no_offset(query query: Combined) -> Combined

Removes Offset from the Combined ReadQuery.

no_order_by

</>

pub fn no_order_by(query query: Combined) -> Combined

Removes the OrderBy from the Combined read_query.

offset

</>

pub fn offset(
  query query: Combined,
  offset offset: Int,
) -> Combined

Sets an Offset in the Combined ReadQuery.

order_by

</>

pub fn order_by(
  query query: Combined,
  order_by order_by: String,
  direction direction: Direction,
) -> Combined

Creates or appends an OrderBy for a column with a direction.

The direction can either ASC or DESC.

order_by_asc

</>

pub fn order_by_asc(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Creates or appends an ascending OrderBy.

order_by_asc_nulls_first

</>

pub fn order_by_asc_nulls_first(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Creates or appends an ascending OrderBy with NULLS FIRST.

NOTICE: 🦭MariaDB and 🐬MySQL do not support NULLS FIRST out of the box.

order_by_asc_nulls_last

</>

pub fn order_by_asc_nulls_last(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Creates or appends an ascending OrderBy with NULLS LAST.

NOTICE: 🦭MariaDB and 🐬MySQL do not support NULLS LAST out of the box.

order_by_desc

</>

pub fn order_by_desc(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Creates or appends a descending OrderBy.

order_by_desc_nulls_first

</>

pub fn order_by_desc_nulls_first(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Creates or appends a descending order with NULLS FIRST.

NOTICE: 🦭MariaDB and 🐬MySQL do not support NULLS FIRST out of the box.

order_by_desc_nulls_last

</>

pub fn order_by_desc_nulls_last(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Creates or appends a descending OrderBy with NULLS LAST.

NOTICE: 🦭MariaDB and 🐬MySQL do not support NULLS LAST out of the box.

replace_order_by

</>

pub fn replace_order_by(
  query query: Combined,
  order_by order_by: String,
  direction direction: Direction,
) -> Combined

Replaces the OrderBy with a column with a direction.

replace_order_by_asc

</>

pub fn replace_order_by_asc(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Replaces the OrderBy with a single ascending OrderBy.

replace_order_by_asc_nulls_first

</>

pub fn replace_order_by_asc_nulls_first(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Replaces the OrderBy with a single ascending OrderBy with NULLS FIRST.

NOTICE: 🦭MariaDB and 🐬MySQL do not support NULLS FIRST out of the box.

replace_order_by_asc_nulls_last

</>

pub fn replace_order_by_asc_nulls_last(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Replaces the OrderBy with a single ascending OrderBy with NULLS LAST.

NOTICE: 🦭MariaDB and 🐬MySQL do not support NULLS LAST out of the box.

replace_order_by_desc

</>

pub fn replace_order_by_desc(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Replaces the OrderBy with a single descending order.

replace_order_by_desc_nulls_first

</>

pub fn replace_order_by_desc_nulls_first(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Replaces the OrderBy with a single descending order with NULLS FIRST.

NOTICE: 🦭MariaDB and 🐬MySQL do not support NULLS FIRST out of the box.

replace_order_by_desc_nulls_last

</>

pub fn replace_order_by_desc_nulls_last(
  query query: Combined,
  order_by order_by: String,
) -> Combined

Replaces the OrderBy with a single descending OrderBy with NULLS LAST.

NOTICE: 🦭MariaDB and 🐬MySQL do not support NULLS LAST out of the box.

to_query

</>

pub fn to_query(combined combined: Combined) -> ReadQuery

Creates a ReadQuery from a Combined ReadQuery.

union

</>

pub fn union(
  query_a query_a: Select,
  query_b query_b: Select,
) -> Combined

Creates a UNION query out of two queries as a Combined ReadQuery.

union_all

</>

pub fn union_all(
  query_a query_a: Select,
  query_b query_b: Select,
) -> Combined

Creates a UNION ALL query out of two queries as a Combined ReadQuery.

unions

</>

pub fn unions(
  query_a query_a: Select,
  query_b query_b: Select,
  more_queries more_queries: List(Select),
) -> Combined

Creates a UNION query out of two or more queries as a Combined ReadQuery.

unions_all

</>

pub fn unions_all(
  query_a query_a: Select,
  query_b query_b: Select,
  more_queries more_queries: List(Select),
) -> Combined

Creates a UNION ALL query out of two or more queries as a Combined ReadQuery.

Aliases

Query Lifecycle

Set Operations

union(a, b) -> Combined / unions(a, b, rest) -> Combined

union_all(a, b) -> Combined / unions_all(a, b, rest) -> Combined

except(a, b) -> Combined / excepts(a, b, rest) -> Combined

except_all(a, b) -> Combined / excepts_all(a, b, rest) -> Combined

intersect(a, b) -> Combined / intersects(a, b, rest) -> Combined

intersect_all(a, b) -> Combined / intersects_all(a, b, rest) -> Combined

Helper

get_queries(combined) -> List(Select)

LIMIT & OFFSET

ORDER BY

Direction

Appending

Replacing

Removal / retrieval

Epilog and Comment

Converting to a Query

to_query(combined) -> ReadQuery

Full Example

Constructors

`union(a, b) -> Combined` / `unions(a, b, rest) -> Combined`

`union_all(a, b) -> Combined` / `unions_all(a, b, rest) -> Combined`

`except(a, b) -> Combined` / `excepts(a, b, rest) -> Combined`

`except_all(a, b) -> Combined` / `excepts_all(a, b, rest) -> Combined`

`intersect(a, b) -> Combined` / `intersects(a, b, rest) -> Combined`

`intersect_all(a, b) -> Combined` / `intersects_all(a, b, rest) -> Combined`

`get_queries(combined) -> List(Select)`

`to_query(combined) -> ReadQuery`