Sorting

Overview

Sorting is a fundamental operation for managing the order in which records are loaded and displayed in a foundset. The sorting definition is generally expressed in an SQL ORDER BY clause and is handled by the database. This section explains how to read and set the sorting order for foundsets using Servoy, including various data provider types, performance considerations, and advanced sorting techniques.

Understanding Sorting in Foundsets

A foundset's sorting definition is encapsulated in a string property, which can be programmatically managed using the getCurrentSort and sort methods. The sorting string consists of an ordered list of one or more data providers, each followed by a sort direction (asc for ascending or desc for descending). Multiple data providers are separated by commas, indicating their relative sorting priorities.

Example: Sort String Format

// Sort on column1 ascending, then column2 descending
'column1 asc, column2 desc'

The order of data providers determines their relative priority during sorting. If two records have the same value for a higher priority data provider, the next data provider in the list is used.

Example: Sorting by Last Name and First Name

foundset.sort('last_name asc, first_name asc');
// Resulting order: sorted by last name, then by first name when last names are identical

Available Data Provider Types

You can use various types of data providers as sort criteria, including columns, related columns, and related aggregates. Each type has specific use cases and impacts on query performance.

1. Any Column: Sort by any column directly in the table. This is the most straightforward type of sorting and is typically very efficient.

2. Any Related Column: Sort by columns from related tables. This allows for more complex sorting criteria but can add complexity to the query.

3. Any Related Aggregate: Sort by aggregated values from related tables. This is useful for sorting based on summaries, such as the total number of orders per customer.

Example: Sorting by Related Aggregates

-- Sort a customers foundset based on the number of orders each customer has
SELECT customers.customerid 
FROM customers 
INNER JOIN orders ON customers.customerid = orders.customerid 
GROUP BY customers.customerid 
ORDER BY count(orders.orderid) ASC

Sorting on related columns and aggregates is powerful but can significantly change the nature of the foundset's query. Ensure that the database is properly tuned to handle such operations to maintain performance.

Sorting Methods

There are three primary ways to sort data in Servoy:

Using the sort Method

Use the sort method to define the sorting order for a foundset. This method allows you to specify multiple columns and their respective sorting directions.

Example: Setting the Sort Definition

foundset.sort('last_name asc, first_name asc');

Setting initialSort in the Form

Forms in Servoy can have an initialSort property, which defines the default sorting order when the form is loaded. This is useful for ensuring a consistent display order whenever the form is shown.

Example: Setting Initial Sort in Form Designer

// In Form Designer, set the initialSort property
initialSort: 'last_name asc, first_name asc'

See Sorting Fields Wizard for more information.

Setting Intial Sort in Relation Properties for Related Foundsets

For related foundsets, the sort order can be set in the relation properties. This ensures that the related records are always displayed in the specified order.

Example: Setting Intial Sort in Relation Properties

// In Relation Designer, set the sort property
initialSort: 'order_date desc, order_id asc'

See Relation Editor for more information.

Advanced Sorting Techniques

Combining Multiple Sorting Criteria

In complex applications, you might need to combine multiple sorting criteria to meet specific business requirements. You can dynamically construct the sort string based on user inputs or application state.

Example: Dynamic Sorting

var sortCriteria = [];
if (useLastName) {
    sortCriteria.push('last_name ' + (sortAscending ? 'asc' : 'desc'));
}
if (useFirstName) {
    sortCriteria.push('first_name ' + (sortAscending ? 'asc' : 'desc'));
}
var sortString = sortCriteria.join(', ');
foundset.sort(sortString);

Using Query Builder for Sorting

Servoy's Query Builder API can be used to construct complex sorting queries programmatically and apply them to a foundset.

Example: Sorting Using Query Builder

var query = datasources.db.example_data.orders.createSelect();
query.result.add(query.columns.orderid);
query.sort.add(query.columns.shipdate.asc);
query.sort.add(query.columns.customerid.desc);

foundset.loadRecords(query);

Using Custom Sort Functions

In addition to using SQL-based sorting, Servoy allows custom sort functions to define complex sorting logic that cannot be easily expressed using SQL.

Using custom sort functions can be slow if the foundset has a large number of records because it involves comparing records one by one.

Example: Custom Sort Function

foundset.sort(mySortFunction);

function mySortFunction(r1, r2) {
    var o = 0;
    if (r1.id < r2.id) {
        o = -1;
    } else if (r1.id > r2.id) {
        o = 1;
    }
    return o;
}

Performance Considerations

Sorting is generally handled by the database, making it efficient for standard column-based sorts. However, sorting on related columns and aggregates can require complex joins and aggregations, impacting performance. Always ensure your database is optimized for the types of queries generated by your sorting definitions. Use indexing and query optimization techniques to maintain performance.

Conclusion

Understanding and managing sorting in Servoy is crucial for displaying records in a meaningful order. By using the sort method and leveraging different data provider types, developers can create flexible and efficient sorting mechanisms that enhance the user experience. Properly tuning the database and considering the performance implications of complex sorts will ensure that your applications remain responsive and efficient.