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.

Happy reading!

Sorting

Okay, let’s first take a look at sorting. There are four methods to sort your results: .sortBy(), .sortByDesc(), .thenBy() and .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 any():

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 offset() and limit(). [We will discuss Offset & Limit later.]

(Source: https://isar.dev/queries.html#sorting)

Unique values

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:

Information

The following chapters will be very short because they are also very short in the documentation. I will quote most of the documentation there.

Execution order

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

(Source: https://isar.dev/queries.html#execution-order)

Query operations

We’ve always used .findAll() to get all matching objects. But there are way more operations:

.findFirst() - Retreive only the first matching object or null if 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.

(Source: https://isar.dev/queries.html#query-operations)

Property queries

“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.”

(Source: https://isar.dev/queries.html#query-operations)

Aggregation

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 null if none matches.
.max() - Finds the maximum value or null if none matches.
.sum() - Sums all values.
.average() - Calculates the average of all values or NaN if none matches.

(Source: https://isar.dev/queries.html#aggregation)

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/

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Tomic Riedel

Tomic Riedel

3.7K Followers

Flutter, Dart & Appwrite Developer | Livelong learner | Happy about sharing my knowledge