Querying is how you find records that match certain conditions, for example:
- Find all starred contacts
- Find distinct first names in contacts
- Delete all contacts that don't have a last name defined
Because queries are executed on the database, and not in Dart, they're really fast. When you cleverly use indexes, you can improve the query performance even further. In the following, you'll learn how to write queries and how you can make them as fast as possible.
There are two different methods of filtering your records: Filters and where clauses. We'll start by taking a look at how filters work.
Filters are easy to use and understand. Depending on the type of your properties, there are different filter operators available most of which have self-explanatory names.
Filters work by evaluating an expression for every object in the collection being filtered. If the expression resolves to true, Isar includes the object in the results. Filters have no effect on the ordering of the results.
We'll use the following model for the examples below:
Depending on the type of a field, there are different conditions available.
|Matches values that are equal to the specified |
|Matches values that are between |
|Matches values that are greater than |
|Matches values that are less than |
|Matches values that are |
Let's assume the database contains four shoes with sizes 39, 40, 46 and one with unset (
null) size. Unless you perform sorting, the values will be returned sorted by id.
You can composit predicates using logical operators.
|Evaluates to true if both left-hand and right-hand expressions are true.|
|Evaluates to true if either expression returns true.|
|Negates the result of the following expression.|
|Group conditions and allow to specify order of evaluation.|
If you want to find all shoes with size 46, you can use the following query:
If you want to use more than one condition, you can combine multiple filters using logical and
.and() and logical or
You can also group conditions using
To negate a condition or group, use logical not
You can compare string values using these string operators. Regex-like wildcards allow more flexibility in search.
|Matches string values that begins with provided |
|Matches string values that contain the provided |
|Matches string values that end with the provided |
|Matches string values that match the provided |
All string operations have an optional
caseSensitive parameter that defaults to
A wildcard string expression is a string that uses normal characters with two special wildcard characters:
*wildcard matches zero or more of any character
?wildcard matches any character. For example, the wildcard string
"dug", but not
Sometimes it is necessary to build a query based on some conditions or for different values. Isar has a very powerful tool to build conditional queries:
|Extends the query only if the |
|Extends the query for each value in |
Modifiers can be combined however you like. Every part of a query can be optional or repeated but it does not always make sense. Repeatedly applying a limit for example will effectively only use the limit applied last.
In this example we build a method that can find shoes with an optional filter:
If you want to find all shoes that have one of multiple shoe sizes you can either write a conventional query or use the
If your model contains links or backlinks you filter your query based on the linked objects.
We can for example find all students that have a math or English teacher:
Link filters evaluate to
true if at least one linked object matches the conditions.
Where clauses are a very powerful tool but it can be a little difficult to get them right.
In contrast to filters where clauses use the indexes you defined in the schema. Querying an index is a lot faster than filtering each record individually. As a basic rule, you should always try to reduce the records as much as possible using where clauses and do the remaining filtering using filters.
You can combine where clauses using logical or.
Let's add indexes to the shoe collection:
There are two indexes. The index on
size allows us to use where clauses like
.sizeEqualTo(). The composite index on
isUnisex allows where clauses like
isUnisexSizeEqualTo(). But also
isUnisexEqualTo() because you can also use any prefix of an index.
We can now rewrite the query from before that finds unisex shoes in size 46 using the composite index. This query will be a lot faster than the previous one:
Where clauses have two more superpowers: They give you "free" sorting and a super fast distinct operation.
You can define how the results should be sorted when executing the query using the
To find all shoes sorted by model name in acending order and size in descending order:
Sorting a lot of results can be an expensive operation. Luckily, we can again use indexes an make our query lighning fast even if we need to sort a million objects.
If you use a single index in your query, the results are already sorted by the index. That's a big deal!
Let's assume we have shoes in sizes
[43, 39, 48, 40, 42, 45] and we want to find all shoes with a size greater than or equal to
42 and also have them sorted by size:
As you can see, the result is sorted by the
size index. If you want to reverse the sort order, you can set
Sometimes you don't want to use a where clause but still benefit from the implicit sorting. You can use the
any where clause:
If you use a composite index, the results are sorted by all fields in the index.
General rule of thumb:
If you need the results to be sorted, consider using an index for that purpose. Especially if you work with
Sometimes it's not possible of useful to use an index for sorting. For such cases, you should use indexes to reduce the number of resulting entries as much as possible.
To return only entries with unique values, use the distinct predicate. For example, to find out how many different shoe models you have in your Isar database:
You can also chain multiple distinct conditions for example to find all shoes with distinct model-size combinations:
If you have a non unique index, you may want to get all of its distinct values. You could use the
distinctBy operation but it's performed after sorting and filters so there is some overhead to it.
If you only use a single where clause you can instead rely on the index to perform the distinct operation.
Another great advantages of indexes is that you get "free" sorting. When you query results using a single where clause, the results are sorted by the index. For composite indexes, the result are sorted by all fields in the index.
It's often a good idea to limit the number of results from a query. You can do so by setting a
By setting an
offset() you can also paginate the results of your query.
Isar queries are always executed in the same order:
- Traverse primary or secondary index to find objects
- Filter objects
- Sort results
- Apply distinct operation
- Offset & limit results
- Return results
In the previous examples we used
.findAll() to retrieve all matching objects. There are more operations available however:
|Retreive only the first matching object or |
|Retreive all matching objects.|
|Count how many objects match the query.|
|Delete the first matching object from the collection.|
|Delete all matching objects from the collection.|
|Compile the query to reuse it later.|
If you are only interested in the values of a single property, you can use a property query. Just build a normal query and select a property:
Using only a single property saves time during deserialization.
You can also aggregate the values of a property query. The following aggregation operations are available:
|Finds the minimum value or |
|Finds the maximum value or |
|Sums all values.|
|Calculates the average of all values or |
Using aggregations is vastly faster than finding all matching objects and performing the aggregation manually.
All of the examples above used the QueryBuilder and the generated static extension methods. Maybe you want to create very dynamic queries or even a custom query language (like the Isar Inspector. In that case you can use the