The WHERE clause can be used to specify a condition that acts as a filter. You can use the WHERE clause to either filter the result of the FROM clause in a SELECT statement or specify which rows to operate on in an UPDATE, MERGE, or DELETE statement.
It can also be used in special cases when working with conditions in DEFINE FUNCTION statements or when asserting access control in DEFINE TABLE & DEFINE FIELD statements.
Syntax
STATEMENT
[WHERE condition;]
Conditional record selection
The most common use case for the WHERE clause is to filter the result of the SELECT statement. It is particularly useful when you want to select a subset of records from a table based on a condition.
SELECT @fields FROM <TABLE_NAME> WHERE <CONDITION> = <VALUE>;When fetching records from a table, the WHERE clause is used to filter the records that are returned.
Conditional record alteration
The WHERE clause can also be used to specify which records to operate on in an UPDATE, MERGE, or DELETE statement.
UPDATE [TABLE_NAME] SET [FIELDS] WHERE [CONDITION] = [VALUE]; Setting conditions in DEFINE FUNCTION statements
/**[test]
[[test.results]]
value = "NONE"
*/
-- Define a function that checks if a relation exists between two nodes
DEFINE FUNCTION fn::relation_exists(
$in: record,
$tb: string,
$out: record
) {
-- Check if a relation exists between the two nodes.
LET $results = SELECT VALUE id FROM type::table($tb) WHERE in = $in
AND out = $out;
-- Return true if a relation exists, false otherwise
RETURN array::len($results) > 0;
}; Setting permissions conditions in DEFINE TABLE statements
The WHERE clause can be used to specify the conditions for the permissions of a table and based on the conditions, the permissions are applied to the table CRUD operations.
/**[test]
[[test.results]]
value = "NONE"
*/
-- Specify access permissions for the 'post' table
DEFINE TABLE post SCHEMALESS
PERMISSIONS
FOR select
-- Published posts can be selected
WHERE published = true
-- A user can select all their own posts
OR user = $auth.id
FOR create, update
-- A user can create or update their own posts
WHERE user = $auth.id
FOR delete
-- A user can delete their own posts
WHERE user = $auth.id
-- Or an admin can delete any posts
OR $auth.admin = true
;/**[test]
[[test.results]]
value = "NONE"
*/
-- Define a relation table, and constrain the type of relation which can be stored
DEFINE TABLE assigned_to SCHEMAFULL TYPE RELATION IN tag OUT sticky
PERMISSIONS
FOR create, select, update, delete
WHERE in.owner == $auth.id AND out.author == $auth.id;