Blog

All Blog Posts | Next Post | Previous Post

Firestore server-side filtering in TMS WEB Core v1.7

Saturday, April 24, 2021

To create a high performance web client application, a lot of things need to be taken in account. But foremost, when you create a modern SPA (single-page-application) architecture web client application, you will want to look at optimizing the communication of the application to your database back-end. In case you use the Google cloud based infrastructure as your database back-end, you will want to perform as little as possible requests and ensure that the data involved in these requests is as compact as possible. The server-side filtering capabilities of Google Firestore allow you to easily achieve this, especially with the new built-in support for it in the TWebFirestoreClientDataSet in TMS WEB Core.

Recalling loading and filtering features of TClientDataSet in classic VCL

If you have used a TClientDataSet in Delphi VCL, you already know that the component provides an in-memory table that can be manipulated easily and efficiently. But if there are a large number of records in a database, loading all of them in the TClientDataSet needs to be avoided. With traditional Delphi TClientDataSet, if the records are loaded through a DataSetProvider that supports queries then you can change those queries to load limited records based on a criteria. This approach greatly reduces the memory overhead of the TClientDataSet, since fewer records need to be stored in memory.

Compare this with another option of local filters, also from standard Delphi VCL, where the number of records visible in the dataset can be reduced by using local filtering options. But that does not reduce the number of records in memory. It only restricts access to the records filtered out.

In Web Core, we give a solution similar to DataSetProvider where certain filtering criteria can be applied at the Firestore to reduce the number of records fetched on opening the dataset.

New server-side filtering features in TWebFirestoreClientDataSet

The new server-side filtering features purpose is to limit the number of records fetched from a Firestore collection. Consider the problem when the Firestore collection has a large number of records. Loading all of them at once is not a good idea due to the memory overhead of an in-memory table in the TClientDataSet.

In the new release of TMS WEB Core, the Firestore ClientDataSet component provides new features to apply filters to the data obtained at the Firestore end.

Important background information

Before we look at the details of each filtering method, it is necessary to understand some core principles.

1) Method names begin with "AddService"

There are several Firestore related filtering methods available in the Firestore ClientDataSet component. They are named starting with "AddService" indicating that they are applied at the Firestore Service level. This is to differentiate them from any local filtering methods already available in the standard ClientDataSet.

2) Firestore filters are not as flexible as SQL queries

The primary purpose of the Firestore filter methods is to limit the number of records fetched on opening the dataset in order to reduce the memory overhead.

Don't expect the Firestore filters to be as flexible as an SQL query for your data processing requirements. There are many restrictions and limitations as you will discover later. For an exanple, please see "Limitations and Gotchas" under AddServiceFilterCondition method later in this article.

If you have advanced filtering requirements then in addition to the Firestore filters, you should use the local filtering features of the ClientDataSet for the advanced filtering needs.

3) Understand their mapping to the Firestore Filter API

It's necessary to understand that each of the methods descibed in this article maps to a particular kind of query on the Firestore side, for example AddServiceFilterCondition maps to a "where" query on Firestore.

The mappings are important to keep in mind so that you can refer to the proper Firestore documentation to look for more details on what works and what doesn't. We can not possibly describe/repeat all the Firestore specific gotchas in the TMS WEB Core documentation.

For example, the description of the method AddServiceFilterCount in this article mentions the following mapping:

Maps to: Firestore "limit" query

The "maps to" means that internally the component uses "Firestore limit query." So if you are a curious type and want complete details on "Firestore limit queries," please go to the Firestore documentation on that keyword.

4) Many Filters depend on the current Sort order that you need to set up before calling the filter

For some of the Firestore filters, you need to sort on the field being used. This can be done by using the calls ClearSortFieldDefs and AddSortFieldDef. Note that this sorting happens at the Firestore end.

Here is an example of setting up a sort on the age field.

fireStoreCDS.ClearSortFieldDefs;
fireStoreCDS.AddSortFieldDef(
                   'age', // field name
                   true); // is ascending

5) The Filtering Methods

AddServiceFilterCount method (Maps to: Firestore "limit" query)

You can limit the number of records obtained by this method on an Open. You need to set it before calling Open.

Example:

The following filter when set up will fetch only 100 records on open.

fireStoreCDS.AddServiceFilterCount(100);

Usage notes:

If you have set up a sort order, they will be first 100 records in that sort order.
How do you get the next 100 records? Use the method AddServiceFilterContinueAfterLast described next.

AddServiceFilterContinueAfterLast method for pagination (Maps to: Firestore pagination)

This is the method that sets up the pagination to get the next set of records. You need to call Refresh after this. Then you can call Refresh again and again to get the next set of records or pages.

For example, if you call it after the above example of AddServiceFilterCount and call Refresh, it will fetch the next 100 records. Subsequently, just call Refresh to get the next set and so on till you get an empty dataset.

Usage notes:

AddServiceFilterContinueAfterLast can also be used after the AddServiceFilterRange method described later. But it also requires to clear filters and set a new range. Please see the Filters demo for the actual code.

AddServiceFilterCondition method (Maps to: Firestore "where" query)

Use this method to specify a where condition filter. Setting the filter activates it on the next Open or Refresh.

Requirement:

If you are using a Sort Order by using a AddSortFieldDef call, it must be on the same field that you are using in this filter.

Examples:

1. Get records where field "status" has the value "New"

fireStoreCDS.AddServiceFilterCondition('status', '==', 'New');

2. Use more than once to specify multiple conditions ANDed but for the same field.

fireStoreCDS.AddServiceFilterCondition('age', '>', 18);
fireStoreCDS.AddServiceFilterCondition('age', '<', 65);

3. For an OR condition, use the "in" operator. For example, to get records where field "status" has the value "New" or "Pending"

fireStoreCDS.AddServiceFilterCondition('status', 'in', TJSArray.New('New', 'Pending'));

Limitations and gotchas:

Date/Time fields require special code. This is described in Web Core documentation of Firestore.
This filter maps to Firestore "where" that is much limited as compared to SQL's "where." For example, one of the rules says, you can add more than one where filters, provided they are on the same field and if a Sort Order is being used, the Sort Order must be on the same field. To give you another example, the example 2 above works as AND. But if you want to use a similar OR condition, it's not easily possible. People have written articles on such problems that you can find on the Internet by searching for Firestore where query problems with proper keywords.
It's not possible to describe all possible rules and usage of Firestore "where" filter in this article or even in the Web Core documentation. For more details, please refer to the Firestore document "Querying and filtering data" (search Google on this) and refer to the section on "where" queries.

AddServiceFilterRange method (Maps to: Firestore filters startAt, startAfter, endAt, endBefore)

Use this method to specify a Firestore "start" and/or "end" condition on a value that refers to the current sort field set by AddSortFieldDef call. Setting the range filter activates it on next Refresh or Open call.

Requirement:

The value passed works on the current sort field. So you must have defined a sort order by AddSortFieldDef call.

Example:

Suppose you have defined the sort on the "age" field by AddSortFieldDef

fireStoreCDS.ClearSortFieldDefs;
fireStoreCDS.AddSortFieldDef("age", true);

Now you want to start at age 18 and end at age 65. You will use 2 filters.

fireStoreCDS.AddServiceFilterCondition(frStartAt, 18);
fireStoreCDS.AddServiceFilterCondition(frEndAt, 65);

Limitations and gotchas:

Date/Time fields require special code. This is described in TMS WEB Core documentation of Firestore.

ClearServiceFilters

Clears all the Firestore filters set up so that all the records are obtained on the next open or refresh.

New Firestore Filtering Demo

A demo is available that shows use of the above filtering methods. You will find this demo under Demo\Services\Firestore.

There are 2 parts to this demo, an import utility that imports 500 JSON objects to a Firestore collection as a prerequisite for the demo and then the Demo itself that filters the collection when obtaining data.

Preparing for the Filter Demo: Build and Run the Import Utility

In the Firestore Filters Demo folder under "Demo\Services," you will find a project ImportTestData.

Please follow these steps:

Open the project TMSWeb_ImportTestData
Build and Run the project
Enter values for API Key, AuthDomain and ProjectID if they are not automatically filled by your previous usage of any Firestore demo.
Click on Import Test Data.

This should import 500 objects from the JSON file in a new collection called SalesData. You can verify that the data is imported to such a collection in the Firestore Console. Also, in case you want to recreate this collection due to any problems, you can delete the colleciton in Firestore console and import again.

Running the Filters Demo

Please follow these steps:

Open the project TMSWeb_FirestoreFilters.
If you didn't change the Collection name when importing, just Build the project. Otherwise, please search for CUSTOMIZE comment and use the same collection name here in which you imported the data above.
Now run the project.
Click on the Filters items one by one and see how they work.
To look at how the actual filters are used in code, please see the procedure setupExampleFilter.

Summary

First, we recalled the Loading and Filtering features of TClientDataSet from Delphi VCL. This was necessary to understand the memory limitations of TClientDataSet.
Next, the article stressed on some background information that is necessary to understand before going into the Firestore filtering methods.
This was followed with the actual description of the Firestore filtering methods along with examples, usage notes and limitations.
Then we looked at the Firestore Filtering Demo that uses the filtering methods of the component.

Of course, everything you learned here applies to TMS WEB Core for Delphi or Lazarus as well as TMS WEB Core for Visual Studio Code.

What's next

In the next part 4 of this series, we are going to take a look at the new Async methods to perform database operations in code. In particular, we show you how to wait for the result of the previous operation before performing the next operation in a world of async Firestore updates.

Bruno Fierens

This blog post has not received any comments yet.