Flutter Isar Database — part 5 — Advanced Querying
In the last article, we dealt with how to filter the data we query. Today we will look at advanced querying. This includes 8 topics that we will discover today. Bigger topics are sorting, unique values, and offsets & limits.
If you are new to this series, check out this list.
You can find the whole source code provided in this article here.
Okay, let’s first take a look at sorting. There are four methods to sort your results:
.thenByDesc(). SortBy… means, that you first sort by this value. If two of them are equal (Let’s say you have two times the same shoe model in your database), then you can sort this by something other (for example the size). This could look like this:
This works fine but is a very expensive operation. In the last article, we learned about Indexes. We can use them here too, to sort our output. This is especially important if we have big databases with millions of objects.
Where clause sorting
If you only use one index in your query, the results are already sorted. This saves very much computation time:
If you don’t want to use where clauses, but also want to use the benefits of implicit sorting, you can use
Tip from the official documentation:
If you need the results to be sorted, consider using an index for that purpose. Especially if you work with
limit(). [We will discuss Offset & Limit later.]
To only get unique values, you can use distinct (Everyone who uses SQL should be familiar with this keyword). We again, use the example of the documentation here. We want to know how many models we have in the database:
Where clause distinct
This works great if you have a unique index on your database, but what if have non-unique indexes? You could still use
distinctBy() but this will be very slow. But there is another way of getting distinct values. The
where() function has a parameter called
distinct. It’s by default on false, but we can set it to true of course.
Offset & Limit
If you have a huge database or want to use things like a lazy list view, it’s recommended to limit your number of results. Thankfully, this is very easy to do:
The following chapters will be very short because they are also very short in the documentation. I will quote most of the documentation there.
Let’s talk about execution order for a moment. This doesn’t take very long and is very well explained by the docs:
1. Traverse primary or secondary index to find objects (apply where clauses)
2. Filter objects
3. Sort objects
4. Apply distinct operation
5. Offset & limit results
6. Return results
We’ve always used
.findAll() to get all matching objects. But there are way more operations:
.findFirst()- Retreive only the first matching object or
nullif none matches.
.findAll()- Retreive all matching objects.
.count()- Count how many objects match the query.
.deleteFirst()- Delete the first matching object from the collection.
.deleteAll()- Delete all matching objects from the collection.
.build()- Compile the query to reuse it later. This saves the cost to build a query if you want to execute it multiple times.
“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.”
If you want to aggregate the values of a property query, this is no problem. It’s way faster than finding all matching objects and performing the aggregation manually.
.min()- Finds the minimum value or
nullif none matches.
.max()- Finds the maximum value or
nullif none matches.
.sum()- Sums all values.
.average()- Calculates the average of all values or
NaNif none matches.
Further Reading & Conclusion
In this article, you have learned how to use advanced queries in Isar.
You can unfold the whole power of Isar if you use packages like Freezed, Riverpod, or Flutter Hooks. If you want to learn these additions, I have whole tutorials about them. Check them out here.
In the next few articles, I will introduce more somewhat complicated packages and explain them (e.g. Dio). If you don’t want to miss this, I recommend you to follow me.
I tried my best to write the easiest tutorial which everyone understands. If you appreciate this work, I would be very grateful if you could support this quality content and give me some claps!
Thanks for reading, have a nice day!
Final note: This article is based on the official Isar documentation. All the information provided here is from this site and a big part of the source code is taken from the documentation. Isar Documentation Source: https://isar.dev/