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. DefaultScope model => QueryBuilder model Source #

Represent's a SELECT * FROM .. query. It's the starting point to build a query. Used togehter 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

findManyBy :: forall value (name :: Symbol) model. (ToField value, EqOrIsOperator value, HasField name model value, KnownSymbol name, KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext) => Proxy name -> value -> QueryBuilder model -> IO [model] Source #

findMaybeBy :: forall value (name :: Symbol) model. (EqOrIsOperator value, ToField value, HasField name model value, KnownSymbol name, KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext) => Proxy name -> value -> QueryBuilder model -> IO (Maybe model) Source #

filterWhere :: forall name model value. (KnownSymbol name, ToField value, HasField name model value, EqOrIsOperator value) => (Proxy name, value) -> QueryBuilder model -> QueryBuilder model 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.

When your condition is too complex, use a raw sql query with sqlQuery.

data QueryBuilder model Source #

Instances

Instances details
Eq (QueryBuilder model) Source #

This hack is to allow Eq instances for models with hasMany relations

Instance details

Defined in IHP.QueryBuilder

Methods

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

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

Show (QueryBuilder a) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

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

show :: QueryBuilder a -> String

showList :: [QueryBuilder a] -> ShowS #

Default (QueryBuilder model) Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

def :: QueryBuilder model #

KnownSymbol (GetTableName a) => ToHtml (QueryBuilder a) Source #

Display QueryBuilder's as their sql query inside HSX

Instance details

Defined in IHP.QueryBuilder

Methods

toHtml :: QueryBuilder a -> Html Source #

Fetchable (QueryBuilder model) model Source # 
Instance details

Defined in IHP.QueryBuilder

Associated Types

type FetchResult (QueryBuilder model) model Source #

Methods

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

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

fetchOne :: QueryBuilder model -> IO model Source #

type FetchResult (QueryBuilder model) model Source # 
Instance details

Defined in IHP.QueryBuilder

type FetchResult (QueryBuilder model) model = [model]

findBy :: forall value (name :: Symbol) model. (EqOrIsOperator value, ToField value, HasField name model value, KnownSymbol name, KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext) => Proxy name -> value -> QueryBuilder model -> IO model Source #

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

Alias for orderByAsc

orderByAsc :: (KnownSymbol name, HasField name model value) => Proxy name -> QueryBuilder model -> QueryBuilder model 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 :: (KnownSymbol name, HasField name model value) => Proxy name -> QueryBuilder model -> QueryBuilder model 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 model where Source #

Methods

defaultScope :: QueryBuilder model -> QueryBuilder model Source #

Instances

Instances details
DefaultScope model Source # 
Instance details

Defined in IHP.QueryBuilder

Methods

defaultScope :: QueryBuilder model -> QueryBuilder model Source #

filterWhereIn :: forall name model value. (KnownSymbol name, ToField value, HasField name model value) => (Proxy name, [value]) -> QueryBuilder model -> QueryBuilder model Source #

filterWhereNotIn :: forall name model value. (KnownSymbol name, ToField value, HasField name model value) => (Proxy name, [value]) -> QueryBuilder model -> QueryBuilder model Source #

genericFetchId :: forall model. (KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext, FilterPrimaryKey model) => Id model -> IO [model] Source #

genericfetchIdOneOrNothing :: forall model. (KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext, FilterPrimaryKey model) => Id model -> IO (Maybe model) Source #

genericFetchIdOne :: forall model. (KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext, FilterPrimaryKey model) => Id model -> IO model Source #

class Fetchable fetchable model | fetchable -> model where Source #

Associated Types

type FetchResult fetchable model Source #

Methods

fetch :: (KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext) => fetchable -> IO (FetchResult fetchable model) Source #

fetchOneOrNothing :: (KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext) => fetchable -> IO (Maybe model) Source #

fetchOne :: (KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext) => fetchable -> IO model Source #

Instances

Instances details
(model ~ GetModelById (Id' model'), value ~ Id' model', HasField "id" model value, ToField (PrimaryKey model')) => Fetchable [Id' model'] model Source # 
Instance details

Defined in IHP.QueryBuilder

Associated Types

type FetchResult [Id' model'] model Source #

Methods

fetch :: [Id' model'] -> IO (FetchResult [Id' model'] model) Source #

fetchOneOrNothing :: [Id' model'] -> IO (Maybe model) Source #

fetchOne :: [Id' model'] -> IO model Source #

(model ~ GetModelById (Id' model'), GetTableName model ~ model', FilterPrimaryKey model) => Fetchable (Maybe (Id' model')) model Source # 
Instance details

Defined in IHP.QueryBuilder

Associated Types

type FetchResult (Maybe (Id' model')) model Source #

Methods

fetch :: Maybe (Id' model') -> IO (FetchResult (Maybe (Id' model')) model) Source #

fetchOneOrNothing :: Maybe (Id' model') -> IO (Maybe model) Source #

fetchOne :: Maybe (Id' model') -> IO model Source #

(model ~ GetModelById (Id' model'), GetTableName model ~ model', FilterPrimaryKey model) => Fetchable (Id' model') model Source # 
Instance details

Defined in IHP.QueryBuilder

Associated Types

type FetchResult (Id' model') model Source #

Methods

fetch :: Id' model' -> IO (FetchResult (Id' model') model) Source #

fetchOneOrNothing :: Id' model' -> IO (Maybe model) Source #

fetchOne :: Id' model' -> IO model Source #

Fetchable (QueryBuilder model) model Source # 
Instance details

Defined in IHP.QueryBuilder

Associated Types

type FetchResult (QueryBuilder model) model Source #

Methods

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

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

fetchOne :: QueryBuilder model -> IO model Source #

include :: forall name model fieldType relatedModel. (KnownSymbol name, KnownSymbol (GetTableName model), HasField name model fieldType, relatedModel ~ GetModelById fieldType) => KnownSymbol name => Proxy name -> QueryBuilder model -> QueryBuilder (Include name model) Source #

genericFetchIds :: forall model value. (KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext, ToField value, EqOrIsOperator value, HasField "id" model value) => [value] -> IO [model] Source #

genericfetchIdsOneOrNothing :: forall model value. (KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext, ToField value, EqOrIsOperator value, HasField "id" model value) => [value] -> IO (Maybe model) Source #

genericFetchIdsOne :: forall model value. (KnownSymbol (GetTableName model), FromRow model, ?modelContext :: ModelContext, ToField value, EqOrIsOperator value, HasField "id" model value) => [value] -> IO model Source #

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

fetchCount :: forall model. (?modelContext :: ModelContext, KnownSymbol (GetTableName model)) => QueryBuilder model -> IO Int Source #

Returns the count of records selected by the query builder.

Example: Counting all users.

allUsersCount <- query @User |> fetchCount -- SELECT COUNT(*) FROM users

Example: Counting all active projects

    activeProjectsCount <- query @Project
        |> filterWhere (#isActive, True)
        |> fetchCount
    -- SELECT COUNT(*) FROM projects WHERE is_active = true

filterWhereSql :: forall name model value. (KnownSymbol name, ToField value, HasField name model value) => (Proxy name, ByteString) -> QueryBuilder model -> QueryBuilder model 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'

fetchExists :: forall model. (?modelContext :: ModelContext, KnownSymbol (GetTableName model)) => QueryBuilder model -> IO Bool Source #

Checks whether the query has any results.

Returns True when there is atleast one row matching the conditions of the query. Returns False otherwise.

Example: Checking whether there are unread messages

    hasUnreadMessages <- query @Message
        |> filterWhere (#isUnread, True)
        |> fetchExists
    -- SELECT EXISTS (SELECT * FROM messages WHERE is_unread = true)

class FilterPrimaryKey model where Source #

Methods

filterWhereId :: Id model -> QueryBuilder model -> QueryBuilder model Source #