FireDAC
ContentsIndexHome
PreviousUpNext
Filtering Records

FireDAC offers few methods to filter the records. The dataset filtering allows to get records subsets basing on a condition.

Group
Links
General

All FireDAC datasets are offering few approaches to locally filter the records. After applying a filter, FireDAC will not requery records, but will filter records in local dataset cache. Internally FireDAC builds and maintains a list of filtered records. So, on large record volumes the filter activation may be long, but subsequent records navigation will be the same fast as without filtering. 

 

Standard filtering

FireDAC datasets offer few options to filter records using a standard approach:

  • Filter property allows to specify an conditional expression as a string. After specifying Filter value set Filtered property to True to activate the filter. For example:

 

ADQuery1.Filter := 'OrderID in (10150, 10151, 10152)';
ADQuery1.Filtered := True;

 

  • OnFilterRecord event handler allows to implement filtering as a Delphi code. After specifying Filter value set Filtered property to True to activate the filter. For example:

 

ADQuery1.OnFilterRecord := Form1FilterRecord;
ADQuery1.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 a range

When dataset is sorted using the field list, then application may apply filtering by a field values range. This is most effective way to limit records, as it is using the dataset internal index structures. 

The following methods control the filtering:

  • SetRangeStart - brings dataset to a mode allowing to set range minimal values and erasing the previous values;
  • EditRangeStart - brings dataset to a mode allowing to set range minimal values preserving the previous values;
  • SetRangeEnd - brings dataset to a mode allowing to set range maximal values and erasing the previous values;
  • EditRangeEnd - brings dataset to a mode allowing to set range maximal values preserving the previous values;
  • ApplyRange - activates range filtering after specifying minimal and maximal values;
  • SetRange - combines SetRangeStart, SetRangeEnd and ApplyRange into a single method;
  • CancelRange - cancels the range filtering.

And properties:

  • IsRanged - allows to get the current range filtering mode.
  • KeyExclusive - get / set the inclusion of minimal and maximal values into filtered range;
  • KeyFieldCount - get / set the number of index fields to use in range filtering.

For example: 

 

ADQuery1.IndexFieldNames := 'ORDERID;ORDERDATE';
ADQuery1.SetRangeStart;
ADQuery1.KeyExclusive := False;
ADQuery1.KeyFieldCount := 1;
ADQuery1.FieldByName('OrderID').AsInteger := 10150;
ADQuery1.SetRangeEnd;
ADQuery1.KeyExclusive := False;
ADQuery1.KeyFieldCount := 1;
ADQuery1.FieldByName('OrderID').AsInteger := 10152;
ADQuery1.ApplyRange;

 

Filtering by a record status

The FilterChanges property allows to filter records, depending on their change status. This filtering may be used only in Cached Updates mode. For example, to show only modified and deleted records: 

 

ADQuery1.FilterChanges := [rtModified, rtDeleted];

 

To filter records, failed to process at ApplyUpdates call, use FilterChanges with rtHasErrors. 

 

Other options

There are other options allowing to limit the visible records:

Also note, FireDAC does not support filtering on fields of the fkCalculated and fkLookup kinds. But application may use fkInternalCalc and fkAggregate fields in filtering. 

 

TADTable and filtering

TADTable in live data window mode uses server side filtering (WHERE) for:

  • 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;
  • a detail TADTable in the master-detail relationship;
  • when a range is applied to a TADTable.

And client side filtering for:

See the following demos for additional details:

  • FireDAC\Samples\Comp Layer\TADQuery\Filter
  • FireDAC\Samples\Comp Layer\TADMemTable\MasterDetail
What do you think about this topic? Send feedback!