Sorting Records (FireDAC)
Go Up to Working with DataSets (FireDAC)
All FireDAC datasets offer some approaches to locally sort the records. Before sorting the records, FireDAC fetches all records from the result set. Internally, FireDAC builds and maintains a list of sorted records. So, for large record volumes the sorting may be long, but subsequent record navigation will be as fast as without sorting.
To sort the dataset by field values, the application uses the IndexFieldNames property. It supports multiple fields, case-insensitive, and descending modes. Also, you can use FormatOptions.SortOptions to modify the sort order properties. For example:
FDQuery1.IndexFieldNames := 'ORDERID';
FDQuery1.IndexFieldNames := 'OrderDate:D;Price';
Alternatively, when a dataset has indexes defined in the Indexes collection, one of the indexes can be selected. To do that, use the IndexName property. For example:
with FDQuery1.Indexes.Add do begin Name := 'By OrderDate'; Fields := 'OrderDate'; Active := True; end; FDQuery1.IndexName := 'By OrderDate';
FireDAC allows defining views of the dataset records using the Indexes collection. The following TFDIndex properties control the view:
- Sorting by fields (Fields, CaseInsFields, DescFields, Options).
- Sorting by expression (Expression, Options).
- Records filter (Filter, FilterOptions).
- Records distinction flag (Distinct).
The view is identified by the Name property. It must be unique across the dataset.
The view is a list of records maintained by FireDAC when the application fetches or edits records. The view is maintained when both its Active property and the IndexesActive property of the dataset are set to True. When the view has the soUnique or soPrimary property in Options and the application needs to enforce record uniqueness, set the ConstraintsEnabled property of the dataset to True.
When the view is selected, the dataset represents the record list maintained by this view. To select the view, set its Selected property to True or set the IndexName property of the dataset to the view name. For example:
with FDQuery1.Indexes.Add do begin Name := 'May sale dates'; Fields := 'OrderDate'; Filter := 'MONTH(OrderDate) = 5'; Distinct := True; Active := True; Selected := True; end;
Some navigation methods, such as Locate / Lookup / SetRange, use the current sort order to optimize their operations. Note that the Filter property does not use the views to optimize filtering.
The application can use the fkInternalCalc and fkAggregate fields in sorting. FireDAC does not support sorting on fields of fkCalculated and fkLookup type. As a workaround, the application can create the fkInternalCalc field, fill it in the OnCalcFields event handler with the fkCalculated or fkLookup field value, then sort on this fkInternalCalc field.
To sort dataset by original records order (as the records were fetched and /or appended), use '#' as a value for IndexFieldNames and Fields properties.
TFDTable and Sorting
The TFDTable sorting is performed at server side (ORDER BY). Note that FireDAC automatically and transparently adds primary key fields to the sorting order. This is required for proper live data window functionality.
- Writing Expressions
- Filtering Records
- Finding a Record
- Calculated and Aggregated Fields
- Master-Detail Relationship
- Browsing Tables
- FireDAC TFDMemTable Main sample
- FireDAC TFDQuery Indices sample