IHP Api Reference
Copyright(c) digitally induced GmbH 2020
Safe HaskellNone

IHP.QueryBuilder

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

Documentation

query :: forall model (table :: Symbol). (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 #

Instances

Instances details
Show (QueryBuilder table) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

showsPrec :: Int -> QueryBuilder table -> ShowS #

show :: QueryBuilder table -> String

showList :: [QueryBuilder table] -> ShowS #

Table (GetModelByTableName table) => Default (QueryBuilder table) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

def :: QueryBuilder table #

Eq (QueryBuilder table) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

(==) :: QueryBuilder table -> QueryBuilder table -> Bool #

(/=) :: QueryBuilder table -> QueryBuilder table -> Bool #

KnownSymbol table => ToHtml (QueryBuilder table) Source #

Display QueryBuilder's as their sql query inside HSX

Instance details

Defined in IHP.QueryBuilder

Methods

toHtml :: QueryBuilder table -> Html #

(model ~ GetModelByTableName table, KnownSymbol table) => Fetchable (QueryBuilder table) model Source # 
Instance details

Defined in IHP.Fetch

Associated Types

type FetchResult (QueryBuilder table) model 
Instance details

Defined in IHP.Fetch

type FetchResult (QueryBuilder table) model = [model]

Methods

fetch :: QueryBuilder table -> IO (FetchResult (QueryBuilder table) model) Source #

fetchOneOrNothing :: QueryBuilder table -> IO (Maybe model) Source #

fetchOne :: QueryBuilder table -> IO model Source #

type FetchResult (QueryBuilder table) model Source # 
Instance details

Defined in IHP.Fetch

type FetchResult (QueryBuilder table) model = [model]

newtype In a #

Constructors

In a 

Instances

Instances details
Functor In 
Instance details

Defined in Database.PostgreSQL.Simple.Types

Methods

fmap :: (a -> b) -> In a -> In b #

(<$) :: a -> In b -> In a #

Read a => Read (In a) 
Instance details

Defined in Database.PostgreSQL.Simple.Types

Methods

readsPrec :: Int -> ReadS (In a)

readList :: ReadS [In a]

readPrec :: ReadPrec (In a)

readListPrec :: ReadPrec [In a]

Show a => Show (In a) 
Instance details

Defined in Database.PostgreSQL.Simple.Types

Methods

showsPrec :: Int -> In a -> ShowS #

show :: In a -> String

showList :: [In a] -> ShowS #

Eq a => Eq (In a) 
Instance details

Defined in Database.PostgreSQL.Simple.Types

Methods

(==) :: In a -> In a -> Bool #

(/=) :: In a -> In a -> Bool #

Ord a => Ord (In a) 
Instance details

Defined in Database.PostgreSQL.Simple.Types

Methods

compare :: In a -> In a -> Ordering #

(<) :: In a -> In a -> Bool #

(<=) :: In a -> In a -> Bool #

(>) :: In a -> In a -> Bool #

(>=) :: In a -> In a -> Bool #

max :: In a -> In a -> In a #

min :: In a -> In a -> In a #

ToField a => ToField (In [a]) 
Instance details

Defined in Database.PostgreSQL.Simple.ToField

Methods

toField :: In [a] -> Action

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

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

queryUnion :: forall {k1} {k2} queryBuilderProvider (joinRegister :: k1) r (joinRegister' :: k2) (model :: Symbol). (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 = '..')

queryUnionList :: forall (table :: Symbol). (Table (GetModelByTableName table), KnownSymbol table, GetTableName (GetModelByTableName table) ~ table) => [QueryBuilder table] -> QueryBuilder table Source #

Like queryUnion, but applied on all the elements on the list

 action ProjectsAction = do
     let values :: [(ProjectType, Int)] = [(ProjectTypeOngoing, 3), (ProjectTypeNotStarted, 2)]

         valuePairToCondition :: (ProjectType, Int) -> QueryBuilder "projects"
         valuePairToCondition (projectType, participants) =
             query @Project
                 |> filterWhere (#projectType, projectType)
                 |> filterWhere (#participants, participants)

         theQuery = queryUnionList (map valuePairToCondition values)

     projects <- fetch theQuery
     render IndexView { .. }

queryOr :: forall {k1} {k2} {k3} queryBuilderProvider (joinRegister :: k1) queryBuilderProvider'' (joinRegister'' :: k2) queryBuilderProvider''' (joinRegister''' :: k3) (model :: Symbol). (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 :: Symbol) where Source #

Methods

defaultScope :: QueryBuilder table -> QueryBuilder table Source #

Instances

Instances details
DefaultScope table Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

defaultScope :: QueryBuilder table -> QueryBuilder table Source #

filterWhere :: forall {k} (name :: Symbol) (table :: Symbol) model value queryBuilderProvider (joinRegister :: k). (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 {k} (name :: Symbol) (table :: Symbol) model value queryBuilderProvider (joinRegister :: k). (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 :: Symbol) (table :: Symbol) 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 :: Symbol) (table :: Symbol) 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

filterWhereIdIn :: forall (table :: Symbol) model queryBuilderProvider joinRegister. (KnownSymbol table, Table model, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => [Id model] -> queryBuilderProvider table -> queryBuilderProvider table Source #

filterWhereNotIn :: forall {k} (name :: Symbol) (table :: Symbol) model value queryBuilderProvider (joinRegister :: k). (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 {k} (name :: Symbol) (table :: Symbol) model value queryBuilderProvider (joinRegister :: k). (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 {k} (name :: Symbol) (table :: Symbol) model value queryBuilderProvider (joinRegister :: k). (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 {k} (name :: Symbol) (table :: Symbol) model value queryBuilderProvider (joinRegister :: k). (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 {k} (name :: Symbol) (table :: Symbol) model value queryBuilderProvider (joinRegister :: k). (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 {k} model (name :: Symbol) (table :: Symbol) value queryBuilderProvider (joinRegister :: k) (table' :: Symbol). (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 {k} model (name :: Symbol) (table :: Symbol) value queryBuilderProvider (joinRegister :: k) (table' :: Symbol). (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 {k} model (name :: Symbol) (table :: Symbol) value queryBuilderProvider (joinRegister :: k) (table' :: Symbol). (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 {k} model (name :: Symbol) (table :: Symbol) value queryBuilderProvider (joinRegister :: k) (table' :: Symbol). (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 {k} model (name :: Symbol) (table :: Symbol) value queryBuilderProvider (joinRegister :: k) (table' :: Symbol). (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 filterWhereLike, 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 {k} model (table :: Symbol) (name :: Symbol) (table' :: Symbol) model' value queryBuilderProvider (joinRegister :: k). (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 {k} model (table :: Symbol) (name :: Symbol) value queryBuilderProvider (joinRegister :: k) (table' :: Symbol). (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 {k} model (table :: Symbol) (name :: Symbol) value queryBuilderProvider (joinRegister :: k) (table' :: Symbol). (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 :: forall {k} (table :: Symbol) (name :: Symbol) value queryBuilderProvider (joinRegister :: k). (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 :: forall {k} (table :: Symbol) (name :: Symbol) value queryBuilderProvider (joinRegister :: k). (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 {k} foreignModel baseModel (foreignTable :: Symbol) (baseTable :: Symbol) (name :: Symbol) value queryBuilderProvider (joinRegister :: k). (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

Instances details
EqOrIsOperator otherwise Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

toEqOrIsOperator :: otherwise -> FilterOperator Source #

EqOrIsOperator (Maybe something) Source # 
Instance details

Defined in IHP.QueryBuilder

filterWhereSql :: forall {k} (name :: Symbol) (table :: Symbol) model value queryBuilderProvider (joinRegister :: k). (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 :: Symbol) where Source #

Methods

filterWhereId :: Id' table -> QueryBuilder table -> QueryBuilder table Source #

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

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

toSQL :: forall {k} (table :: Symbol) queryBuilderProvider (joinRegister :: k). (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 {k} (table :: Symbol) queryBuilderProvider (joinRegister :: k). (KnownSymbol table, HasQueryBuilder queryBuilderProvider joinRegister) => queryBuilderProvider table -> SQLQuery Source #

data SQLQuery Source #

Instances

Instances details
Show SQLQuery Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

showsPrec :: Int -> SQLQuery -> ShowS #

show :: SQLQuery -> String

showList :: [SQLQuery] -> ShowS #

Eq SQLQuery Source # 
Instance details

Defined in IHP.QueryBuilder

SetField "selectFrom" SQLQuery ByteString Source # 
Instance details

Defined in IHP.QueryBuilder

SetField "distinctClause" SQLQuery (Maybe ByteString) Source # 
Instance details

Defined in IHP.QueryBuilder

SetField "distinctOnClause" SQLQuery (Maybe ByteString) Source # 
Instance details

Defined in IHP.QueryBuilder

SetField "limitClause" SQLQuery (Maybe ByteString) Source # 
Instance details

Defined in IHP.QueryBuilder

SetField "offsetClause" SQLQuery (Maybe ByteString) Source # 
Instance details

Defined in IHP.QueryBuilder

SetField "orderByClause" SQLQuery [OrderByClause] Source # 
Instance details

Defined in IHP.QueryBuilder

SetField "queryIndex" SQLQuery (Maybe ByteString) Source # 
Instance details

Defined in IHP.QueryBuilder

SetField "whereCondition" SQLQuery (Maybe Condition) Source # 
Instance details

Defined in IHP.QueryBuilder

data OrderByClause Source #

Instances

Instances details
FromJSON OrderByClause Source # 
Instance details

Defined in IHP.DataSync.DynamicQuery

Generic OrderByClause Source # 
Instance details

Defined in IHP.QueryBuilder

Associated Types

type Rep OrderByClause 
Instance details

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)))
Show OrderByClause Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

showsPrec :: Int -> OrderByClause -> ShowS #

show :: OrderByClause -> String

showList :: [OrderByClause] -> ShowS #

NFData OrderByClause Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

rnf :: OrderByClause -> () #

Eq OrderByClause Source # 
Instance details

Defined in IHP.QueryBuilder

SetField "orderByClause" SQLQuery [OrderByClause] Source # 
Instance details

Defined in IHP.QueryBuilder

type Rep OrderByClause Source # 
Instance details

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 {k} model' (table' :: Symbol) (name' :: Symbol) value' model (table :: Symbol) (name :: Symbol) value queryBuilderProvider (joinRegister :: k). (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 {k} model model' (name :: Symbol) (name' :: Symbol) value value' (table :: Symbol) (table' :: Symbol) (baseTable :: Symbol) baseModel queryBuilderProvider (joinRegister :: k). (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 :: Symbol -> Type) (joinRegister :: k) | queryBuilderProvider -> joinRegister Source #

Minimal complete definition

getQueryBuilder, injectQueryBuilder

Instances

Instances details
HasQueryBuilder NoJoinQueryBuilderWrapper NoJoins Source # 
Instance details

Defined in IHP.QueryBuilder

HasQueryBuilder (JoinQueryBuilderWrapper joinRegister) (joinRegister :: k) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

getQueryBuilder :: forall (table :: Symbol). JoinQueryBuilderWrapper joinRegister table -> QueryBuilder table Source #

injectQueryBuilder :: forall (table :: Symbol). QueryBuilder table -> JoinQueryBuilderWrapper joinRegister table Source #

getQueryIndex :: forall (table :: Symbol). JoinQueryBuilderWrapper joinRegister table -> Maybe ByteString

(KnownSymbol foreignTable, foreignModel ~ GetModelByTableName foreignTable, KnownSymbol indexColumn, HasField indexColumn foreignModel indexValue) => HasQueryBuilder (LabeledQueryBuilderWrapper foreignTable indexColumn indexValue) NoJoins Source # 
Instance details

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 Source #

getQueryIndex :: forall (table :: Symbol). LabeledQueryBuilderWrapper foreignTable indexColumn indexValue table -> Maybe ByteString

data JoinQueryBuilderWrapper (joinRegister :: k) (table :: Symbol) Source #

Instances

Instances details
HasQueryBuilder (JoinQueryBuilderWrapper joinRegister) (joinRegister :: k) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

getQueryBuilder :: forall (table :: Symbol). JoinQueryBuilderWrapper joinRegister table -> QueryBuilder table Source #

injectQueryBuilder :: forall (table :: Symbol). QueryBuilder table -> JoinQueryBuilderWrapper joinRegister table Source #

getQueryIndex :: forall (table :: Symbol). JoinQueryBuilderWrapper joinRegister table -> Maybe ByteString

(model ~ GetModelByTableName table, KnownSymbol table) => Fetchable (JoinQueryBuilderWrapper r table) model Source # 
Instance details

Defined in IHP.Fetch

Associated Types

type FetchResult (JoinQueryBuilderWrapper r table) model 
Instance details

Defined in IHP.Fetch

type FetchResult (JoinQueryBuilderWrapper r table) model = [model]
type FetchResult (JoinQueryBuilderWrapper r table) model Source # 
Instance details

Defined in IHP.Fetch

type FetchResult (JoinQueryBuilderWrapper r table) model = [model]

data NoJoinQueryBuilderWrapper (table :: Symbol) Source #

Instances

Instances details
HasQueryBuilder NoJoinQueryBuilderWrapper NoJoins Source # 
Instance details

Defined in IHP.QueryBuilder

(model ~ GetModelByTableName table, KnownSymbol table) => Fetchable (NoJoinQueryBuilderWrapper table) model Source # 
Instance details

Defined in IHP.Fetch

Associated Types

type FetchResult (NoJoinQueryBuilderWrapper table) model 
Instance details

Defined in IHP.Fetch

type FetchResult (NoJoinQueryBuilderWrapper table) model = [model]
type FetchResult (NoJoinQueryBuilderWrapper table) model Source # 
Instance details

Defined in IHP.Fetch

type FetchResult (NoJoinQueryBuilderWrapper table) model = [model]

data LabeledQueryBuilderWrapper (foreignTable :: k) (indexColumn :: k1) (indexValue :: k2) (table :: Symbol) Source #

Instances

Instances details
(KnownSymbol foreignTable, foreignModel ~ GetModelByTableName foreignTable, KnownSymbol indexColumn, HasField indexColumn foreignModel indexValue) => HasQueryBuilder (LabeledQueryBuilderWrapper foreignTable indexColumn indexValue) NoJoins Source # 
Instance details

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 Source #

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 # 
Instance details

Defined in IHP.Fetch

Associated Types

type FetchResult (LabeledQueryBuilderWrapper foreignTable columnName value table) model 
Instance details

Defined in IHP.Fetch

type FetchResult (LabeledQueryBuilderWrapper foreignTable columnName value table) model = [LabeledData value model]

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 # 
Instance details

Defined in IHP.Fetch

type FetchResult (LabeledQueryBuilderWrapper foreignTable columnName value table) model = [LabeledData value model]

getQueryBuilder :: forall (table :: Symbol). HasQueryBuilder queryBuilderProvider joinRegister => queryBuilderProvider table -> QueryBuilder table Source #

data NoJoins Source #

Instances

Instances details
HasQueryBuilder NoJoinQueryBuilderWrapper NoJoins Source # 
Instance details

Defined in IHP.QueryBuilder

(KnownSymbol foreignTable, foreignModel ~ GetModelByTableName foreignTable, KnownSymbol indexColumn, HasField indexColumn foreignModel indexValue) => HasQueryBuilder (LabeledQueryBuilderWrapper foreignTable indexColumn indexValue) NoJoins Source # 
Instance details

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 Source #

getQueryIndex :: forall (table :: Symbol). LabeledQueryBuilderWrapper foreignTable indexColumn indexValue table -> Maybe ByteString

data Condition Source #

Instances

Instances details
FromJSON Condition Source # 
Instance details

Defined in IHP.DataSync.DynamicQuery

Show Condition Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

showsPrec :: Int -> Condition -> ShowS #

show :: Condition -> String

showList :: [Condition] -> ShowS #

Eq Condition Source # 
Instance details

Defined in IHP.QueryBuilder

SetField "whereCondition" SQLQuery (Maybe Condition) Source # 
Instance details

Defined in IHP.QueryBuilder

data Join Source #

Instances

Instances details
FromJSON Join Source # 
Instance details

Defined in IHP.DataSync.DynamicQuery

Methods

parseJSON :: Value -> Parser Join #

parseJSONList :: Value -> Parser [Join] #

omittedField :: Maybe Join #

Show Join Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

showsPrec :: Int -> Join -> ShowS #

show :: Join -> String

showList :: [Join] -> ShowS #

Eq Join Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

(==) :: Join -> Join -> Bool #

(/=) :: Join -> Join -> Bool #

data OrderByDirection Source #

Constructors

Asc 
Desc 

Instances

Instances details
FromJSON OrderByDirection Source # 
Instance details

Defined in IHP.DataSync.DynamicQuery

Generic OrderByDirection Source # 
Instance details

Defined in IHP.QueryBuilder

Associated Types

type Rep OrderByDirection 
Instance details

Defined in IHP.QueryBuilder

type Rep OrderByDirection = D1 ('MetaData "OrderByDirection" "IHP.QueryBuilder" "main" 'False) (C1 ('MetaCons "Asc" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "Desc" 'PrefixI 'False) (U1 :: Type -> Type))
Show OrderByDirection Source # 
Instance details

Defined in IHP.QueryBuilder

NFData OrderByDirection Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

rnf :: OrderByDirection -> () #

Eq OrderByDirection Source # 
Instance details

Defined in IHP.QueryBuilder

type Rep OrderByDirection Source # 
Instance details

Defined in IHP.QueryBuilder

type Rep OrderByDirection = D1 ('MetaData "OrderByDirection" "IHP.QueryBuilder" "main" 'False) (C1 ('MetaCons "Asc" 'PrefixI 'False) (U1 :: Type -> Type) :+: C1 ('MetaCons "Desc" 'PrefixI 'False) (U1 :: Type -> Type))

injectQueryBuilder :: forall (table :: Symbol). HasQueryBuilder queryBuilderProvider joinRegister => QueryBuilder table -> queryBuilderProvider table Source #

data FilterOperator Source #

Constructors

EqOp
col = val
NotEqOp
col != val
InOp
col IN (set)
NotInOp
col NOT IN (set)
IsOp
col IS val
IsNotOp
col IS NOT val
LikeOp !MatchSensitivity
col LIKE val
NotLikeOp !MatchSensitivity
col NOT LIKE val
MatchesOp !MatchSensitivity
col ~ pattern
SqlOp

Used by filterWhereSql

Instances

Instances details
Show FilterOperator Source # 
Instance details

Defined in IHP.QueryBuilder

Eq FilterOperator Source # 
Instance details

Defined in IHP.QueryBuilder

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 query |> filterWhere (#userId, self.id) to all users by default.

This is needed to support syntax like this:

fetch user.posts
Instance details

Methods

(==) :: Builder -> Builder -> Bool #

(/=) :: Builder -> Builder -> Bool #

Eq Action Source #

Needed for the 'Eq QueryBuilder' instance

Instance details

Methods

(==) :: Action -> Action -> Bool #

(/=) :: Action -> Action -> Bool #