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

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

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

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