WHERE clause
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 conditons in DEFINE FUNCTION statement or when asserting access control in DEFINE TABLE & DEFINE FIELD statements.
Syntax
Clause 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
DEFINE FUNCTION fn::relation_exists(
$in: record,
$tb: string,
$out: record
) {
LET $results = SELECT VALUE id FROM type::table($tb) WHERE in = $in AND out = $out;
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.
DEFINE TABLE post SCHEMALESS
PERMISSIONS
FOR select
WHERE published = true
OR user = $auth.id
FOR create, update
WHERE user = $auth.id
FOR delete
WHERE user = $auth.id
OR $auth.admin = true
;
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;
