Filter
Serverpod makes it easy to build expressions that are statically type-checked. Columns and relational fields are referenced using table descriptor objects. The table descriptors, t
, are accessible from each model and are passed as an argument to a model specific expression builder function. A callback is then used as argument to the where
parameter when fetching data from the database.
Column operations
The following column operations are supported in Serverpod, each column datatype supports a different set of operations that make sense for that type.
When using the operators, it's a good practice to place them within a set of parentheses as the precedence rules are not always what would be expected.
Equals
Compare a column to an exact value, meaning only rows that match exactly will remain in the result.
await User.db.find(
where: (t) => t.name.equals('Alice')
);
In the example we fetch all users with the name Alice.
Not equals is the negated version of equals.
await User.db.find(
where: (t) => t.name.notEquals('Bob')
);
In the example we fetch all users with a name that is not Bob. If a non-null
value is used as an argument for the notEquals comparison, rows with a null
value in the column will be included in the result.
Comparison operators
Compare a column to a value, these operators are support for int
, double
, Duration
, and DateTime
.
await User.db.find(
where: (t) => t.age > 25
);
In the example we fetch all users that are older than 25 years old.
await User.db.find(
where: (t) => t.age >= 25
);
In the example we fetch users that are 25 years old or older.
await User.db.find(
where: (t) => t.age < 25
);
In the example we fetch all users that are younger than 25 years old.
await User.db.find(
where: (t) => t.age <= 25
);
In the example we fetch all users that are 25 years old or younger.
Between
The between method takes two values and checks if the columns value is between the two input variables inclusively.
await User.db.find(
where: (t) => t.age.between(18, 65)
);
In the example we fetch all users between 18 and 65 years old. This can also be expressed as (t.age >= 18) & (t.age <= 65)
.
The 'not between' operation functions similarly to 'between' but it negates the condition. It also works inclusively with the boundaries.
await User.db.find(
where: (t) => t.age.notBetween(18, 65)
);
In the example we fetch all users that are not between 18 and 65 years old. This can also be expressed as (t.age < 18) | (t.age > 65)
.
In set
In set can be used to match with several values at once. This method functions the same as equals but for multiple values, inSet
will make an exact comparison.
await User.db.find(
where: (t) => t.name.inSet({'Alice', 'Bob'})
);
In the example we fetch all users with a name matching either Alice or Bob. If an empty set is used as an argument for the inSet comparison, no rows will be included in the result.
The 'not in set' operation functions similarly to inSet
, but it negates the condition.
await User.db.find(
where: (t) => t.name.notInSet({'Alice', 'Bob'})
);
In the example we fetch all users with a name not matching Alice or Bob. Rows with a null
value in the column will be included in the result. If an empty set is used as an argument for the notInSet comparison, all rows will be included in the result.
Like
Like can be used to perform match searches against String
entries in the database, this matcher is case-sensitive. This is useful when matching against partial entries.
Two special characters enables matching against partial entries.
%
Matching any sequence of character._
Matching any single character.
String | Matcher | Is matching |
---|---|---|
abc | a% | true |
abc | _b% | true |
abc | a_c | true |
abc | b_ | false |
We use like to match against a partial string.
await User.db.find(
where: (t) => t.name.like('A%')
);
In the example we fetch all users with a name that starts with A.
There is a negated version of like that can be used to exclude rows from the result.
await User.db.find(
where: (t) => t.name.notLike('B%')
);
In the example we fetch all users with a name that does not start with B.
ilike
ilike
works the same as like
but is case-insensitive.
await User.db.find(
where: (t) => t.name.ilike('a%')
);
In the example we fetch all users with a name that starts with a or A.
There is a negated version of ilike
that can be used to exclude rows from the result.
await User.db.find(
where: (t) => t.name.notIlike('b%')
);
In the example we fetch all users with a name that does not start with b or B.
Logical operators
Logical operators are also supported when filtering, allowing you to chain multiple statements together to create more complex queries.
The &
operator is used to chain two statements together with an and
operation.
await User.db.find(
where: (t) => (t.name.equals('Alice') & (t.age > 25))
);
In the example we fetch all users with the name "Alice" and are older than 25.
The |
operator is used to chain two statements together with an or
operation.
await User.db.find(
where: (t) => (t.name.like('A%') | t.name.like('B%'))
);
In the example we fetch all users that has a name that starts with A or B.
Relation operations
If a relation between two models is defined a one-to-one or one-to-many object relation, then relation operations are supported in Serverpod.
One-to-one
For 1:1 relations the columns of the relation can be accessed directly on the relation field. This enables filtering on related objects properties.
await User.db.find(
where: (t) => t.address.street.like('%road%')
);
In the example each user has a relation to an address that has a street field. Using relation operations we then fetch all users where the related address has a street that contains the word "road".
One-to-many
For 1:n relations, there are special filter methods where you can create sub-filters on all the related data. With them, you can answer questions on the aggregated result on many relations.
Count
Count can be used to count the number of related entries in a 1:n relation. The count
always needs to be compared with a static value.
await User.db.find(
where: (t) => t.orders.count() > 3
);
In the example we fetch all users with more than three orders.
We can apply a sub-filter to the count
operator filter the related entries before they are counted.
await User.db.find(
where: (t) => t.orders.count((o) => o.itemType.equals('book')) > 3
);
In the example we fetch all users with more than three "book" orders.
None
None can be used to retrieve rows that have no related entries in a 1:n relation. Meaning if there exists a related entry then the row is omitted from the result. The operation is useful if you want to ensure that a many relation does not contain any related rows.
await User.db.find(
where: (t) => t.orders.none()
);
In the example we fetch all users that have no orders.
We can apply a sub-filter to the none
operator to filter the related entries. Meaning if there is a match in the sub-filter the row will be omitted from the result.
await User.db.find(
where:((t) => t.orders.none((o) => o.itemType.equals('book')))
);
In the example we fetch all users that have no "book" orders.
Any
Any works similarly to the any
method on lists in Dart. If there exists any related entry then include the row in the result.
await User.db.find(
where: (t) => t.orders.any()
);
In the example we fetch all users that have any order.
We can apply a sub-filter to the any
operator to filter the related entries. Meaning if there is a match in the sub-filter the row will be included in the result.
await User.db.find(
where:((t) => t.orders.any((o) => o.itemType.equals('book')))
);
In the example we fetch all users that have any "book" order.
Every
Every works similarly to the every
method on lists in Dart. If every related entry matches the sub-filter then include the row in the result. For the every
operator the sub-filter is mandatory.
await User.db.find(
where: (t) => t.orders.every((o) => o.itemType.equals('book'))
);
In the example we fetch all users that have only "book" orders.