Copyright | (c) digitally induced GmbH 2020 |
---|---|
Safe Haskell | None |
IHP.QueryBuilder
Contents
Description
QueryBuilder is mainly used for doing simple SELECT
sql queries. It allows dynamic
creation of sql queries in a type safe way.
For more complex sql queries, use sqlQuery
.
Synopsis
- query :: forall model table. table ~ GetTableName model => DefaultScope table => QueryBuilder table
- 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
- data QueryBuilder (table :: Symbol)
- newtype In a = In a
- orderBy :: (KnownSymbol name, HasField name model value, model ~ GetModelByTableName table) => Proxy name -> QueryBuilder table -> QueryBuilder table
- orderByAsc :: forall name model table value. (KnownSymbol name, HasField name model value, model ~ GetModelByTableName table) => Proxy name -> QueryBuilder table -> QueryBuilder table
- orderByDesc :: forall name model table value. (KnownSymbol name, HasField name model value, model ~ GetModelByTableName table) => Proxy name -> QueryBuilder table -> QueryBuilder table
- limit :: Int -> QueryBuilder model -> QueryBuilder model
- offset :: Int -> QueryBuilder model -> QueryBuilder model
- queryUnion :: QueryBuilder model -> QueryBuilder model -> QueryBuilder model
- queryOr :: qb ~ QueryBuilder model => (qb -> qb) -> (qb -> qb) -> qb -> qb
- class DefaultScope table where
- defaultScope :: QueryBuilder table -> QueryBuilder table
- filterWhereIn :: forall name table model value. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, [value]) -> QueryBuilder table -> QueryBuilder table
- filterWhereNotIn :: forall name table model value. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, [value]) -> QueryBuilder table -> QueryBuilder table
- filterWhereLike :: forall name table model value. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder table
- filterWhereILike :: forall name table model value. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder table
- filterWhereMatches :: forall name table model value. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder table
- filterWhereIMatches :: forall name table model value. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, value) -> QueryBuilder table -> QueryBuilder table
- class EqOrIsOperator value
- filterWhereSql :: forall name table model value. (KnownSymbol name, ToField value, HasField name model value, model ~ GetModelByTableName table) => (Proxy name, ByteString) -> QueryBuilder table -> QueryBuilder table
- class FilterPrimaryKey table where
- filterWhereId :: Id' table -> QueryBuilder table -> QueryBuilder table
- distinctOn :: forall name model value table. (KnownSymbol name, HasField name model value, model ~ GetModelByTableName table) => Proxy name -> QueryBuilder table -> QueryBuilder table
- distinct :: QueryBuilder table -> QueryBuilder table
- toSQL :: KnownSymbol table => QueryBuilder table -> (ByteString, [Action])
- toSQL' :: SQLQuery -> (ByteString, [Action])
- buildQuery :: forall table. KnownSymbol table => QueryBuilder table -> SQLQuery
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
Constructors
In a |
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
DefaultScope table Source # | |
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 #
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 #
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
.
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
EqOrIsOperator otherwise Source # | |
Defined in IHP.QueryBuilder Methods toEqOrIsOperator :: otherwise -> FilterOperator | |
EqOrIsOperator (Maybe something) Source # | |
Defined in IHP.QueryBuilder Methods toEqOrIsOperator :: Maybe something -> FilterOperator |
filterWhereSql :: forall name table model value. (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 an @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 This is needed to support syntax like this: user |> get #posts |> fetch |
Eq Action Source # | Needed for the 'Eq QueryBuilder' instance |