ihp-1.4.0: Haskell Web Framework
Copyright(c) digitally induced GmbH 2020
Safe HaskellNone
LanguageGHC2021

IHP.QueryBuilder.Order

Description

This module provides functions for ordering, limiting, and deduplicating query results.

Synopsis

Documentation

orderBy :: forall {k} (table :: Symbol) (name :: Symbol) model value queryBuilderProvider (joinRegister :: k). (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table Source #

Alias for orderByAsc

orderByAsc :: forall {k} (name :: Symbol) model (table :: Symbol) value queryBuilderProvider (joinRegister :: k). (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table Source #

Adds an ORDER BY .. ASC to your query.

Use orderByDesc for descending order.

Example: Fetch the 10 oldest books.

query @Book
    |> orderBy #createdAt -- >     |> limit 10
    |> fetch
-- SELECT * FROM books LIMIT 10 ORDER BY created_at ASC

orderByDesc :: forall {k} (name :: Symbol) model (table :: Symbol) value queryBuilderProvider (joinRegister :: k). (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table Source #

Adds an ORDER BY .. DESC to your query.

Use orderBy for ascending order.

Example: Fetch the 10 newest projects (ordered by creation time).

query @Project
    |> orderByDesc #createdAt
    |> limit 10
    |> fetch
-- SELECT * FROM projects LIMIT 10 ORDER BY created_at DESC

orderByJoinedTable :: forall {k1} model (name :: Symbol) (table :: Symbol) value queryBuilderProvider (joinRegister :: k1) (table' :: Symbol). (KnownSymbol table, KnownSymbol name, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => Proxy name -> queryBuilderProvider table' -> queryBuilderProvider table' Source #

Alias for orderByAscJoinedTable

orderByAscJoinedTable :: forall {k1} model (name :: Symbol) (table :: Symbol) value queryBuilderProvider (joinRegister :: k1) (table' :: Symbol). (KnownSymbol table, KnownSymbol name, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => Proxy name -> queryBuilderProvider table' -> queryBuilderProvider table' Source #

Adds an ORDER BY .. ASC on a joined table column to your query.

Use orderByDescJoinedTable for descending order.

Example: Order joined User records by username ascending.

query @Project
    |> innerJoin @User (#id, #projectId)
    |> orderByAscJoinedTable #username
    |> fetch
-- SELECT ... FROM projects
-- INNER JOIN users ON projects.id = users.project_id
-- ORDER BY users.username ASC

orderByDescJoinedTable :: forall {k1} model (name :: Symbol) (table :: Symbol) value queryBuilderProvider (joinRegister :: k1) (table' :: Symbol). (KnownSymbol table, KnownSymbol name, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => Proxy name -> queryBuilderProvider table' -> queryBuilderProvider table' Source #

Adds an ORDER BY .. DESC on a joined table column to your query.

Use orderByAscJoinedTable for ascending order.

Example: Order joined User records by username descending.

query @Project
    |> innerJoin @User (#id, #projectId)
    |> orderByDescJoinedTable #username
    |> fetch
-- SELECT ... FROM projects
-- INNER JOIN users ON projects.id = users.project_id
-- ORDER BY users.username DESC

limit :: forall {k} queryBuilderProvider (joinRegister :: k) (model :: Symbol). HasQueryBuilder queryBuilderProvider joinRegister => Int -> queryBuilderProvider model -> queryBuilderProvider model Source #

Adds an LIMIT .. to your query.

Example: Fetch 10 posts

query @Post
    |> limit 10
    |> fetch
-- SELECT * FROM posts LIMIT 10

offset :: forall {k} queryBuilderProvider (joinRegister :: k) (model :: Symbol). HasQueryBuilder queryBuilderProvider joinRegister => Int -> queryBuilderProvider model -> queryBuilderProvider model Source #

Adds an OFFSET .. to your query. Most often used together with LIMIT...

Example: Fetch posts 10-20

query @Post
    |> limit 10
    |> offset 10
    |> fetch
-- SELECT * FROM posts LIMIT 10 OFFSET 10

distinct :: forall {k} queryBuilderProvider (joinRegister :: k) (table :: Symbol). HasQueryBuilder queryBuilderProvider joinRegister => queryBuilderProvider table -> queryBuilderProvider table Source #

Adds a DISTINCT to your query.

Use distinct to remove all duplicate rows from the result

Example: Fetch distinct books

query @Book
    |> distinct
    |> fetch
-- SELECT DISTINCT * FROM books

distinctOn :: forall {k} (name :: Symbol) model value (table :: Symbol) queryBuilderProvider (joinRegister :: k). (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table Source #

Adds an @DISTINCT ON .. to your query.

Use distinctOn to return a single row for each distinct value provided.

Example: Fetch one book for each categoryId field

query @Book
    |> distinctOn #categoryId
    |> fetch
-- SELECT DISTINCT ON (category_id) * FROM books