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

filterWhere :: 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 #

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 @User
    |> 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.

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) => Proxy name -> QueryBuilder table -> QueryBuilder table Source #

Alias for orderByAsc

orderByAsc :: forall name model table value. (KnownSymbol name, HasField name model value, model ~ GetModelByTableName table) => Proxy name -> QueryBuilder table -> QueryBuilder 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. (KnownSymbol name, HasField name model value, model ~ GetModelByTableName table) => Proxy name -> QueryBuilder table -> QueryBuilder 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 :: Int -> QueryBuilder model -> QueryBuilder model Source #

Adds an LIMIT .. to your query.

Example: Fetch 10 posts

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

offset :: Int -> QueryBuilder model -> QueryBuilder 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 :: QueryBuilder model -> QueryBuilder model -> QueryBuilder 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 :: qb ~ QueryBuilder model => (qb -> qb) -> (qb -> qb) -> qb -> qb 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 #

filterWhereIn :: forall name table model value. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, [value]) -> QueryBuilder table -> QueryBuilder 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. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, [value]) -> QueryBuilder table -> QueryBuilder 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. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder 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. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder 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. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder 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. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder table Source #

Adds a WHERE x ~* y condition to the query. Case-insensitive version of filterWhereMatches.

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. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, ByteString) -> QueryBuilder table -> QueryBuilder 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. (KnownSymbol name, HasField name model value, model ~ GetModelByTableName table) => Proxy name -> QueryBuilder table -> QueryBuilder 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 :: QueryBuilder table -> QueryBuilder 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 => QueryBuilder 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. KnownSymbol table => QueryBuilder table -> SQLQuery Source #

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 #