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

The QueryBuilder is a flat newtype over SQLQuery. Each combinator directly modifies fields of the underlying SQLQuery, avoiding any recursive tree traversal.

Represents a WHERE condition

A condition value: either a parameterized encoder or a literal SQL fragment.

Represents an ORDER BY clause component

ORDER BY direction

Operators used in WHERE clause conditions

Represents whether string matching should be case-sensitive or not

Type class for default scoping of queries

Helper to deal with some_field IS NULL and some_field = 'some value'

Type class for filtering by primary key

Extract the SQLQuery from a QueryBuilder.

Compile a QueryBuilder to SQL text (for testing / error messages). Discards the encoder.

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.

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)));

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'

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

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.

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 '%..%'

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 '%..%'

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). '

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

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()

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()

Adds a WHERE x > y condition to the query.

Example: Find assignments with grade greater than 80.

greatAssignments <- query @Assignment
    |> filterWhereGreaterThan (#grade, 80)
    |> fetch
-- SELECT * FROM assignments WHERE grade > 80

See also: filterWhereLarger, filterWhereGreaterThanOrEqualTo, filterWhereAtLeast

Alias for filterWhereGreaterThan. Adds a WHERE x > y condition to the query.

Example: Find assignments with grade larger than 80.

greatAssignments <- query @Assignment
    |> filterWhereLarger (#grade, 80)
    |> fetch
-- SELECT * FROM assignments WHERE grade > 80

Adds a WHERE x >= y condition to the query.

Example: Find assignments with grade at least 80.

greatAssignments <- query @Assignment
    |> filterWhereGreaterThanOrEqualTo (#grade, 80)
    |> fetch
-- SELECT * FROM assignments WHERE grade >= 80

See also: filterWhereAtLeast, filterWhereGreaterThan, filterWhereLarger

Alias for filterWhereGreaterThanOrEqualTo. Adds a WHERE x >= y condition to the query.

Example: Find assignments with grade at least 80.

greatAssignments <- query @Assignment
    |> filterWhereAtLeast (#grade, 80)
    |> fetch
-- SELECT * FROM assignments WHERE grade >= 80

Adds a WHERE x < y condition to the query.

Example: Find assignments with grade less than 60.

poorAssignments <- query @Assignment
    |> filterWhereLessThan (#grade, 60)
    |> fetch
-- SELECT * FROM assignments WHERE grade < 60

See also: filterWhereSmaller, filterWhereLessThanOrEqualTo, filterWhereAtMost

Alias for filterWhereLessThan. Adds a WHERE x < y condition to the query.

Example: Find assignments with grade smaller than 60.

poorAssignments <- query @Assignment
    |> filterWhereSmaller (#grade, 60)
    |> fetch
-- SELECT * FROM assignments WHERE grade < 60

Adds a WHERE x <= y condition to the query.

Example: Find assignments with grade at most 60.

poorAssignments <- query @Assignment
    |> filterWhereLessThanOrEqualTo (#grade, 60)
    |> fetch
-- SELECT * FROM assignments WHERE grade <= 60

See also: filterWhereAtMost, filterWhereLessThan, filterWhereSmaller

Alias for filterWhereLessThanOrEqualTo. Adds a WHERE x <= y condition to the query.

Example: Find assignments with grade at most 60.

poorAssignments <- query @Assignment
    |> filterWhereAtMost (#grade, 60)
    |> fetch
-- SELECT * FROM assignments WHERE grade <= 60

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'

Alias for orderByAsc

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

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

Adds an LIMIT .. to your query.

Example: Fetch 10 posts

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

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

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

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

Merges the results of two query builders by ORing their WHERE conditions.

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 = '..') OR (team_id = '..')

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 { .. }

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

Compiles a FilterOperator to its SQL representation

Returns the NOT version of an operator

>>> negateFilterOperator EqOp
NotEqOp