Docs‎ > ‎API Creator‎ > ‎Create your API Project‎ > ‎

Structured Sorts

By default, when you issue a GET request, you can specify an order argument to specify how the returned data should be sorted, for example:

.../Customer?order=name desc

There is, however, a serious problem with regular sorts: they aren't safe. Regular sorts are pieces of SQL code that get sent directly to the database. In most cases, that is fine, but malicious persons can access data they're not supposed to have access to using SQL injection.

Regular sorts are a frequent cause of unintentional data leaks. Think of regular sorts as a prototyping tool: they're quite nice when you want to move fast, but not appropriate for production systems. To protect the security of your data, turn off regular sorts and use structured sorts.

Turn Off Regular Sorts

In API Creator, select API Properties, and then select the Disallow free-form filters checkbox.

Named Sorts

There are two kinds of named sorts: system sorts and user sorts.

System Sorts

For the vast majority of sorts, you can use the built-in mechanism:
sysorder=(expression[,expression])

espression is columnName[:modifier]

modifier is asc, desc, asc_uc, desc_uc, null_first, null_last 

Example:

.../Customer?sysorder=(name:null_first,balance:desc )

To specify the sort order, add a sysorder parameter, with a list of attributes, optionally followed with a colon and either asc or desc (if unspecified, asc is assumed).

For String (TEXT) columns, you can also specify asc_uc, or desc_uc to sort as if all values were upper-case. You can control specific handling of upper/lower case mixed values by combining this with the same column a second time.

For example, given a table with the six rows:
MAX,Max,max,DAVID,David,david

You would get the following results:
 Sort Result
 sysorder=(name:asc_uc,name:desc) david,David,DAVID,max,Max,MAX
 sysorder=(name:asc_uc,name:desc) DAVID,David,david,MAX,Max,max
 sysorder=(name:desc_uc,name:desc) MAX,Max,max,DAVID,David,david
 sysorder=(name:desc_uc,name:desc) max,Max,MAX,david,David,David
Note: You can place multiple columns in a single sysorder, or multiple sysorders (each a separate URL parameter) each with one or more columns.

User Sorts

If you need a more complex sort than what the system sorts allow, define a user sort. In API Creator, click API Properties, Sorts. The following image shows how to define a user sort:
If you define a user sort named MySort with the following value:
dayofweek(order_date) desc

then you can use that sort in a query with:
.../PurchaseOrder?userorder=MySort

More Information

For more information about structgured filters, see Structured Filters.