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'

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 filterWhereJoinedTable, but adds a WHERE LOWER(x) = LOWER(y) condition to the query. Example:

Example: get posts by user Tom, ignoring case.

tomPosts <- query @Post
                   |> innerJoin @User (#createdBy, #id)
                   |> filterWhereCaseInsensitiveJoinedTable @User (#name, "Tom" :: Text)
                   |> fetch
-- SELECT posts.* FROM posts INNER JOIN users ON posts.created_by = users.id WHERE LOWER(users.name) = LOWER('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