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

Wrap a list of values for use in an IN clause. Replaces a single "?" character with a parenthesized list of rendered values.

Example:

query c "select * from whatever where id in ?" (Only (In [3,4,5]))

Note that In [] expands to (null), which works as expected in the query above, but evaluates to the logical null value on every row instead of TRUE. This means that changing the query above to ... id NOT in ? and supplying the empty list as the parameter returns zero rows, instead of all of them as one would expect.

Since postgresql doesn't seem to provide a syntax for actually specifying an empty list, which could solve this completely, there are two workarounds particularly worth mentioning, namely:

1. Use postgresql-simple's Values type instead, which can handle the empty case correctly. Note however that while specifying the postgresql type "int4" is mandatory in the empty case, specifying the haskell type Values (Only Int) would not normally be needed in realistic use cases.

   query c "select * from whatever where id not in ?"
           (Only (Values ["int4"] [] :: Values (Only Int)))

2. Use sql's COALESCE operator to turn a logical null into the correct boolean. Note however that the correct boolean depends on the use case:

   query c "select * from whatever where coalesce(id NOT in ?, TRUE)"
           (Only (In [] :: In [Int]))

   query c "select * from whatever where coalesce(id IN ?, FALSE)"
           (Only (In [] :: In [Int]))

   Note that at as of PostgreSQL 9.4, the query planner cannot see inside the COALESCE operator, so if you have an index on id then you probably don't want to write the last example with COALESCE, which would result in a table scan. There are further caveats if id can be null or you want null treated sensibly as a component of IN or NOT IN.

Alias for orderByAsc

Alias for orderByAscJoinedTable

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 .. ASC on a joined table column to your query.

Use orderByDescJoinedTable for descending order.

Example: Order joined User records by username ascending.

query @Project
    |> innerJoin @User (#id, #projectId)
    |> orderByAscJoinedTable #username
    |> fetch
-- SELECT ... FROM projects
-- INNER JOIN users ON projects.id = users.project_id
-- ORDER BY users.username 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 ORDER BY .. DESC on a joined table column to your query.

Use orderByAscJoinedTable for ascending order.

Example: Order joined User records by username descending.

query @Project
    |> innerJoin @User (#id, #projectId)
    |> orderByDescJoinedTable #username
    |> fetch
-- SELECT ... FROM projects
-- INNER JOIN users ON projects.id = users.project_id
-- ORDER BY users.username 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

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

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

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.

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'

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'

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

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

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

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 (#createdBy, #id) |> filterWhereILikeJoinedTable User (#name, "%Olaf%") > -- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE users.name ILIKE '%Olaf%'

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

Case-insensitive version of filterWhereMatchesJoinedTable

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

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.

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

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'

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

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

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"])

Joins a table to an existing QueryBuilder (or something holding a QueryBuilder) on the specified columns. Example: > query Posts > |> innerJoin Users (#author, #id) > -- SELECT users.* FROM users INNER JOIN posts ON users.id = posts.author ...

Joins a table on a column held by a previously joined table. Example: > query Posts > |> innerJoin Users (#author, #id) > |> innerJoinThirdTable City Users (#id, #homeTown) > -- SELECT posts.* FROM posts INNER JOIN users ON posts.author = users.id INNER JOIN cities ON user.home_town = cities.id