API/Queries/Attributes.md

@@ -10,7 +10,7 @@ The IronAPI supports these options:
 - [(+)columns](#columns)
 - (+)select
 - (+)as
-- order_by
+- [order_by)[#ordering]
 - group_by
 - collapse
 - having
@@ -170,26 +170,47 @@ Use `+columns` to get all columns of the base table plus certain ones from joine
 }
 ```
 
-
-# Ordering
+# Select and As
 
 RESTRICTIONS: keys and values may not contain spaces.
 
-Ordering can be defined by the "order_by" attribute:
+The "select" and "as" attribues allow even more flexibility to define exactly what should be returned than "columns" does.
+
+```JSON
+"attrs" : {
+    "join":   [ "contracts__working_time_constraint" ],
+    "select": [ {"min" : "max_hours_per_day"} ],
+    "as":     [ "min_max" ]
+}
+```
+
+Generates:
+
+```SQL
+SELECT MIN( max_hours_per_day )
+FROM contracts.collective_agreement() me
+LEFT JOIN contracts.working_time_constraint() contracts_working_time_constraint ON contracts_working_time_constraint.collective_agreement = me.id
+LIMIT 10
+```
+
+Returns one row:
+```JSON
+{
+    "min_max": "9"
+}
+```
 
-`{attrs : { "order_by" : "date_of_birth" }}`
+"+select" and "+as" are also available.
 
-`{attrs : { "order_by" : { "-asc" : "col1" }}`
+<https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/ResultSet.pm#select>
 
-`{attrs : { "order_by": ["colA", {"-asc" : "colB"}]}`
+# Ordering
 
-The API will also support [similarity](https://www.postgresql.org/docs/9.6/static/pgtrgm.html):
+Ordering can be defined by the "order_by" attribute:
 
-`{attrs : { {"order_by" : {"-similarity" : ["me.name","abc"]}}}`
 
-will be translated to DBIx::Class notation `\[ 'me.name <-> ?', "abc" ]`
 
-which in turn will be translated to sql `ORDER BY me.name <-> ? : "abc"`
+The API also supports [similarity](/API/Queries/Query-Search-Options#similarity) for sorting.
 
 Any column from a joined/prefetched table or a column selected with ["select"](#select-as) can be used.
 
@@ -198,57 +219,62 @@ More details can be found here:
 <https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/ResultSet.pm#order_by> and
 <https://metacpan.org/pod/release/ILMARI/SQL-Abstract-1.84/lib/SQL/Abstract.pm#ORDER_BY_CLAUSES>
 
-# Naming Conventions
+## Restrictions
 
-When referencing to a column, the name without the table prefix can be used as long as it is unique within
-the query. Otherwise, the table name (as used for joining the table) needs to be prefixed:
- `system__transition__pre_state.id`.
+Keys and values may not contain spaces.
 
-The table the search is based on is always referenced as "me", so to dismbiguate use the "me" prefix,
-for joined/prefetched tables use their name as a prefix:
+## Examples
 
-```JSON
-#/data/system/state/query
+`POST /data/system/entity/query`
 
+```json
 {
-    "conds" : { "system__transition__pre_state.id" : { "-between" : [1,100] } } ,
-    "attrs" : {"prefetch" : ["modifying_client", { "system__transition__pre_state" : "entity" }]}
+  "attrs": {
+    "order_by": "name"
+  }
 }
 ```
 
-# Select and As
-
-RESTRICTIONS: keys and values may not contain spaces.
-
-The "select" and "as" attribues allow even more flexibility to define exactly what should be returned than "columns" does.
+To reverse the sort order from the default ascending to descending:
 
-```JSON
-"attrs" : {
-    "join":   [ "contracts__working_time_constraint" ],
-    "select": [ {"min" : "max_hours_per_day"} ],
-    "as":     [ "min_max" ]
+`POST /data/system/entity/query`
+```json
+{
+  "attrs": {
+    "order_by": {"-desc" : "name"}
+  }
 }
 ```
 
-Generates:
+Multiple sort parameters may be listed in the order they should be applied with:
 
-```SQL
-SELECT MIN( max_hours_per_day )
-FROM contracts.collective_agreement() me
-LEFT JOIN contracts.working_time_constraint() contracts_working_time_constraint ON contracts_working_time_constraint.collective_agreement = me.id
-LIMIT 10
-```
+`POST /data/system/entity/query`
 
-Returns one row:
-```JSON
+```json
 {
-    "min_max": "9"
+  "attrs": {
+    "order_by": ["name", {"-desc" : "modification_time"}]
+  }
 }
 ```
 
-"+select" and "+as" are also available.
+Similarity;
 
-<https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/ResultSet.pm#select>
+`POST /data/system/entity/query`
+
+```json
+{
+  "attrs": {
+    "order_by": {
+      "-similarity": [
+        "me.name",
+        "constant"
+      ]
+    },
+    "columns": "me.name"
+  }
+}
+```
 
 # Collapse
 
@@ -349,3 +375,21 @@ Reply:
 Note that "prefetch" is a shortcut for "join", adding all columns from the joined related sources as "+columns" and setting "collapse" to a true value.
 
 https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/ResultSet.pm#collapse
+
+# Naming Conventions
+
+When referencing to a column, the name without the table prefix can be used as long as it is unique within
+the query. Otherwise, the table name (as used for joining the table) needs to be prefixed:
+ `system__transition__pre_state.id`.
+
+The table the search is based on is always referenced as "me", so to dismbiguate use the "me" prefix,
+for joined/prefetched tables use their name as a prefix:
+
+```JSON
+#/data/system/state/query
+
+{
+    "conds" : { "system__transition__pre_state.id" : { "-between" : [1,100] } } ,
+    "attrs" : {"prefetch" : ["modifying_client", { "system__transition__pre_state" : "entity" }]}
+}
+```