Documentation

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'