DerivableRequest

Creates a request which returns distinct rows.

// SELECT DISTINCT * FROM player
var request = Player.all()
request = request.distinct()

// SELECT DISTINCT name FROM player
var request = Player.select(Column("name"))
request = request.distinct()

Returns a request which embeds the common table expression.

If a common table expression with the same table name had already been embedded, it is replaced by the new one.

For example, you can build a request that fetches all chats with their latest message:

let latestMessageRequest = Message
    .annotated(with: max(Column("date")))
    .group(Column("chatID"))

let latestMessageCTE = CommonTableExpression(
    named: "latestMessage",
    request: latestMessageRequest)

let latestMessage = Chat.association(
    to: latestMessageCTE,
    on: { chat, latestMessage in
        chat[Column("id")] == latestMessage[Column("chatID")]
    })

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

Creates a request which appends aggregates to the current selection.

// SELECT team.*, COUNT(DISTINCT player.id) AS playerCount
// FROM team LEFT JOIN player ...
var request = Team.all()
request = request.annotated(with: Team.players.count)

Creates a request which appends aggregates to the current selection.

// SELECT team.*, COUNT(DISTINCT player.id) AS playerCount
// FROM team LEFT JOIN player ...
var request = team.all()
request = request.annotated(with: [Team.players.count])

Creates a request which appends the provided aggregate predicate to the eventual set of already applied predicates.

// SELECT team.*
// FROM team LEFT JOIN player ...
// HAVING COUNT(DISTINCT player.id) = 0
var request = Team.all()
request = request.having(Team.players.isEmpty)