FireDAC offers few methods to filter the records. The dataset filtering allows to get records subsets basing on a condition.
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.
FireDAC datasets offer few options to filter records using a standard approach:
ADQuery1.Filter := 'OrderID in (10150, 10151, 10152)'; ADQuery1.Filtered := True;
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;
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:
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;
ADQuery1.FilterChanges := [rtModified, rtDeleted];
To filter records, failed to process at ApplyUpdates call, use FilterChanges with rtHasErrors.
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 in live data window mode uses server side filtering (WHERE) for:
And client side filtering for:
See the following demos for additional details:
What do you think about this topic? Send feedback!