The Perfion Query - ORDER
- Morten Lindequist Køhler
When no Order part is specified there will be no guaranteed order, the order will depend on the query plan.
The Order part of the query is written in the following manner:
XML:
<Order mode='Hierarchical'>
<By id='Brand' />
<By id='PowerW' direction='desc' />
<By id='String(EN)' />
</Order>JSON:
"Order": {
"Mode": "Hierarchical"
"By": [
{ "id": "Brand" },
{ "id": "PowerW", "direction": "desc" },
{ "id": "String(EN)" }
]
}Introduced in version 4.7.5, a mode-attribute is made available on the Order-element. This mode controls whether or not you want the sorting to take the item hierarchy into account. Usually you are not interested in that so ‘Flat’ is default if no mode is supplied.
mode | Valid values are ‘Flat’ or ‘Hierarchically’. Default is ‘Flat’. |
The following attributes/properties are available for the <By/> element or "By": [ ] array:
id | The Name, ID or Property of a feature on the Items in the From part of the clause. |
direction | Optional. When not specified as ‘desc’ it defaults to ascending order. |
In the same way as for Clauses, the ID specification may contain a Language specification, which is interpreted as follows:
id='String' | The first language specified by the language attribute/property on the Select part of the query is used. |
id='String(EN)' | The specified language is used. |
id='String(*)' | No specific language is used |
From above, when e.g. no specific language is used, and values exist for multiple languages, then the value with the “lowest” alpha-numeric value will determine the order, e.g. the English color ‘Black’ will dominate over the German ‘Schwarz’ when ordering is ascending and the other way around if ordering is descending.
Ordering using a relation to another feature
In addition to ordering items using feature values you can also order items using the “Sortable Related Items”-property you can set on selectable features. The “Sortable Related Items” can be set when defining a feature to allow items of any related feature to be ordered depending on the value of Items of the original feature. That was pretty abstract, so let us take an example (the example is made based on the Coffee Demo-database shipped with Perfion).
The “Catalog”-feature in this database has “Sortable Related Items” checked, as seen below:
This means that Items of another feature, say “Product”, related to an Item of feature Catalog, can be sorted depending on the Catalog. Simpler stated: Products related to some Catalog may be sorted depending on the catalog. Consider the following screenshot. Here Catalog “Mixed products on sale” contains 3 products. The order in which these products are sorted is indeed depending on the Catalog selected. The red cup comes before the Sand-colored Coffee Maker which again is before the Chrome-colored Coffee-maker.
Picking another catalog, adding the same 3 products allows you to sort them differently there. Changing the order in the GUI is simple. Just click the product you want to move and press ALT-Up or ALT-Down to move it. Note: This only works, if the left-side feature (here Catalog) has the “Sortable Related Items” checked).
When querying products you get the “related order” out for all feature-values having “Sortable Related Items” checked. A query selecting all product where catalog is “Mixed products on sale” gives the following result (three dots indicates part of the response removed for brevity):
<?xml version="1.0" encoding="utf-8"?>
<Data totalExecutionTime="00:00:00.1269285" totalCount="3">
<Features>
<Product id="100" language="EN" caption="Product" ... />
<ItemName id="101" language="EN" caption="Item Name" ... />
<Image id="105" language="EN" caption="Image" ... />
<Color id="210" language="EN" caption="Color" ... />
<Catalog id="104" language="EN" caption="Catalog" ... />
</Features>
<Product id="560" parentId="559" brand="Normal" orer="0" ...>
<Value seq="0" viewOrder="0" ...>1923-16</Value>
<Color language="EN" id="598" ...>Chrome</Color>
...
<Catalog language="EN" id="2809" ... relatedOrder="7" ...>Mixed products on sale</Catalog>
</Product>
<Product id="566" parentId="559" brand="Normal" order="0" ...>
<Value seq="0" viewOrder="0" ...>1923-18</Value>
<Color language="EN" id="598" ...>Sand</Color>
...
<Catalog language="EN" id="2809" ... relatedOrder="6" ...>Mixed products on sale</Catalog>
</Product>
<Product id="653" parentId="711" brand="Normal" order="0" ...>
<Value seq="0" viewOrder="0" ...>40511-565-0</Value>
<Color language="EN" id="598" ...>Red</Color>
...
<Catalog language="EN" id="2809" ... relatedOrder="5" ...>Mixed products on sale</Catalog>
</Product>
</Data>Notice that the “relatedOrder”-attribute and how “relatedOrder” has the least value for the “Cup”, namely 5, then comes the Sand-colored coffee maker with a 6 and finally the Chrome-colored coffee maker with a 7. That is, related order is numerically ordered as the products in Perfion. Notice further, related order not necessarily starts with a 1, followed by 2 etc. For optimization reasons they are only ordered numerically, not necessarily starting from 1 incrementing 1 per next product.
As seen, the products are not ordered by “related order” when output per default. You can “Order by” related order as seen in the following query:
Notice here the order by clause. That will order the products by their catalog feature and in respect to the item with id “2809”, which is the “Mixed products on sale“-catalog). Remember that another catalog might have its products sorted differently and maybe even hold different products. The above “related order by clause” therefore really only make sense in conjunction with a where-clause as shown restricting to items only in the catalog. It may restrict further, but should never include products not in the catalog ordered by. If the query finds products not being related to the ordered by feature it is undetermined where these products end in the result.
Below shows the result of the above query as shown in the API Toolbar-window: