The Perfion Query – SELECT
- Morten Lindequist Køhler
- lkj
XML:
<Select languages='EN,DE' view='Virtual,CatalogRoot,Normal,Branded' />JSON:
"Select": { "languages": "EN", "view": "Virtual,CatalogRoot,Normal,Branded" }languages | Specifies the languages to be returned (above selects English and German). Specifying '*' resolves to all active languages. |
view | Specifies the types of Items to be returned (can be any single or multiple combination of Virtual, CatalogRoot, Normal or Branded. If e.g. only Normal Items (e.g. Real Products) are wanted, only Normal should be specified. |
Index | Used for Paging through a larger result-set. 0-indexed position of the first Item in the result-set. (default: 0) |
maxCount | Used for Paging through a larger result-set. The maximum number of Items to retrieve, counting from the position specified by the index attribute. (default: everything) |
options | Specifies special Query execution behavior. The following options are available and can be combined (separated by comma): IncludeDebugInfo In addition to the Total Execution time, this option includes Perfion API version and additional Query timing information for time used by pure SQL, retrieval of remote values and other internal data filling or configuration tasks. IncludeTotalCount Includes the total number of found Items matched by the Where clause. Useful when Paging through the result-set. May incur extra execution time for large result-sets. IncludeChildCount Includes a ‘childCount’ attribute value for each Item in the result-set, with the number of child items that have the Item as their parent (Even if these are not contained in the result set). IncludeVisibleChildCount Includes a ‘visableChildCount’ attribute value for each Item in the result-set, with the number of child items that have the Item as their parent and included in the result set. (Introduced from Perfion 2024-R1) This is very useful for e.g. a web-solution for navigating through large hierarchical result-sets, so navigation trees or menu Items know whether they can unfold to display sub-items (lazy- loaded when and only if they are needed). IncludeFeatureViewOrder Includes a ‘viewOrder’ attribute value for all feature elements in the result-set, specifying the appearance ordering of features for each of the individual Items. This is very useful for e.g. a web-solution, making it possible to design fully generic lists and detailed product or item pages with varying information and order of appearance. RemoveUnwantedFeaturesByConfiguration The normal behavior of the Query during retrieval of a result-set containing more than 1 Item, is to determine what features are relevant for any of the Items in the result-set. If a feature is relevant for 1 Item it will be retrieved for all other Items also, such that the entire result-set can be displayed in a list with the same columns available for all Items. When specifying this option, each Item will be treated individually, and only the feature values that are specified to appear for each Item will be retrieved. If presented side-by-side this means that some Items may not display values for some features even though they might have values defined for these features. Which features to display values for is determined by the Configuration of a feature. The exact behavior of this option is determined by wildcard selection of features in combination with the ‘view’ attribute on the Select <Feature> element. This is very useful for e.g. a web-solution, making it possible to design fully generic lists and detailed product or item pages with varying information and order of appearance. IncludeControlInfo Includes additional meta data about the features. Behavior A bitmap encoded value of the feature properties, see table. IsRemote - 0x00000001 Inheritable - 0x00000002 LocalizableValues - 0x00000004 MultiValues - 0x00000008 SelectValues - 0x00000010 AllowHierarchy - 0x00000020 CanOrderValues - 0x00000040 CanOrderItems - 0x00000080 CanOrderRelatedItems - 0x00000100 AllowFormula - 0x00000200 AllowTriStateBoolean - 0x00000400 Example: behavior=”48”. Converted to hex, 48 is 30. Therefore 48 is Select Values (0x00000010) + Allow Hierarchy. Control Default - ”1937D3DA-48F1-41ae-9D0E-BC723CC30176” PEditMultiSelector - “3D46A890-AF1B-4132-8189-2A22B4313A4F” PeditMultiSelectorWithLateLoad - “A4D9101A-50EA-4CBF-88DC-1C620AB4A7E4” PeditMultiChecked - “B95D61D4-34C6-4b25-87BA-7B888C1B0626” PeditTextInline - “78108DC3-9FA2-402f-A6CB-FC22263F9139” PeditSingleSelectTree - “791C8A93-8244-45e2-9A04-88DF275E4D87” PeditSingleSelectTreeWithLateLoad - “823ED7BF-FEE0-42F3-95CA-D00F10C1ACA6” PeditSingleSelectTreeWithPagedLateLoad - “F4C09A76-C7DD-4382-A75b-E0740BD2CEC8” PeditRichText - “E996D0B3-1616-4061-AA49-A743DE6D4732” PeditFraction - “6449F802-392C-4713-8628-4A28D5663374” PeditProgres - “1B94B89D-8B50-4519-9193-CE01BCEB4B20” PeditFormula - “7DF10D10-635F-4A18-BD7E-1240A2BEA85F” PeditSymbol - “2076E3F7-EACA-4922-B203-7FA48BFD4A3A” PeditRtfEditor - “8E54C201-26A4-4041-8954-89B4D02D2256” PeditToggleSwitch - “DA2A75E7-F409-42D0-B552-9B84DA3A18C2” ViewType and FilterType The feature ID of the assigned Views and Filters containers assigned to the feature ExpandClusterQueries Includes all columns for clustered remote features. ExcludeFeatureDefinitions This option will, from version 5.2, exclude the feature definition metadata which is normally included in the <features> element in the response. IncludeConfiguredSwapRelations From version 2024 R1 this option will ensure that all swapped features fetched will be fetched “switching direction”. See more in section “Select Feature as Swap Relation Direction”. OnlyShowSwapRelationMarkers From version 2024 R1 this option will ensure that all swapped features fetched will not have their data, but only a marker indicating that there is data. See more in section “Select Feature as Swap Relation Direction”. |
timezone | From version 4.7 of Perfion the query supports returning all timestamp in the supplied time zone. Time zones supported are all time zones supported by the Windows machine executing the query. These time zones are listed here (and they are pretty static): https://support.microsoft.com/en-us/help/973627/microsoft-time-zone-index-values Note that it is the id of the Time Zone (called “Name of Time Zone” in above page) that needs to be passed into the timezone-attribute, as for example: XML: <Select languages='EN,DE' timezone='Central Europe Standard Time'>JSON: "Select": { "languages": "EN", "timezone": "Central Europe Standard Time" }Furthermore Perfion has built in 3 time zone names that are also supported by the timezone-attribute:
Using the time zone not only affects the output timestamps but also any clause using timestamps (“*.ModfiedDate for example). The time zone attribute is also supported by update- and delete-statements (there is no need for it on insert-statements, since these have no clauses). |
rtfoutput | From version 4.7 of Perfion the query supports outputting Rich Text Format values which is generated when using the Rtf Editor on Text features. The rtf text can also be outputted as enriched HTML, which includes CSS. XML: <Select languages='EN,DE' rtfoutput='html'>JSON: "Select": { "languages": "EN", "rtfoutput": "html" }The embedded CSS can be outputted in two ways; ‘inline’ or ‘style’. Inline is chosen as default, if no parameter is supplied: XML: <Select languages='EN,DE' rtfoutput='html,style'>JSON: "Select": { "languages": "EN", "rtfoutput": "html,style" }Supplying the inline-parameter will insert the CSS in the HTML-tags, whereas supplying the style-parameter will embed the CSS as a stylesheet at the top of the HTML value. |
Select Specific Features
XML:
<Feature id='ItemNumber' />
<Feature id='Image' />The id attribute of the <Feature> elements, specify the name of a feature (or alternatively the unique ID number of a feature).
JSON:
"Features": [
{ "id": "ItemNumber" },
{ "id": "Image" }
]The id property of each object in the array Features, specify the name of a feature (or alternatively the unique ID number of a feature).
Select Many Features: All features
XML:
<Feature id='*' />JSON:
"Features": [
{ "id": "*" }
]Setting the id attribute/property to the wildcard * character will return all the feature values stored and managed directly by Perfion. All such feature values are fetched regardless of whether they are in the configuration for the “From” feature or not.
Select Many Features: Scoping to a Dynamic View
XML:
<Feature id='*' view='<some view>' />JSON:
"Features": [
{ "id": "*", "view": "<some view>" }
]Including a ‘view’ attribute/property, makes it possible to select all features that are part of the configuration and have a check mark in the picked view. As shown in the below example we have a configuration in which 5 features have been added to the “Item” view. These features are: “Item Name”, “Image”, “Brand”, “Color” and “New”.
Using a view in a query will look like this:
XML:
<Feature id='*' view='Item' />JSON:
"Features": [
{ "id": "*", "view": "Item" }
]It will get exactly these five features. The following dynamic views are available: Family, Group, Item, Variant, Detail, Lookup, Table, Compare, Layout, Need, Nice, WebList, WebDetail and WebVariant. These can all be found in the Perfion Client. Checking all of the 4 View-buttons in the configuration. In the dialog above only the “Family”-button is checked so only 5 of the available dynamic views are seen.
In addition to the views above, you can specify “Config” as the value of the view-attribute like this:
XML:
<Feature id='*' view='Config' />JSON:
"Features": [
{ "id": "*", "view": "Config" }
]When doing that Perfion will include all features that are configured on the From-feature, i.e. it works like a dynamic view that happens to have all features checked.
Fetching remotes using ** as Feature element ID
Setting the id attribute/property to a super wildcard ** (double star), in combination with a view, will also retrieve of all Remote features included in the view (or entire configuration, if “Config” is specified).
NOTE: Remote features will only be fetched using a double star in combination with a view, “Config” or at least one group. Specifying only a double star without specifying a view (or group as described below) will not retrieve any Remotes.
Important
Complex Remote features (based on cluster queries), such as e.g. price variants, only return the values according to their default specification in the remote query (when included by wildcard technique described above).
To control exactly which Remote feature variants are returned by the query, the specific Remote features must be included explicitly in the query and include further specification of variants, parameters and columns (as described in more detail under Select Remote Variants).
Select Many Features: Scoping to a group
From Perfion 4.5 2018 R3 a wild card feature element can also be scoped to a group instead of (or in addition to) a view.
In this case instead of view name we have to use group ID which is a GUID.
XML:
<Feature id='*' view='b1f79f06-1923-4e54-8414-fadf2cbb5814' />JSON:
"Features": [
{ "id": "*", "view": "b1f79f06-1923-4e54-8414-fadf2cbb5814" }
]This element/object will fetch all configured features that are in the group identified by the supplied group GUID. In this case, it is the ID of the group “Technical Specifications” in the standard Coffee Demo Database.
In Perfion, we have 3 kinds of groups: Top View Groups, View Groups and Security Groups. Each of these can be addressed in the wild card element by using the view-attribute as shown above. You can even combine them, so the following wild card feature-element/object will select all features that are both in group “Marketing” and group “Technical Specifications”:
XML:
<Feature id='*' view='b1f79f06-1923-4e54-8414-fadf2cbb5814 * 79b96ce3-20f3-4f66-b24c-49a7806bb497' />JSON:
"Features": [
{ "id": "*", "view": "b1f79f06-1923-4e54-8414-fadf2cbb5814 * 79b96ce3-20f3-4f66-b24c-49a7806bb497" }
]This corresponds to finding the intersection of the features in each of the groups. Note that it is a * (star) that is used to denote the set-operation. If you instead want the union of these two features-sets, simply add two wild card feature-elements/objects as the following example shows:
XML:
<Feature id='*' view ='b1f79f06-1923-4e54-8414-fadf2cbb5814' />
<Feature id='*' view ='79b96ce3-20f3-4f66-b24c-49a7806bb497' />JSON:
"Features": [
{ "id": "*", "view": "b1f79f06-1923-4e54-8414-fadf2cbb5814" },
{ "id": "*", "view": "79b96ce3-20f3-4f66-b24c-49a7806bb497" }
]It is additionally allowed to combine groups with views. Consider these wild card elements:
XML:
<Feature id='*' view='Item' />
<Feature id='*' view='b1f79f06-1923-4e54-8414-fadf2cbb5814' />JSON:
"Features": [
{ "id": "*", "view": "Item" },
{ "id": "*", "view": "b1f79f06-1923-4e54-8414-fadf2cbb5814" }
]The above will fetch all features that are either in the View “Item” or in the Group denoted by the shown GUID (which happens to be “Technical Specifications” in the Coffee Demo Database). Same as before, multiple wild card features will take the union of the respective sets of features.
You can also query for the intersection of features in the above two set. The following query specifying both the group-GUID and view in the same element will take Perfion to take the intersection of the features sets denoted by each.
XML:
<Feature id='*' view='Item * b1f79f06-1923-4e54-8414-fadf2cbb5814' />JSON:
"Features": [ { "id": "*", "view": "Item * b1f79f06-1923-4e54-8414-fadf2cbb5814" } ]Select Many Features: Scoping to a Customized View
In the same way you can select many features by scoping to a Dynamic View, you can also select many features by scoping to a Customized View.
XML:
<Feature id='*' view='<some customized view name or ID>' />JSON:
"Features": [
{ "id": "*", "view": "<some customized view name or ID>" }
]There are two types of customized views:
Base feature related custom views. These are the Search type feature items which are directly related to the base feature which is defined in From-part of the query.
Global custom views. These are any Search type feature items.
Base feature related custom views
The base feature referenced in the FROM-part of the query must have a “Views Feature” set up in its definition:
With a “Views Feature” set up, it is possible to scope a query to any of the views defined on this feature:
The base feature related custom view will be prioritized over the global custom views if the base feature have “Views Feature” setup. So in case there are 2 views which have the same name (Search type feature item base value), then it will be the base feature related custom view which will be selected as correct view to be used.
Global custom views
Global custom views are all available views in Perfion which are defined as Search type feature items.
Custom view usage
Using a Customized View in a query will look like this:
XML:
<Feature id='*' view='Overview' />JSON:
"Features": [
{ "id": "*", "view": "Overview" }
]It will get exactly the features defined in the WHERE-part of the API query behind the Overview item.
The view can also be selected using view ID which in this case is Search type feature item ID.
Custom view handling
Custom view when defined will be handled based on how views are configured for the base feature and also if the custom view is defined using view ID. The priority of selecting custom views is as shown below:
If the view name is an integer number, then the view will be treated as view ID. If the view with given ID is found, then that view will be selected.
If the base feature has “Views Feature” setup, then the name of the view will be searched in the scope of that defined Search type views feature only to find a match. If the match is found, then the view will be selected.
The view will be search among any existing Search type feature items.
The views in Perfion can be named in many ways and using various characters, but when they are referenced in Perfion API, there are limitations how the view can be referenced and which views are valid. Views validation and naming rules are as follows:
The view name must be a single word without any spaces.
The view name must be an alphanumeric string, e.g. it should not contain various special characters.
There should be no duplicate views. It depends how the views are selected, but if one chooses to use a view name which exist in multiple views in Perfion, then the view will be rejected and API query request will fail with error.
The query defined for selected view must be valid. If the view query cannot be parsed for some reason, then the view will be rejected and API query request will fail with error.
Select Expressions
For data exports to portals or external design tools it is important to be able to format the data, since these external design tools cannot format numbers and dates etc. – they treat all data as strings and will present the data as they are received.
Expressions can be used to format the values of a feature. This can make sense in many situations where e.g. a number must be formatted in a certain way, multi-values must be merged together into a single value, the value of a composite must be calculated by combining several other feature values and their metadata etc.
The following expression returns all the names of the Multi-valued feature Actors separated by comma and a space.
XML:
<Feature id='Actors'>
<Expression id='List' expression='@FormatMultiValues({Value}|, )'/>
</Feature>JSON:
"features": [
{
"id": "Actors",
"expressions": [
{ "id": "List", "expression": "@FormatMultiValues({Value}|, )" }
]
}
]The output from the above query could look like this:
XML:
<Item>
<Actors id="2748">Cameron Diaz</Actors>
<Actors id="2750">Mel Gibson</Actors>
<Actors id="2749">Sandra Bullock</Actors>
<Actors expression="List">Cameron Diaz, Mel Gibson, Sandra Bullock</Actors>
</Item>JSON:
"Items": [
{
"Values": [
{ "id": 2748,"value": "Cameron Diaz" },
{ "id": 2750,"value": "Mel Gibson" },
{ "id": 2749, "value": "Sandra Bullock" },
{ "expression": "List", "value": "Cameron Diaz, Mel Gibson, Sandra Bullock" }
]
}
]Select Remote Variants
Returning values for remote variants such as e.g. customer specific prices or prices in specific currencies etc. is done by the following syntax:
XML:
<Feature id='Price'>
<Parameter id='PriceDate' dataType='Date'>2008-09-12 10:45:00</Parameter>
<Variant>EUR-ret</Variant>
<Variant>EUR-net</Variant>
<Column id='Currency'/>
</Feature>JSON:
...
"features": [
{
"id": "Price",
"variants": [ "EUR-ret", "EUR-net" ],
"columns": [ { "id": "Currency" } ]
}
]
...Parameter refers to a parameter named ‘PriceDate’ and of DataType Date which is defined and used in the remote query defined in Perfion used to retrieve the remote values. The value of the Parameter is set to the parameter in the remote query before execution. As many parameters can be defined as needed. Please note that you can currently not set parameters via JSON queries.
The parameters for remote feature can be defined only for those parameter values which are defined in the remote query. Any parameter which does not match existing parameter will be ignored.
There are 2 types of remote parameters:
Standard remote parameters. This type of parameters must be defined exactly like they are defined for remotes in Setup XML document and they must match existing “Custom” and/or “Replace” type parameters. The only exception is that ‘dataType’ attribute is mandatory for this type parameter and must match the original remote parameter data type. Refer to Remotes - Reference Guide for more information how to defined these parameters. Below you can see an example of how various types of remote parameters are defined in remote query Setup XML. Note that “Replace” parameter in the example is defined without ‘dataType’ attribute, but in order to overwrite it from Perfion API query it must be added and set to value ‘string’.
Special remote parameters. These parameters are also known as Output Parameters and they are used e.g. when defining extra parameters for generating reports. These parameters have more features, but not all of them may be supported by remotes. The most important data when defining such parameters is that parameter ID must match the ID of the existing remote parameter which is defined in remote query Setup XML. Refer to Parameters for more information how to defined these parameters. Below you can see an example how these special parameters are defined.
Variant is similar to Parameter, but is a special parameter that can be multi-valued and is known by Perfion to be the variant. A unique key (e.g. an ItemNumber) in combination with the variant should in combination always represent unique rows in the returned remote values.
Column defines the name of an additional informative column value returned from the remote data source that also should be included in the result set for the specified feature. As many additional columns can be defined as needed. Wildcard '*' can be specified to add all columns.
Select Feature as Swap Relation Direction
Setting the attribute/property “type” to “Relation” will return the Swap Relation Direction base values in the XML result. If the “view” attribute/property is set, the result will include all features in the “view”. (Introduced from Perfion 2023-R1-SR1)
XML:
<Feature id='LightConfiguration' type='Relation' />
<Feature id='LightConfiguration' type='Relation' view='Config'/>JSON:
{ "id": "LightConfiguration","type": "Relation" }
{ "id": "LightConfiguration","type": "Relation", "view": "Config" }.It only makes sense to use this attribute/property on features which have a relation to the feature in the FROM section.
The result will include Items form LightConfiguration in a LightConfiguration Element/Property.
XML:
JSON:
The above syntax requires you to query each individual Swapped Feature, here LightConfiguration. From version 2024-R1 it is possible to use the select-option IncludeConfiguredSwapRelations to fetch all features marked as a swapped feature in the configuration as if marked with “type='relation”. The syntax in XML is the following:
<Query>
<Select languages='EN' options='IncludeConfiguredSwapRelations'>
<Feature id='*' view='Item'/>
</Select>
<From id='Product' />
...
</Query>
The above query will automatically fetch all swapped features in the configuration of the Product-feature for the Item-view. Perfion will always use the Config-view when fetching swapped features using this option. If you for some swapped features want to fetch its features using another view, an explicit fetch marked with type “relation” is needed. Note that the option can be combined explicit selects of individual swapped features.
When fetching swapped features it is possible to only retrieve whether or not a swapped feature holds data or not. The option OnlyShowSwapRelationMarkers instructs Perfion to only output whether or not swapped features hold data, but not the data itself. So fetching the LightConfiguration-feature using this option will instead just return the following, her shown for two sample products:
Note that instead of returning the “LightConfiguration” for the product, Perfion instead returns a placeholder for it having the attribute “data” set to “1”. In case there are no swapped data for some product, nothing will be output, as is also the case when outputting non-swapped features not holding any data. As can be seen in the above example, the product with id 2282 indeed holds data for swapped feature “LightConfiguration” while this is not the case for product with id 2283.