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. table ~ GetTableName 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
Eq (QueryBuilder table) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

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

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

Show (QueryBuilder table) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

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

show :: QueryBuilder table -> String

showList :: [QueryBuilder table] -> ShowS #

Default (QueryBuilder table) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

def :: QueryBuilder table #

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

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

Defined in IHP.Fetch

Associated Types

type FetchResult (QueryBuilder table) model Source #

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 #

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 #

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 #

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

Defined in Database.PostgreSQL.Simple.ToField

Methods

toField :: In [a] -> Action

orderBy :: (KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => Proxy name -> queryBuilderProvider table -> queryBuilderProvider table Source #

Alias for orderByAsc

orderByAsc :: forall name model table value queryBuilderProvider joinRegister. (KnownSymbol name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => 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 name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => 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
    |> orderBy #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 -> NoJoinQueryBuilderWrapper 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

Instances details
DefaultScope table Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

defaultScope :: QueryBuilder table -> QueryBuilder table Source #

filterWhere :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => (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 name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => (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 name, ToField value, HasField name model value, EqOrIsOperator value, model ~ GetModelByTableName table) => (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 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 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 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 name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => (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 name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => (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 name, ToField value, HasField name model value, table ~ GetTableName model, HasQueryBuilder queryBuilderProvider joinRegister) => (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) => (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) => (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) => (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) => (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) => (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) => (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 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) => (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 (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) => (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) => (Proxy name, value) -> queryBuilderProvider table' -> queryBuilderProvider table' Source #

Case-insensitive version of filterWhereMatchesJoinedTable

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

Instances details
EqOrIsOperator otherwise Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

toEqOrIsOperator :: otherwise -> FilterOperator

EqOrIsOperator (Maybe something) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

toEqOrIsOperator :: Maybe something -> FilterOperator

filterWhereSql :: forall name table model value queryBuilderProvider joinRegister. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => (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 name, HasField name model value, model ~ GetModelByTableName table, HasQueryBuilder queryBuilderProvider joinRegister) => 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 #

data SQLQuery Source #

Instances

Instances details
Eq SQLQuery Source # 
Instance details

Defined in IHP.QueryBuilder

Show SQLQuery Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

showsPrec :: Int -> SQLQuery -> ShowS #

show :: SQLQuery -> String

showList :: [SQLQuery] -> ShowS #

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

Defined in IHP.QueryBuilder

Show OrderByClause Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

showsPrec :: Int -> OrderByClause -> ShowS #

show :: OrderByClause -> String

showList :: [OrderByClause] -> ShowS #

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

Defined in IHP.QueryBuilder

data OrderByDirection Source #

Constructors

Asc 
Desc 

Instances

Instances details
Eq OrderByDirection Source # 
Instance details

Defined in IHP.QueryBuilder

Show OrderByDirection Source # 
Instance details

Defined in IHP.QueryBuilder

data Condition Source #

Instances

Instances details
Eq Condition Source # 
Instance details

Defined in IHP.QueryBuilder

Show Condition Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

showsPrec :: Int -> Condition -> ShowS #

show :: Condition -> String

showList :: [Condition] -> ShowS #

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

Defined in IHP.QueryBuilder

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 (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 (id) > |> innerJoinThirdTable City Users (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

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

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

HasQueryBuilder NoJoinQueryBuilderWrapper NoJoins Source # 
Instance details

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

(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

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

data JoinQueryBuilderWrapper joinRegister table 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

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

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

Defined in IHP.Fetch

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

data NoJoinQueryBuilderWrapper table Source #

Instances

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

Defined in IHP.Fetch

Associated Types

type FetchResult (NoJoinQueryBuilderWrapper table) model Source #

HasQueryBuilder NoJoinQueryBuilderWrapper NoJoins Source # 
Instance details

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

type FetchResult (NoJoinQueryBuilderWrapper table) model Source # 
Instance details

Defined in IHP.Fetch

type FetchResult (NoJoinQueryBuilderWrapper table) model = [model]

data LabeledQueryBuilderWrapper foreignTable indexColumn indexValue table 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

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

Defined in IHP.Fetch

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

getQueryBuilder :: HasQueryBuilder queryBuilderProvider joinRegister => queryBuilderProvider table -> QueryBuilder table Source #

data NoJoins Source #

Instances

Instances details
HasQueryBuilder NoJoinQueryBuilderWrapper NoJoins Source # 
Instance details

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

(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

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

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 (id self) to all users by default.

This is needed to support syntax like this:

user
    |> get #posts
    |> fetch
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 #