Making properties sortable

A sortable property is a property that can be used to sort the elements of a collection resource. You can use the instructions in this topic to make properties sortable that are:

  • Part of custom entities

  • Custom properties that extend base configuration entities

  • Base configuration properties that are not already sortable

Note that if a base configuration property is sortable, you can’t make it unsortable.

Important: Adding new sortable columns can have performance implications, particularly if the collection to be sorted tends to have a large number of elements. In these cases, you might need to add new database indexes to maintain acceptable performance.

Query-backed collections vs stream-backed collections

The way in which you make a property sortable depends not only on the type of property, but also on the type of the collection. Collections can be query-backed or stream-backed.

  • Query-backed collections extend RestQueryCollectionResource.

  • Stream-backed collections extend RestStreamCollectionResource.

  • Resources that have both stream-backed and query-backed collections extend RestDualCollectionResource.

For all collection types, use the schema file to make a property sortable when the property exists directly on the data model entity.

For query-backed collections, use resource files to sort on a property that is derived at run time or derived from a join of multiple data model entities.

For stream-backed collections, always use the schema files. If you want to sort based on a property that’s accessed through a join of data model entities, create an enhancement property with the join, then add that property to the schema file (as well as the mapping file).

Note:

See Gosu Reference for more information on enhancements. See Syntax for schema configuration files for information on adding properties to mapping files.

For dual collections, perform the required actions for both query-backed and stream-backed. For example, if you create a sort based on a property that requires a join between multiple data model entities, you need to update the resource file (for query-backed) and create an enhancement and update the schema file (for stream-backed).

Adding sortable properties through schema files

Properties that are directly on a data model entity (or created through an enhancement for stream-backed collections) can easily be made sortable by updating the schema file associated with the API to which the entity belongs. For example, to make a property on the User data model entity sortable, you update the admin_ext-1.0.schema.json file. Within this file, set the x-gw-extensions object's sortable property to true:

"x-gw-extensions": {
  "sortable": true
}
Java

For example, suppose the CustomEntity_Ext entity has an ExpirationDate field. When retrieving a collection of CustomEntity_Ext instances, the caller application can opt to sort the collection based on the expiration date using a call such as:

GET /common/v1/customentity-exts?sort=expirationDate
Java

In the common-1.0.schema.json file, the property declaration would be:

"definitions": {
  "CustomEntityExt": {
    ...
      "properties": {
	 ...
	   "expirationDate": {
	     "x-gw-extensions": {
	       "sortable": true
	     }
	   }
Java

Adding sortable properties through resource files

To make a property sortable that is not directly mapped to an entity, you need to update the resource file associated with the collection you want to sort. For example, if the property is returned as part of the Users collection, you’ll need to update the UsersExtResource.gs file.

The following is the simplest example of making a property in a query-backed collection sortable:

override property get CustomSortColumnMap() : Map<String, QuerySortColumn> {
    var customSort = new HashMap<String, QuerySortColumn>(super.CustomSortColumnMap)
    customSort.put("sortProperty_Ext", new SimpleSortColumn(Resource#Property))
    return customSort
}
Java

This example overrides getCustomSortColumnMap. Notice it returns a type of QuerySortColumn, as this is a query-backed collection.

The first line is a call to the super method. You need to start your custom sorts with this call to avoid losing any sorts already defined in the core resource class.

Next is a call to SimpleSortColumn. Use SimpleSortColumn for sorting simple properties that cannot be sorted by adding them to the schema.

In this example, make the following replacements:

  • sortProperty_Ext: Replace with the name of the property you’re sorting on, such as customProperty_Ext.

  • Resource#Property: Replace with the name of the property and the resource where it can be found. For example, CustomEntity#CustomProperty.

Here’s an example of adding a custom sort on the ActivityPattern property of an Activity:

override property get CustomSortColumnMap() : Map<String, QuerySortColumn> {
   var customSort = super.CustomSortColumnMap
   customSort.put("activityPattern_Ext", new SimpleSortColumn(Activity#ActivityPattern))
   return customSort
 }
Java

For practical examples of using this type of sort, see the Policy Center documentation.

Some properties are derived from other resources. The sort for these properties is a little more complex. Instead of calling a simple sort on a single property, you need to join tables to retrieve the property you want to sort on. Here’s what that might look like:

private static var sortSortColumnPath = Lists.newArrayList({Resource1#JoinProperty.getPropertyInfo() as IEntityPropertyInfo, Resource2#SortProperty.PropertyInfo as IEntityPropertyInfo})

 override property get CustomSortColumnMap() : Map<String, QuerySortColumn> {
   var customSort = new HashMap<String, QuerySortColumn>(super.CustomSortColumnMap)
   customSort.put("sortProperty_Ext", new PathSortColumn(abuidSortColumnPath))
   return customSort
 }

}
Java

You need to replace the placeholders in the preceding example with the following:

  • Resource1: The data model entity of the collection you’re sorting on.

  • JoinProperty: The foreign key property used to join this data model entity to another entity.

  • Resource2: The data model entity that contains the property on which you want to sort.

  • SortProperty: The property on which you want to sort.

  • sortProperty_Ext: The name you’ll use in your sort command on your endpoint to sort on the property.

Here’s a real-world example:

class UsersExtResource extends UsersCoreResource {

 private static var enSortColumnPath = Lists.newArrayList({User#Contact.getPropertyInfo() as IEntityPropertyInfo, UserContact#EmployeeNumber.PropertyInfo as IEntityPropertyInfo})


 override property get CustomSortColumnMap() : Map<String, QuerySortColumn> {
   var customSort = new HashMap<String, QuerySortColumn>(super.CustomSortColumnMap)
customSort.put("employeeNumber_Ext", new PathSortColumn(enSortColumnPath))

   return customSort
 }

}
Java

In this example, you want to sort users based on their employee number, like this:

GET /admin/v1/users?sort=employeeNumber_Ext

However, you can’t use a simple sort because the EmployeeNumber property isn’t part of the User data model entity; User retrieves that property from UserContact. So before you can sort, you need to join these two resources together. You do that by creating an ArrayList that defines how the entities will be joined:

private static var enSortColumnPath = Lists.newArrayList({User#Contact.getPropertyInfo() as IEntityPropertyInfo, UserContact#EmployeeNumber.PropertyInfo as IEntityPropertyInfo})
Java

In this example the Contact property from the User data model entity will be used to join to the UserContact entity, where the EmployeeNumber property is located.

You then pass this array to PathSortColumn to perform the sort.

new PathSortColumn(enSortColumnPath)
Java

Notice that in the simple example we showed previously you used SimpleSortColumn, passing in the resource and property, but here you need to use PathSortColumn so you can pass in an array of information about the resources required to retrieve the information.

Set a default sort order

You can specify that a collection has a default sort order. This sort order is used when the caller does not specify a sort of their own. This is done in the API's apiconfig extension file.

Every API has an apiconfig extension file named <api_collection>_ext-1.0.apiconfig.yaml. This file maps both element resource and collection resources to Gosu impl files. It can also define default sort orders for collections.

For example, the following code specifies that, by default, CustomEntitiesExt collections are sorted by expirationDate (ascending). If any elements have the same value, they are sorted by customDescription (ascending).

CustomEntitiesExt:
  defaultSort:
    - expirationDate
    - customDescription
Java

To specify a descending order, use the following syntax:

- "-<propertyName>"
Java

For example, the following code specifies that, by default, CustomEntitiesExt collections are sorted by expirationDate (descending). If any elements have the same value, they are sorted by customDescription (ascending).

CustomEntitiesExt:
  defaultSort:
    - "-expirationDate"
    - customDescription
Java