Filtering Records (FireDAC)
Go Up to Working with DataSets (FireDAC)
All the FireDAC datasets provide approaches to locally filter the records. After applying a filter, FireDAC does not require records, instead it filters records in the local dataset cache. Internally, FireDAC builds and maintains a list of filtered records. So, on large record volumes, the filter activation may take a long time, but the subsequent records navigation is as fast as without filtering.
The FireDAC datasets provide some options to filter records using a standard approach:
- The Filter property allows you to specify a conditional expression as a string. After specifying the Filter value, set the Filtered property to True to activate the filter. For example:
FDQuery1.Filter := 'OrderID in (10150, 10151, 10152)'; FDQuery1.Filtered := True;
- The OnFilterRecord event handler allows you to implement filtering as a Delphi code. After specifying the OnFilterRecord handler, set the Filtered property to True to activate the filter. For example:
FDQuery1.OnFilterRecord := Form1FilterRecord; FDQuery1.Filtered := True; procedure TForm1.Form1FilterRecord(DataSet: TDataSet; var Accept: Boolean); var iOrderID: Integer; begin iOrderID := DataSet.FieldByName('OrderID').AsInteger; Accept := (iOrderID = 10150) or (iOrderID = 10151) or (iOrderID = 10152); end;
Filtering by Range of Values
When the dataset is sorted using the field list, the application applies filtering by a range of field values. This is the most effective way to limit records, because it uses the dataset internal index structures.
The following methods control the filtering:
- SetRangeStart -- brings the dataset to a state that allows setting the minimum range value and erasing the previous values.
- EditRangeStart -- brings the dataset to a state that allows setting the minimum range value, preserving the previous values.
- SetRangeEnd -- brings the dataset to a state that allows setting the maximum range value and erasing the previous values.
- EditRangeEnd -- brings the dataset to a state that allows setting the maximum range value, preserving the previous values.
- ApplyRange -- activates the range filtering after specifying minimum and maximum values.
- SetRange -- combines SetRangeStart, SetRangeEnd, and ApplyRange into a single method.
- CancelRange -- cancels the range filtering.
The following properties control the filtering:
- IsRanged -- allows getting the current range filtering mode.
- KeyExclusive -- gets or sets the inclusion of minimum and maximum values into a filtered range.
- KeyFieldCount -- gets or sets the number of index fields to use in range filtering.
FDQuery1.IndexFieldNames := 'ORDERID;ORDERDATE'; FDQuery1.SetRangeStart; FDQuery1.KeyExclusive := False; FDQuery1.KeyFieldCount := 1; FDQuery1.FieldByName('OrderID').AsInteger := 10150; FDQuery1.SetRangeEnd; FDQuery1.KeyExclusive := False; FDQuery1.KeyFieldCount := 1; FDQuery1.FieldByName('OrderID').AsInteger := 10152; FDQuery1.ApplyRange;
Filtering by Record Status
The FilterChanges property allows filtering records, depending on their change status. This type of filtering can be used only in Cached Updates mode. For example, to show only modified and deleted records, use the following:
FDQuery1.FilterChanges := [rtModified, rtDeleted];
To filter records the ApplyUpdates call failed to process, use the FilterChanges property and set its value to rtHasErrors.
There are other options that allow limiting the visible records, such as:
- Using the master-detail datasets relation.
- Combining sorting and filtering through the local dataset indexes.
Also, note that FireDAC does not support filtering on fields of fkCalculated or fkLookup type. Instead, the application uses fkInternalCalc and fkAggregate fields in filtering.
TFDTable and Filtering
TFDTable in live data window mode uses server-side filtering (WHERE) in the following cases:
- For the Filter property. Note that the Filter property content is sent to a DB as is. You may use FireDAC escape sequences to make an expression compatible with a DBMS and with the local expression engine.
- For a detail TFDTable in the master-detail relationship.
- When a range is applied to a TFDTable.
TFDTable in live data window mode uses client-side filtering for:
- The OnFilterRecord event.
- The FilterChanges property.
- Writing Expressions
- Sorting Records
- Finding a Record
- Calculated and Aggregated Fields
- Master-Detail Relationship
- Browsing Tables