Common Table Expressions

Common table expressions (CTEs) can generally be seen as SQL views that you define on the fly.

A certain level of familiarity with SQL databases is helpful before you dive into this guide. The starting point is obviously the SQLite documentation. Many CTE tutorials exist online as well, including this good one.

In this guide, you will learn how to:

Define Common Table Expressions
Embed Common Table Expressions in Requests
Fetch Values From Common Table Expressions
Join CTEs with Associations to Common Table Expressions

Note: most code examples will be trivial, and not very "useful". This is because the goal of this guide is to stay focused on the GRDB support for CTEs. Rich setup would just be distracting. So bring your own good ideas with you!

Define Common Table Expressions

You will create a CommonTableExpression definition first. Choose a name, and a request that provides the content of the common table expression.

The CTE name is like a regular table name: pick one that does not conflict with the names of existing tables.

The CTE request can be provided as a query interface request:

// WITH playerName AS (SELECT name FROM player) ...
let playerNameCTE = CommonTableExpression(
    named: "playerName", 
    request: Player.select(Column("name")))

You can feed a CTE with raw SQL as well (second and third examples use SQL Interpolation):

let name = "O'Brien"

// WITH playerName AS (SELECT 'O''Brien') ...
let playerNameCTE = CommonTableExpression(
    named: "playerName",
    sql: "SELECT ?", arguments: [name])

// WITH playerName AS (SELECT 'O''Brien') ...
let playerNameCTE = CommonTableExpression(
    named: "playerName",
    literal: "SELECT \(name)")

// WITH playerName AS (SELECT 'O''Brien') ...
let request = SQLRequest("SELECT \(name)")
let playerNameCTE = CommonTableExpression(
    named: "playerName",
    request: request)

All CTEs can be provided with explicit column names:

// WITH pair(a, b) AS (SELECT 1, 2) ...
let pairCTE = CommonTableExpression(
    named: "pair", 
    columns: ["a", "b"], 
    sql: "SELECT 1, 2")

Recursive CTEs need the recursive flag. The example below selects all integers between 1 and 1000:

// WITH RECURSIVE counter(x) AS
//   (VALUES(1) UNION ALL SELECT x+1 FROM counter WHERE x<1000)
let counterCTE = CommonTableExpression(
    recursive: true,
    named: "counter",
    columns: ["x"],
    sql: """
        VALUES(1)
        UNION ALL
        SELECT x+1 FROM counter WHERE x<1000
        """)

Note: many recursive CTEs use the UNION ALL SQL operator. The query interface does not provide any Swift support for it, so you'll generally have to write SQL in your definitions of recursive CTEs.

Embed Common Table Expressions in Requests

A typical SQLite query that uses a common table expression first defines the CTE and then uses the CTE by mentioning its table name. We'll see below Swift apis that match those two steps.

We will use the (simple) query below as a target. It is the query we'll want to generate in this chapter. It defines a CTE, and uses it in a subquery:

WITH playerName AS (SELECT 'O''Brien')
SELECT * FROM player
WHERE name = (SELECT * FROM playerName)

We first build a CommonTableExpression:

let name = "O'Brien"
let playerNameCTE = CommonTableExpression(
    named: "playerName", 
    literal: "SELECT \(name)")

We can then embed the definition of the CTE in a query interface request by calling the with(_:) method:

// WITH playerName AS (SELECT 'O''Brien')
// SELECT * FROM player ...
let request = Player
    .with(playerNameCTE)...

And we can then filter the player table with a subquery:

// WITH playerName AS (SELECT 'O''Brien')
// SELECT * FROM player
// WHERE name = (SELECT * FROM playerName)
let request = Player
    .with(playerNameCTE)
    .filter(Column("name") == playerNameCTE.all())

Note: the with(_:) method can be called as many times as a there are common table expressions in your request.

Note: the with(_:) method can be called at any time, as all request methods: Player.with(...).filter(...).with(...).

Note: the with(_:) method replaces any previously embedded CTE that has the same table name. This allows you to embed the same CTE several times if you feel like it.

Note: the CommonTableExpression.all() method builds a regular query interface request for the content of the CTE (like SELECT * FROM <cte name>, not to be mismatched with the request that was used to define the CTE). You can filter this request, sort it, etc, like all query interface requests:
cte.all().select(...).filter(...).group(...).order(...)

Common table expressions can also be embedded in SQLRequest with SQL Interpolation:

// WITH playerName AS (SELECT 'O''Brien')
// SELECT * FROM player
// WHERE name = (SELECT * FROM playerName)
let request: SQLRequest<Player> = """
    WITH \(definitionFor: playerNameCTE)
    SELECT * FROM player
    WHERE name = (SELECT * FROM \(playerNameCTE))
    """

// WITH playerName AS (SELECT 'O''Brien')
// SELECT * FROM player
// WHERE name = (SELECT * FROM playerName)
let request: SQLRequest<Player> = """
    WITH \(definitionFor: playerNameCTE)
    SELECT * FROM player
    WHERE name = (\(playerNameCTE.all()))
    """

Common table expressions can also be used as subqueries, when you update or delete rows in the database:

// WITH playerName AS (SELECT 'O''Brien')
// UPDATE player SET name = (SELECT * FROM playerName)
try Player
    .with(playerNameCTE)
    .updateAll(db, Column("name").set(to: playerNameCTE.all()))
    
// WITH playerName AS (SELECT 'O''Brien')
// DELETE FROM player WHERE name = (SELECT * FROM playerName)
try Player
    .with(playerNameCTE)
    .filter(Column("name") == playerNameCTE.all())
    .deleteAll(db)

Fetch Values From Common Table Expressions

In the previous chapter, a common table expression was embedded as a subquery, with the CommonTableExpression.all() method.

cte.all() builds a regular query interface request that you can filter, sort, etc, like all query interface requests.

You can also fetch from cte.all(), as long as the request is given the definition of the CTE: cte.all().with(cte). In SQL, this would give: WITH cte AS (...) SELECT * FROM cte:

This request, of type QueryInterfaceRequest<Row>, can fetch raw database rows:

let cte = CommonTableExpression(...)
let request = cte.all().with(cte)
let rows = try request.fetchAll(db) // [Row]

In order to fetch something else, such as simple values, or custom records, you have two possible options:

Use the asRequest(of:) method:

let cte = CommonTableExpression(...)
let request = cte.all().with(cte).asRequest(of: Player.self)
//                               ~~~~~~~~~~~~~~~~~~~~~~~~~~~
let players = try request.fetchAll(db) // [Player]

Provide the fetched type to the cte itself:

let cte = CommonTableExpression<Player>(...)
//                             ~~~~~~~~
let request = cte.all().with(cte)
let players = try request.fetchAll(db) // [Player]

Associations to Common Table Expressions

GRDB associations define "to-one" and "to-many" relationships between two database tables. Here we will define associations between regular tables and common table expressions.

We recommend familiarity with the "joining methods", described in Joining And Prefetching Associated Records:

// SELECT parent.* FROM parent LEFT JOIN child ON ...
Parent.joining(optional: childAssociation)

// SELECT parent.* FROM parent JOIN child ON ...
Parent.joining(required: childAssociation)

// SELECT parent.*, child.* FROM parent LEFT JOIN child ON ...
Parent.including(optional: childAssociation)

// SELECT parent.*, child.* FROM parent JOIN child ON ...
Parent.including(required: childAssociation)

Note: common table expressions currently only define "to-one" associations, so the including(all:) joining method is unavailable.

CTE associations are generally built with the association(to:on:) method, which needs:

The two sides of the association: a CommonTableExpression instance, and another CTE or a type that conforms to the TableRecord protocol.
A function that returns the condition that joins the two sides of the association.

The condition function plays the same role as the foreign key that defines regular table associations such as BelongsTo or HasMany. It accepts two TableAlias, from which you can build a joining expression:

For example:

// An association from LeftRecord to rightCTE
let rightCTE = ...
let association = LeftRecord.association(
    to: rightCTE, 
    on: { left, right in
        left[Column("x")] == right[Column("y")]
    })

Now this association can be used with a joining method:

// WITH rightCTE AS (...)
// SELECT leftRecord.*, rightCTE.*
// FROM leftRecord
// JOIN rightCTE ON leftRecord.x = rightCTE.y
LeftRecord
    .with(rightCTE)
    .including(required: association)

CTE Association Example: a Chat App

As an example, let's build the classical main screen of a chat application: a list of all latest messages from all conversations.

The database schema of the chat app contains a chat and a message table. The application defines the following records:

struct Chat: Codable, FetchableRecord, PersistableRecord {
    var id: Int64
    ...
}

struct Message: Codable, FetchableRecord, PersistableRecord {
    var chatID: Int64
    var date: Date
    ...
}

To feed the main app screen, we want to fetch a list of ChatInfo records:

struct ChatInfo: Decodable, FetchableRecord {
    /// The chat
    var chat: Chat
    
    /// The latest chat message, if any
    var latestMessage: Message?
}

The SQL request that we want to run is below. It uses an SQLite-specific special processing of MAX() that helps the selection of latest messages from all chats:

WITH latestMessage AS
  (SELECT *, MAX(date) FROM message GROUP BY chatID)
SELECT chat.*, latestMessage.*
FROM chat
LEFT JOIN latestMessage ON chat.id = latestMessage.chatID
ORDER BY latestMessage.date DESC

We start by defining the CTE request, which loads the latest messages of all chats:

// SELECT *, MAX(date) FROM message GROUP BY chatID
let latestMessageRequest = Message
    .annotated(with: max(Column("date")))
    .group(Column("chatID"))

We can now define the CTE for the latest messages:

// WITH latestMessage AS
//   (SELECT *, MAX(date) FROM message GROUP BY chatID)
let latestMessageCTE = CommonTableExpression(
    named: "latestMessage",
    request: latestMessageRequest)

The association from a chat to its latest message follows:

// ... JOIN latestMessage ON chat.id = latestMessage.chatID
let latestMessage = Chat.association(
    to: latestMessageCTE,
    on: { chat, latestMessage in
        chat[Column("id")] == latestMessage[Column("chatID")]
    })
    .order(Column("date").desc)

The final request can now be defined:

// WITH latestMessage AS
//   (SELECT *, MAX(date) FROM message GROUP BY chatID)
// SELECT chat.*, latestMessage.*
// FROM chat
// LEFT JOIN latestMessage ON chat.id = latestMessage.chatID
// ORDER BY latestMessage.date DESC
let request = Chat
    .with(latestMessageCTE)
    .including(optional: latestMessage)
    .asRequest(of: ChatInfo.self)

And we can fetch the data that feeds our application screen:

let chatInfos: [ChatInfos] = try dbQueue.read { db in
    try request.fetchAll(db)
}

💡 Tip: the joining methods are generally type-safe: they won't allow you to join apples to oranges. This works when associations have a precise type. In this context, anonymous CommonTableExpression CTEs can work against type safety. When you want to define associations between several CTEs, and make sure the compiler will notice wrong uses of those associations, tag your common table expressions with an explicit type: CommonTableExpression<SomeType>.

To do so, you can use an existing record type, or an ad-hoc enum. For example:

enum CTE1 { }
let cte1 = CommonTableExpression<CTE1>(...)

enum CTE2 { }
let cte2 = CommonTableExpression<CTE2>(...)

let assoc1 = BaseRecord.association(to: cte1, on: ...)     // from BaseRecord to CTE1
let assoc2 = cte1.association(to: cte2, on: ...)           // from CTE1 to CTE2
let assoc3 = cte2.association(to: FarRecord.self, on: ...) // from CTE2 to FarRecord

// WITH ...
// SELECT base.* FROM base
// JOIN cte1 ON ...
// JOIN cte2 ON ...
// JOIN far ON ...
let request = BaseRecord
    .with(cte1).with(cte2)
    .joining(required: assoc1.                     // OK
        .joining(required: assoc2.                 // OK
            .joining(required: assoc3)))           // OK

// Compiler error
let request = BaseRecord
    .joining(required: assoc2)                     // Not OK
    .joining(required: assoc3)                     // Not OK

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

CommonTableExpressions.md

CommonTableExpressions.md

Common Table Expressions

Define Common Table Expressions

Embed Common Table Expressions in Requests

Fetch Values From Common Table Expressions

Associations to Common Table Expressions

CTE Association Example: a Chat App

Files

CommonTableExpressions.md

Latest commit

History

CommonTableExpressions.md

File metadata and controls

Common Table Expressions

Define Common Table Expressions

Embed Common Table Expressions in Requests

Fetch Values From Common Table Expressions

Associations to Common Table Expressions

CTE Association Example: a Chat App