Structured Sorts

By default, requests cannot access your data using regular sorts (regular sorts are turned off and not allowed). Regular sorts are pieces of SQL code that you can specify at runtime and send directly to the database. Allow regular sorts if you need maximum flexibility in specifying how the returned data should be sorted in GET requests.
lac53
By default, requests cannot access your data using regular sorts (regular sorts are turned off and not allowed). Regular sorts are pieces of SQL code that you can specify at runtime and send directly to the database. They are not safe and are a frequent cause of unintentional data leaks. Think of regular sorts as a prototyping tool: they are nice when you want to move fast, but not appropriate for production systems. Allow regular sorts if you need maximum flexibility in specifying how the returned data should be sorted in GET requests.
For example, if you allow regular sorts, you can specify an 
order
 argument: 
.../Customer?order=name desc
Named sorts prevent malicious persons from accessing data they are not supposed to have access to using SQL injections. 
Layer7 Live API Creator
 includes system sorts (
sysorder
) and user sorts (
userorder
) as named sorts. For most cases, require that requests access your data using system sorts. However, if you require arbitrarily complex sorts, allow requests that use user sorts.
For more information:
In this article:
 
2
Use System Sorts
Requests can specify the sort order by adding a 
sysorder
 parameter with a list of attributes optionally followed with a colon and either 
asc
 or 
desc
. If you do not specify 
asc
 or 
desc
asc
 is assumed.
System sorts (
sysorder
) have the following syntax:
.../v1/[Resource]?sysorder=([expression][,expression])
The syntax for 
expression
 is [
columnName][:modifier]
.
Modifiers
You can use the following modifiers with system sorts: 
Modifier
Description
asc
Sort in ascending order.
asc_lc
Sort in ascending order after lower casing.
You can use this modifier only with string (TEXT) attributes.
asc_uc
Sort in ascending order after upper casing.
You can use this modifier only with string (TEXT) attributes.
For example, you can sort as if all values were upper-case by specifying 
asc_uc
 .
desc
Sort in descending order.
desc_lc
Sort in descending order after lower casing.
You can use this modifier only with string (TEXT) attributes.
desc_uc
Sort in descending order after upper casing.
You can use this modifier only with string (TEXT) attributes.
For example, you can sort as if all values were upper-case by specifying 
desc_uc
 .
null_first
Sort null values first.
null_last
Sort null values last.
Example:
 
.../Customer?sysorder=(name:null_first,balance:desc)
Control the Handling of Upper and Lower Case Mixed Values
You can control specific handling of upper/lower case mixed values by repeating the attribute with the same column a second time. You can also place multiple columns in a single
sysorder
parameter or multiple 
sysorders
, each a separate URL parameter, each with one or more columns.
Example:
 
You have a table with the following rows:
MARK,Mark,mark,AMY,Amy,amy
You can have 
Layer7 Live API Creator
 return your data sorted your rows using the system sort (
sysorder
) with the following expressions in your request:
You can use the 
asc_uc
 and 
desc_uc 
 modifiers only with string (TEXT) attributes.
To return this result
Use this system sort
amy,Amy,AMY,mark,Mark,MARK
sysorder=(name:asc_uc,name:desc)
AMY,Amy,amy,MARK,Mark,mark
sysorder=(name:asc_uc,name:asc)
MARK,Mark,mark,AMY,Amy,amy
sysorder=(name:desc_uc,name:desc)
mark,Mark,MARK,amy,Amy,AMY
sysorder=(name:desc_uc,name:asc)
Add User Sorts
Add user sorts when you need a more complex sort than what system sorts afford you. User sorts (
userorder
) have the following syntax:
userorder=[Sort name]([expression])
The syntax for 
expression
 is [
columnName][:modifier]
.
Example:
 
.../v1/[Resource]?userorder=[My user sort]([expression])
Example:
 
You define a user sort named 
MySort
 with the following 
Order by
 value:
dayofweek(order_date) desc
Use this user sort in a query, for example:
.../PurchaseOrder?userorder=MySort
Follow these steps:
 
  1. With your API open, in the Create section, click 
    API Properties
    .
    The API Properties page appears.
  2. Click the 
    Sorts
     tab.
    Your user sorts are displayed in the User sorts list.
  3. Above the list of user sorts, click 
    Add
    .
  4. Complete the following fields, and then save your changes:
    Sort name
     
    The name of the user sort.
    Resources
     
    A comma-separated list of resources, tables, and views.
    Optional:
     Yes
    Order by
     
    For example:
    round({att1}) + abs({att2})
Your user sort is defined.