Copyright | (c) digitally induced GmbH 2020 |
---|---|
Safe Haskell | Safe-Inferred |
IHP.QueryBuilder
Contents
Description
QueryBuilder is mainly used for doing simple SELECT
sql queries. It allows dynamic
creation of sql queries in a type safe way.
For more complex sql queries, use sqlQuery
.
Synopsis
- query :: forall model table. (table ~ GetTableName model, Table model) => DefaultScope table => QueryBuilder table
- data QueryBuilder (table :: Symbol)
- = NewQueryBuilder {
- selectFrom :: !ByteString
- columns :: ![ByteString]
- | DistinctQueryBuilder {
- queryBuilder :: !(QueryBuilder table)
- | DistinctOnQueryBuilder {
- queryBuilder :: !(QueryBuilder table)
- distinctOnColumn :: !ByteString
- | FilterByQueryBuilder {
- queryBuilder :: !(QueryBuilder table)
- queryFilter :: !(ByteString, FilterOperator, Action)
- applyLeft :: !(Maybe ByteString)
- applyRight :: !(Maybe ByteString)
- | OrderByQueryBuilder {
- queryBuilder :: !(QueryBuilder table)
- queryOrderByClause :: !OrderByClause
- | LimitQueryBuilder {
- queryBuilder :: !(QueryBuilder table)
- queryLimit :: !Int
- | OffsetQueryBuilder {
- queryBuilder :: !(QueryBuilder table)
- queryOffset :: !Int
- | UnionQueryBuilder {
- firstQueryBuilder :: !(QueryBuilder table)
- secondQueryBuilder :: !(QueryBuilder table)
- | JoinQueryBuilder {
- queryBuilder :: !(QueryBuilder table)
- joinData :: Join
- = NewQueryBuilder {
- newtype In a = In a
- orderBy :: (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
- orderByAsc :: forall name model table value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
- orderByDesc :: forall name model table value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
- limit :: HasQueryBuilder queryBuilderProvider joinRegister => Int -> queryBuilderProvider model -> queryBuilderProvider model
- offset :: HasQueryBuilder queryBuilderProvider joinRegister => Int -> queryBuilderProvider model -> queryBuilderProvider model
- queryUnion :: (HasQueryBuilder queryBuilderProvider joinRegister, HasQueryBuilder r joinRegister') => queryBuilderProvider model -> r model -> NoJoinQueryBuilderWrapper model
- queryOr :: (HasQueryBuilder queryBuilderProvider joinRegister, HasQueryBuilder queryBuilderProvider'' joinRegister'', HasQueryBuilder queryBuilderProvider''' joinRegister''') => (queryBuilderProvider model -> queryBuilderProvider''' model) -> (queryBuilderProvider model -> queryBuilderProvider'' model) -> queryBuilderProvider model -> queryBuilderProvider model
- class DefaultScope table where
- defaultScope :: QueryBuilder table -> QueryBuilder table
- filterWhere :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
- filterWhereCaseInsensitive :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
- filterWhereNot :: forall name table model value. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, Table model) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder table
- filterWhereIn :: forall name table model value queryBuilderProvider (joinRegister :: *). (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, EqOrIsOperator value, Table model) => (Proxy name, [value]) -> queryBuilderProvider table -> queryBuilderProvider table
- filterWhereNotIn :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, EqOrIsOperator value) => (Proxy name, [value]) -> queryBuilderProvider table -> queryBuilderProvider table
- filterWhereLike :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
- filterWhereILike :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
- filterWhereMatches :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
- filterWhereIMatches :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table
- filterWhereJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
- filterWhereNotJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
- filterWhereInJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, [value]) -> queryBuilderProvider table' -> queryBuilderProvider table'
- filterWhereNotInJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, [value]) -> queryBuilderProvider table' -> queryBuilderProvider table'
- filterWhereLikeJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol name, KnownSymbol table, table ~ GetTableName model, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
- filterWhereILikeJoinedTable :: forall model table name table' model' value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, model' ~ GetModelByTableName table', HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
- filterWhereMatchesJoinedTable :: forall model table name value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
- filterWhereIMatchesJoinedTable :: forall model table name value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table'
- filterWherePast :: (KnownSymbol table, KnownSymbol name, ToField value, HasField name (GetModelByTableName table) value, HasQueryBuilder queryBuilderProvider joinRegister, Table (GetModelByTableName table)) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
- filterWhereFuture :: (KnownSymbol table, KnownSymbol name, ToField value, HasField name (GetModelByTableName table) value, HasQueryBuilder queryBuilderProvider joinRegister, Table (GetModelByTableName table)) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
- labelResults :: forall foreignModel baseModel foreignTable baseTable name value queryBuilderProvider joinRegister. (KnownSymbol foreignTable, KnownSymbol baseTable, foreignTable ~ GetTableName foreignModel, baseModel ~ GetModelByTableName baseTable, HasField name foreignModel value, HasQueryBuilder queryBuilderProvider joinRegister, KnownSymbol name, IsJoined foreignModel joinRegister) => Proxy name -> queryBuilderProvider baseTable -> LabeledQueryBuilderWrapper foreignTable name value baseTable
- class EqOrIsOperator value
- filterWhereSql :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, ByteString) -> queryBuilderProvider table -> queryBuilderProvider table
- class FilterPrimaryKey table where
- filterWhereId :: Id' table -> QueryBuilder table -> QueryBuilder table
- distinctOn :: forall name model value table queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table
- distinct :: HasQueryBuilder queryBuilderProvider joinRegister => queryBuilderProvider table -> queryBuilderProvider table
- toSQL :: (KnownSymbol table, HasQueryBuilder queryBuilderProvider joinRegister) => queryBuilderProvider table -> (ByteString, [Action])
- toSQL' :: SQLQuery -> (ByteString, [Action])
- buildQuery :: forall table queryBuilderProvider joinRegister. (KnownSymbol table, HasQueryBuilder queryBuilderProvider joinRegister) => queryBuilderProvider table -> SQLQuery
- data SQLQuery = SQLQuery {
- queryIndex :: !(Maybe ByteString)
- selectFrom :: !ByteString
- distinctClause :: !(Maybe ByteString)
- distinctOnClause :: !(Maybe ByteString)
- whereCondition :: !(Maybe Condition)
- joins :: ![Join]
- orderByClause :: ![OrderByClause]
- limitClause :: !(Maybe ByteString)
- offsetClause :: !(Maybe ByteString)
- columns :: ![ByteString]
- data OrderByClause = OrderByClause {}
- innerJoin :: forall model' table' name' value' model table name value queryBuilderProvider joinRegister. (KnownSymbol name, KnownSymbol table, HasField name model value, KnownSymbol name', KnownSymbol table', HasQueryBuilder queryBuilderProvider joinRegister, ModelList joinRegister, HasField name' model' value', value ~ value', model ~ GetModelByTableName table, table' ~ GetTableName model') => (Proxy name, Proxy name') -> queryBuilderProvider table -> JoinQueryBuilderWrapper (ConsModelList model' joinRegister) table
- innerJoinThirdTable :: forall model model' name name' value value' table table' baseTable baseModel queryBuilderProvider joinRegister. (KnownSymbol name, KnownSymbol table, HasField name model value, KnownSymbol name', KnownSymbol table', HasQueryBuilder queryBuilderProvider joinRegister, ModelList joinRegister, HasField name' model' value', value ~ value', table ~ GetTableName model, table' ~ GetTableName model', baseModel ~ GetModelByTableName baseTable) => (Proxy name, Proxy name') -> queryBuilderProvider baseTable -> JoinQueryBuilderWrapper (ConsModelList model joinRegister) baseTable
- class HasQueryBuilder queryBuilderProvider joinRegister | queryBuilderProvider -> joinRegister
- data JoinQueryBuilderWrapper joinRegister table
- data NoJoinQueryBuilderWrapper table
- data LabeledQueryBuilderWrapper foreignTable indexColumn indexValue table
- getQueryBuilder :: HasQueryBuilder queryBuilderProvider joinRegister => queryBuilderProvider table -> QueryBuilder table
- data NoJoins
- data Condition
- = VarCondition !ByteString !Action
- | OrCondition !Condition !Condition
- | AndCondition !Condition !Condition
- data Join = Join {}
- data OrderByDirection
Documentation
query :: forall model table. (table ~ GetTableName model, Table model) => DefaultScope table => QueryBuilder table Source #
Represent's a SELECT * FROM ..
query. It's the starting point to build a query.
Used together with the other functions to compose a sql query.
Example:
toSQL (query @User) -- Returns: ("SELECT id, firstname, lastname FROM users", [])
Example: Fetching all users
allUsers <- query @User |> fetch -- Runs a 'SELECT * FROM users' query
You can use it together with filterWhere
:
activeUsers :: [User] <- query @User |> filterWhere (#active, True) |> fetch
data QueryBuilder (table :: Symbol) Source #
Constructors
NewQueryBuilder | |
Fields
| |
DistinctQueryBuilder | |
Fields
| |
DistinctOnQueryBuilder | |
Fields
| |
FilterByQueryBuilder | |
Fields
| |
OrderByQueryBuilder | |
Fields
| |
LimitQueryBuilder | |
Fields
| |
OffsetQueryBuilder | |
Fields
| |
UnionQueryBuilder | |
Fields
| |
JoinQueryBuilder | |
Fields
|
Instances
Constructors
In a |
orderBy :: (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 name model table value queryBuilderProvider joinRegister. (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 name model table value queryBuilderProvider joinRegister. (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
limit :: 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 :: 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
queryUnion :: (HasQueryBuilder queryBuilderProvider joinRegister, HasQueryBuilder r joinRegister') => queryBuilderProvider model -> r model -> NoJoinQueryBuilderWrapper model Source #
Merges the results of two query builders.
Take a look at ‘queryOr' as well, as this might be a bit shorter.
Example: Return all pages owned by the user or owned by the users team.
let userPages = query @Page |> filterWhere (#ownerId, currentUserId) let teamPages = query @Page |> filterWhere (#teamId, currentTeamId) pages <- queryUnion userPages teamPages |> fetch -- (SELECT * FROM pages WHERE owner_id = '..') UNION (SELECT * FROM pages WHERE team_id = '..')
queryOr :: (HasQueryBuilder queryBuilderProvider joinRegister, HasQueryBuilder queryBuilderProvider'' joinRegister'', HasQueryBuilder queryBuilderProvider''' joinRegister''') => (queryBuilderProvider model -> queryBuilderProvider''' model) -> (queryBuilderProvider model -> queryBuilderProvider'' model) -> queryBuilderProvider model -> queryBuilderProvider model Source #
Adds an a OR b
condition
Example: Return all pages owned by the user or public.
query @Page |> queryOr (filterWhere (#createdBy, currentUserId)) (filterWhere (#public, True)) |> fetch -- SELECT * FROM pages WHERE created_by = '..' OR public = True
class DefaultScope table where Source #
Methods
defaultScope :: QueryBuilder table -> QueryBuilder table Source #
Instances
DefaultScope table Source # | |
Defined in IHP.QueryBuilder Methods defaultScope :: QueryBuilder table -> QueryBuilder table Source # |
filterWhere :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table Source #
Adds a simple WHERE x = y
condition to the query.
Example: Only show projects where active
is True
.
activeProjects <- query @Project |> filterWhere (#active, True) |> fetch -- SELECT * FROM projects WHERE active = True
Example: Find book with title Learn you a Haskell
.
book <- query @Book |> filterWhere (#title, "Learn you a Haskell") |> fetchOne -- SELECT * FROM books WHERE name = 'Learn you a Haskell' LIMIT 1
Example: Find active projects owned by the current user.
projects <- query @Project |> filterWhere (#active, True) |> filterWhere (#currentUserId, currentUserId) |> fetch -- SELECT * FROM projects WHERE active = true AND current_user_id = '..'
For dynamic conditions (e.g. involving NOW()
), see filterWhereSql
.
For WHERE x IN (a, b, c)
conditions, take a look at filterWhereIn
and filterWhereNotIn
.
For WHERE x LIKE a
or WHERE x ~ a
conditions, see filterWhereLike
and filterWhereMatches
respectively.
For case-insensitive versions of these operators, see filterWhereILike
and filterWhereIMatches
.
When your condition is too complex, use a raw sql query with sqlQuery
.
filterWhereCaseInsensitive :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table Source #
Adds a WHERE LOWER(x) = LOWER(y)
condition to the query.
Example: Get a user by an email address, ignoring case
user <- query @User |> filterWhereCaseInsensitive (#email, "marc@digitallyinduced.com") |> fetchOne -- SELECT * FROM users WHERE LOWER(email) = 'marc@digitallyinduced.com'
For high performance it's best to have an index for LOWER(field)
in your Schema.sql
>>>
CREATE UNIQUE INDEX users_email_index ON users ((LOWER(email)));
filterWhereNot :: forall name table model value. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, Table model) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder table Source #
Like filterWhere
but negates the condition.
Example: Only show projects created by other users.
activeProjects <- query @Project |> filterWhereNot (#userId, currentUserId) |> fetch -- SELECT * FROM projects WHERE user_id != '23d5ea33-b28e-4f0a-99b3-77a3564a2546'
filterWhereIn :: forall name table model value queryBuilderProvider (joinRegister :: *). (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, EqOrIsOperator value, Table model) => (Proxy name, [value]) -> queryBuilderProvider table -> queryBuilderProvider table Source #
Adds a WHERE x IN (y)
condition to the query.
Example: Only show projects where status
is Draft
or Active
.
visibleProjects <- query @Project |> filterWhereIn (#status, [Draft, Active]) |> fetch -- SELECT * FROM projects WHERE status IN ('draft', 'active')
For negation use filterWhereNotIn
filterWhereNotIn :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, EqOrIsOperator value) => (Proxy name, [value]) -> queryBuilderProvider table -> queryBuilderProvider table Source #
Adds a WHERE x NOT IN (y)
condition to the query.
Example: Only show projects where status
is not Archived
visibleProjects <- query @Project |> filterWhereNotIn (#status, [Archived]) |> fetch -- SELECT * FROM projects WHERE status NOT IN ('archived')
The inclusive version of this function is called filterWhereIn
.
filterWhereLike :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table Source #
Adds a WHERE x LIKE y
condition to the query.
Example: Find titles matching search term.
articles <- query @Article |> filterWhereLike (#title, "%" <> searchTerm <> "%") |> fetch -- SELECT * FROM articles WHERE title LIKE '%..%'
filterWhereILike :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table Source #
Adds a WHERE x ILIKE y
condition to the query. Case-insensitive version of filterWhereLike
.
Example: Find titles matching search term.
articles <- query @Article |> filterWhereILike (#title, "%" <> searchTerm <> "%") |> fetch -- SELECT * FROM articles WHERE title ILIKE '%..%'
filterWhereMatches :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table Source #
Adds a WHERE x ~ y
condition to the query.
Example: Find names with titles in front.
articles <- query @User |> filterWhereMatches (#name, "^(M(rs|r|iss)|Dr|Sir). ") |> fetch -- SELECT * FROM articles WHERE title ~ '^(M(rs|r|iss)|Dr|Sir). '
filterWhereIMatches :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table -> queryBuilderProvider table Source #
Adds a WHERE x ~* y
condition to the query. Case-insensitive version of filterWhereMatches
.
filterWhereJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table' Source #
Like filterWhere
, but takes a type argument specifying the table which holds the column that is to be compared. The column must have been joined before using innerJoin
or innerJoinThirdTable
. Example:
Example: get posts by user Tom.
tomPosts <- query @Post |> innerJoin @User (#createdBy, #id) |> filterWhereJoinedTable @User (#name, "Tom" :: Text) |> fetch -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name = 'Tom'
filterWhereNotJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table' Source #
Like filterWhereNotJoinedTable
but negates the condition.
Example: Only show projects not created by user Tom.
tomPosts <- query @Post |> innerJoin @User (#createdBy, #id) |> filterWhereNotJoinedTable @User (#name, "Tom" :: Text) |> fetch -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name = 'Tom'
filterWhereInJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, [value]) -> queryBuilderProvider table' -> queryBuilderProvider table' Source #
Like filterWhereIn
, but takes a type argument specifying the table which holds the column that is compared. The table needs to have been joined before using innerJoin
or innerJoinThirdTable
.
Example: get posts by Tom and Tim.
tomOrTimPosts <- query @Post |> innerJoin @User (#createdBy, #id) |> filterWhereInJoinedTable @User (#name, ["Tom","Tim"]) |> fetch -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name IN ('Tom', 'Tim')
filterWhereNotInJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, [value]) -> queryBuilderProvider table' -> queryBuilderProvider table' Source #
Like filterWhereNotIn
, but takes a type argument specifying the table which holds the column that is compared. The table needs to have been joined before using innerJoin
or innerJoinThirdTable
.
Example: get posts by users not named Tom or Tim.
notTomOrTimPosts <- query @Post |> innerJoin @User (#createdBy, #id) |> filterWhereNotInJoinedTable @User (#name, ["Tom","Tim"]) |> fetch -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name NOT IN ('Tom', 'Tim')
filterWhereLikeJoinedTable :: forall model name table value queryBuilderProvider joinRegister table'. (KnownSymbol name, KnownSymbol table, table ~ GetTableName model, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table' Source #
Like filterWhereLik
e, but takes a type argument specifying the table which holds the column that is compared. The table needs to have been joined before using innerJoin
or innerJoinThirdTable
.
Example: Serach for Posts by users whose name contains "olaf" (case insensitive)
olafPosts <- query @Post |> innerJoin @User (#createdBy, #id) |> filterWhereLikeJoinedTable @User (#name, "%Olaf%") |> fetch -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name LIKE '%Olaf%'
filterWhereILikeJoinedTable :: forall model table name table' model' value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, model' ~ GetModelByTableName table', HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table' Source #
Like filterWhereILike
; case-insensitive version of filterWhereLikeJoinedTable, takes a type argument specifying the table which holds the column that is compared. The table needs to have been joined before using innerJoin
or innerJoinThirdTable
.
Example: Serach for Posts by users whose name contains "olaf" (case insensitive)
olafPosts <- query @Post
|> innerJoin User (#createdBy, #id)
|> filterWhereILikeJoinedTable
User (#name, "%Olaf%")
> -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name ILIKE '%Olaf%'
filterWhereMatchesJoinedTable :: forall model table name value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table' Source #
Adds a WHERE x ~ y
condition to the query, where the column x is held by a joined table.
Example: Find Posts by people with names with titles in front.
articles <- query @Post |> innerJoin @User (#createdBy, #id) |> filterWhereMatchesJoinedTable (#title, "^(M(rs|r|iss|s)|Dr|Sir). ") |> fetch -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.title ~ '^(M(rs|r|iss|s)|Dr|Sir). '
filterWhereIMatchesJoinedTable :: forall model table name value queryBuilderProvider joinRegister table'. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister, IsJoined model joinRegister, Table model) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table' Source #
Case-insensitive version of filterWhereMatchesJoinedTable
filterWherePast :: (KnownSymbol table, KnownSymbol name, ToField value, HasField name (GetModelByTableName table) value, HasQueryBuilder queryBuilderProvider joinRegister, Table (GetModelByTableName table)) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table Source #
Filter all rows by whether a field is in the past, determined by comparing 'NOW()' to the field's value.
Opposite of filterWhereFuture
Example: Fetch all posts scheduled for the past.
publicPosts <- query @Post |> filterWherePast #scheduledAt |> fetch -- SELECT * FROM posts WHERE scheduled_at <= NOW()
filterWhereFuture :: (KnownSymbol table, KnownSymbol name, ToField value, HasField name (GetModelByTableName table) value, HasQueryBuilder queryBuilderProvider joinRegister, Table (GetModelByTableName table)) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table Source #
Filter all rows by whether a field is in the future, determined by comparing 'NOW()' to the field's value.
Opposite of filterWherePast
Example: Fetch all posts scheduled for the future.
hiddenPosts <- query @Post |> filterWhereFuture #scheduledAt |> fetch -- SELECT * FROM posts WHERE scheduled_at > NOW()
labelResults :: forall foreignModel baseModel foreignTable baseTable name value queryBuilderProvider joinRegister. (KnownSymbol foreignTable, KnownSymbol baseTable, foreignTable ~ GetTableName foreignModel, baseModel ~ GetModelByTableName baseTable, HasField name foreignModel value, HasQueryBuilder queryBuilderProvider joinRegister, KnownSymbol name, IsJoined foreignModel joinRegister) => Proxy name -> queryBuilderProvider baseTable -> LabeledQueryBuilderWrapper foreignTable name value baseTable Source #
Index the values from a table with values of a field from a table joined by innerJoin
or innerJoinThirdTable
. Useful to get, e.g., the tags to a set of posts in such a way that the assignment of tags to posts is preserved.
Example: Fetch a list of all comments, each paired with the id of the post it belongs to.
labeledTags <- query @Tag |> innerJoin @Tagging (#id, #tagId) |> innerJoinThirdTable @Post @Tagging (#id, #postId) |> labelResults @Post #id |> fetch -- SELECT posts.id, tags.* FROM comments INNER JOIN taggings ON tags.id = taggings.tagId INNER JOIN posts ON posts.id = taggings.postId
labeledTags is then a list of type [LabeledData
(Id' "posts") Tag] such that "LabeledData postId tag" is contained in that list if "tag" is a tag of the post with id postId.
class EqOrIsOperator value Source #
Helper to deal with some_field IS NULL
and some_field = 'some value'
Minimal complete definition
toEqOrIsOperator
Instances
EqOrIsOperator otherwise Source # | |
Defined in IHP.QueryBuilder Methods toEqOrIsOperator :: otherwise -> FilterOperator | |
EqOrIsOperator (Maybe something) Source # | |
Defined in IHP.QueryBuilder Methods toEqOrIsOperator :: Maybe something -> FilterOperator |
filterWhereSql :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol table, KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister, Table model) => (Proxy name, ByteString) -> queryBuilderProvider table -> queryBuilderProvider table Source #
Allows to add a custom raw sql where condition
If your query cannot be represented with filterWhereSql
, take a look at sqlQuery
.
Example: Fetching all projects created in the last 24 hours.
latestProjects <- query @Project |> filterWhereSql (#startedAt, "< current_timestamp - interval '1 day'") |> fetch -- SELECT * FROM projects WHERE started_at < current_timestamp - interval '1 day'
class FilterPrimaryKey table where Source #
Methods
filterWhereId :: Id' table -> QueryBuilder table -> QueryBuilder table Source #
distinctOn :: forall name model value table queryBuilderProvider joinRegister. (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
distinct :: 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
toSQL :: (KnownSymbol table, HasQueryBuilder queryBuilderProvider joinRegister) => queryBuilderProvider table -> (ByteString, [Action]) Source #
Transforms a query
User |> ..
expression into a SQL Query. Returns a tuple with the sql query template and it's placeholder values.
Example: Get the sql query that is represented by a QueryBuilder
>>>
let postsQuery = query @Post |> filterWhere (#public, True)
>>>
toSQL postsQuery
("SELECT posts.* FROM posts WHERE public = ?", [Plain "true"])
toSQL' :: SQLQuery -> (ByteString, [Action]) Source #
buildQuery :: forall table queryBuilderProvider joinRegister. (KnownSymbol table, HasQueryBuilder queryBuilderProvider joinRegister) => queryBuilderProvider table -> SQLQuery Source #
Constructors
SQLQuery | |
Fields
|
Instances
Show SQLQuery Source # | |
Eq SQLQuery Source # | |
SetField "selectFrom" SQLQuery ByteString Source # | |
Defined in IHP.QueryBuilder | |
SetField "distinctClause" SQLQuery (Maybe ByteString) Source # | |
Defined in IHP.QueryBuilder | |
SetField "distinctOnClause" SQLQuery (Maybe ByteString) Source # | |
Defined in IHP.QueryBuilder | |
SetField "limitClause" SQLQuery (Maybe ByteString) Source # | |
Defined in IHP.QueryBuilder | |
SetField "offsetClause" SQLQuery (Maybe ByteString) Source # | |
Defined in IHP.QueryBuilder | |
SetField "orderByClause" SQLQuery [OrderByClause] Source # | |
Defined in IHP.QueryBuilder | |
SetField "queryIndex" SQLQuery (Maybe ByteString) Source # | |
Defined in IHP.QueryBuilder | |
SetField "whereCondition" SQLQuery (Maybe Condition) Source # | |
data OrderByClause Source #
Constructors
OrderByClause | |
Fields |
Instances
FromJSON OrderByClause Source # | |
Defined in IHP.DataSync.DynamicQuery Methods parseJSON :: Value -> Parser OrderByClause # parseJSONList :: Value -> Parser [OrderByClause] # | |
Generic OrderByClause Source # | |
Defined in IHP.QueryBuilder Associated Types type Rep OrderByClause :: Type -> Type | |
Show OrderByClause Source # | |
Defined in IHP.QueryBuilder Methods showsPrec :: Int -> OrderByClause -> ShowS # show :: OrderByClause -> String showList :: [OrderByClause] -> ShowS # | |
NFData OrderByClause Source # | |
Defined in IHP.QueryBuilder Methods rnf :: OrderByClause -> () # | |
Eq OrderByClause Source # | |
Defined in IHP.QueryBuilder Methods (==) :: OrderByClause -> OrderByClause -> Bool # (/=) :: OrderByClause -> OrderByClause -> Bool # | |
SetField "orderByClause" SQLQuery [OrderByClause] Source # | |
Defined in IHP.QueryBuilder | |
type Rep OrderByClause Source # | |
Defined in IHP.QueryBuilder type Rep OrderByClause = D1 ('MetaData "OrderByClause" "IHP.QueryBuilder" "main" 'False) (C1 ('MetaCons "OrderByClause" 'PrefixI 'True) (S1 ('MetaSel ('Just "orderByColumn") 'NoSourceUnpackedness 'SourceStrict 'DecidedStrict) (Rec0 ByteString) :*: S1 ('MetaSel ('Just "orderByDirection") 'NoSourceUnpackedness 'SourceStrict 'DecidedStrict) (Rec0 OrderByDirection))) |
innerJoin :: forall model' table' name' value' model table name value queryBuilderProvider joinRegister. (KnownSymbol name, KnownSymbol table, HasField name model value, KnownSymbol name', KnownSymbol table', HasQueryBuilder queryBuilderProvider joinRegister, ModelList joinRegister, HasField name' model' value', value ~ value', model ~ GetModelByTableName table, table' ~ GetTableName model') => (Proxy name, Proxy name') -> queryBuilderProvider table -> JoinQueryBuilderWrapper (ConsModelList model' joinRegister) table Source #
Joins a table to an existing QueryBuilder (or something holding a QueryBuilder) on the specified columns. Example:
> query Posts
> |> innerJoin
Users (#author, #id)
> -- SELECT users.* FROM users INNER JOIN posts ON users.id = posts.author ...
innerJoinThirdTable :: forall model model' name name' value value' table table' baseTable baseModel queryBuilderProvider joinRegister. (KnownSymbol name, KnownSymbol table, HasField name model value, KnownSymbol name', KnownSymbol table', HasQueryBuilder queryBuilderProvider joinRegister, ModelList joinRegister, HasField name' model' value', value ~ value', table ~ GetTableName model, table' ~ GetTableName model', baseModel ~ GetModelByTableName baseTable) => (Proxy name, Proxy name') -> queryBuilderProvider baseTable -> JoinQueryBuilderWrapper (ConsModelList model joinRegister) baseTable Source #
Joins a table on a column held by a previously joined table. Example:
> query Posts
> |> innerJoin
Users (#author, #id)
> |> innerJoinThirdTable City
Users (#id, #homeTown)
> -- SELECT posts.* FROM posts INNER JOIN users ON posts.author = users.id INNER JOIN cities ON user.home_town = cities.id
class HasQueryBuilder queryBuilderProvider joinRegister | queryBuilderProvider -> joinRegister Source #
Minimal complete definition
getQueryBuilder, injectQueryBuilder
Instances
data JoinQueryBuilderWrapper joinRegister table Source #
Instances
data NoJoinQueryBuilderWrapper table Source #
Instances
HasQueryBuilder NoJoinQueryBuilderWrapper NoJoins Source # | |
Defined in IHP.QueryBuilder Methods getQueryBuilder :: forall (table :: Symbol). NoJoinQueryBuilderWrapper table -> QueryBuilder table Source # injectQueryBuilder :: forall (table :: Symbol). QueryBuilder table -> NoJoinQueryBuilderWrapper table getQueryIndex :: forall (table :: Symbol). NoJoinQueryBuilderWrapper table -> Maybe ByteString | |
(model ~ GetModelByTableName table, KnownSymbol table) => Fetchable (NoJoinQueryBuilderWrapper table) model Source # | |
Defined in IHP.Fetch Associated Types type FetchResult (NoJoinQueryBuilderWrapper table) model Source # Methods fetch :: NoJoinQueryBuilderWrapper table -> IO (FetchResult (NoJoinQueryBuilderWrapper table) model) Source # fetchOneOrNothing :: NoJoinQueryBuilderWrapper table -> IO (Maybe model) Source # fetchOne :: NoJoinQueryBuilderWrapper table -> IO model Source # | |
type FetchResult (NoJoinQueryBuilderWrapper table) model Source # | |
Defined in IHP.Fetch |
data LabeledQueryBuilderWrapper foreignTable indexColumn indexValue table Source #
Instances
(KnownSymbol foreignTable, foreignModel ~ GetModelByTableName foreignTable, KnownSymbol indexColumn, HasField indexColumn foreignModel indexValue) => HasQueryBuilder (LabeledQueryBuilderWrapper foreignTable indexColumn indexValue) NoJoins Source # | |
Defined in IHP.QueryBuilder Methods getQueryBuilder :: forall (table :: Symbol). LabeledQueryBuilderWrapper foreignTable indexColumn indexValue table -> QueryBuilder table Source # injectQueryBuilder :: forall (table :: Symbol). QueryBuilder table -> LabeledQueryBuilderWrapper foreignTable indexColumn indexValue table getQueryIndex :: forall (table :: Symbol). LabeledQueryBuilderWrapper foreignTable indexColumn indexValue table -> Maybe ByteString | |
(model ~ GetModelByTableName table, KnownSymbol table, FromField value, KnownSymbol foreignTable, foreignModel ~ GetModelByTableName foreignTable, KnownSymbol columnName, HasField columnName foreignModel value, HasQueryBuilder (LabeledQueryBuilderWrapper foreignTable columnName value) NoJoins) => Fetchable (LabeledQueryBuilderWrapper foreignTable columnName value table) model Source # | |
Defined in IHP.Fetch Associated Types type FetchResult (LabeledQueryBuilderWrapper foreignTable columnName value table) model Source # Methods fetch :: LabeledQueryBuilderWrapper foreignTable columnName value table -> IO (FetchResult (LabeledQueryBuilderWrapper foreignTable columnName value table) model) Source # fetchOneOrNothing :: LabeledQueryBuilderWrapper foreignTable columnName value table -> IO (Maybe model) Source # fetchOne :: LabeledQueryBuilderWrapper foreignTable columnName value table -> IO model Source # | |
type FetchResult (LabeledQueryBuilderWrapper foreignTable columnName value table) model Source # | |
Defined in IHP.Fetch type FetchResult (LabeledQueryBuilderWrapper foreignTable columnName value table) model = [LabeledData value model] |
getQueryBuilder :: HasQueryBuilder queryBuilderProvider joinRegister => queryBuilderProvider table -> QueryBuilder table Source #
Instances
Constructors
VarCondition !ByteString !Action | |
OrCondition !Condition !Condition | |
AndCondition !Condition !Condition |
Constructors
Join | |
Fields |
data OrderByDirection Source #
Instances
Orphan instances
Eq Builder Source # | Need for the 'Eq QueryBuilder' instance You likely wonder: Why do we need the 'Eq SQLQuery' instance if this causes so much trouble? This has to do with how has-many and belongs-to relations are models by the SchemaCompiler E.g. given a table users and a table posts. Each Post belongs to a user. The schema compiler will
add a field 'posts :: QueryBuilder "posts"' with the default value This is needed to support syntax like this: fetch user.posts |
Eq Action Source # | Needed for the 'Eq QueryBuilder' instance |