IBM Content Navigator, Version 2.0.3 Supports: CMIS for Content Manager

Sort order for saved search results

When a saved search is created, the default sort order can be specified in the query expression. You must define a default sort order to use the reserved keyword SORTBY in the query expression when users or applications change the sort order at run time. You might need to add a system attribute existence condition to your query to enable users or applications to change the sort order to sort by system attributes.

The SavedSearchAdmin tool prompts for a default sort order when the saved search is created, but the option is limited to the last modified date or unsorted. However, the query can be edited and you can enter your own query expression that includes the sort order, SORTBY, and any valid property for sorting.

Some applications override the sort order by retrieving the maximum number of search results and sorting in memory by using the sorting rules of the application. However, the full results are not sorted because the application retrieves and sorts only the first set of results up to the maximum number. Results that are not yet retrieved remain unsorted. Some applications display the search results in the order that is returned by the repository. Some applications submit a request for the repository to sort the results in a specific order. In some cases, you can select the sort order. If the application submits a sort order, the existing sort order that is defined by the query expression is replaced with the sort order specified by the application. If you specify a default sort order in the query, then applications display the results that are most relevant regardless of the configured maximum number of results. For search folders, when the application requests the folder child documents, the default sort order is used unless a sort order is specified by the application. If the application specifies a sort order for the folder child documents, the results are sorted according to the folder child documents sort order if possible.

For example, if you query for all documents and the application displays the first 500 results, then you see the first 500 random results that are sorted by the application. But those first 500 results are not sorted in context of the entire result list. However, if you define a default sort order, the first 500 results that you wanted are displayed, not the first 500 random results. Sorting by the last modified date to find the most active documents is the most commonly used default sort order. If you want to ensure that applications show the most active documents before the reaching the maximum results, specify the last modified date as the default sort order. If you do not specify a default sort order, the database usually returns the oldest or inactive documents first. Use the following query expression clause for sorting by last modified date in descending order:

SORTBY(@LASTCHANGEDTS DESCENDING)

For a query with the wildcard character * across all document types without system attributes or custom attributes, you must add a constraint that checks the existence of a system attribute. The search results are constrained to document types that have system attributes that you can sort on. For example, adding [@LASTCHANGEDTS] to the query expression conditions includes a condition that the last modified date system attribute must exist in the result. Then, you can sort by system attributes either by the default sort order or allow users or applications to change the sort order to sort on system attributes. For example, you can sort by last modified date in the query expression by default by adding SORTBY(@LASTCHANGEDTS DESCENDING). One system attribute existence condition sufficiently covers all sortable system attributes. You do not need to add more existence checks for all sortable attributes if you have one existence condition. Also, the existence of any custom attribute constrains the query sufficiently so that you can sort on system attributes. Custom attributes exist only on document types with system attributes. For example, a condition on a title attribute also constrains the query sufficiently so that you can sort on system attributes without having to add existence conditions for system attributes.

For example, a query to find all locked documents does not add sufficient constraints on the results to sort by system attributes. The query expression specifies lock status but does not specify any other system attributes. This issue is resolved by adding a system attribute existence check to the query expression. However, a query to find all documents that are created and locked by the current user adds a condition on the system attribute so that you can sort the results. You can view the predefined saved searches and their query expressions to see examples of queries that require an existence constraint. For example, predefined saved searches include Documents locked by me and Documents created by me, and the first one requires an existence constraint but the second one does not.

The limitation is caused by queries with wildcard characters that typically include /*... or ...=> * in the query expressions. There are some system item types in the repository that do not have system attributes. The repository can sort results only when an attribute exists on all results possible from that query. Therefore, you must include constraints to limit the list of possible results. If you try to sort a query by a system attribute when the query is not sufficiently constrained to sort by system attributes, you receive one of the following errors:

The CQL189 error, logged from error class ModelException.You receive this error if you run a saved search and you change the sort order to sort by a system attribute.

CQL189: The query expression is not defined correctly to
support sorting on the property property-name.
Contact the administrator of this query to revise the 
query expression and to add support for sorting.

The DGL5000A error, logged from error class DKQueryException when you use the SavedSearchAdmin tool. You receive this error if you define a query with a SORTBY clause.
```
DGL5000A: Sortby expression must evaluate to a single value.
```
The CQL173 error, logged from error class RepackagedException, and the logged cause as original error DGL5000A from error class DKQueryException. You receive this error if you edited the query expression in another application that uses IBM® CMIS for Content Manager.
```
CQL173:  The search submitted, query expression,
is not valid. You must use valid syntax.
```