The Perfion Query – SELECT

XML:

<Select languages='EN,DE' view='Virtual,CatalogRoot,Normal,Branded' />

JSON:

"Select": { "languages": "EN", "view": "Virtual,CatalogRoot,Normal,Branded" }

<Select languages='EN,DE' timezone='Central Europe Standard Time'>

"Select": { "languages": "EN", "timezone": "Central Europe Standard Time" }

<Select languages='EN,DE' rtfoutput='html'>

"Select": { "languages": "EN", "rtfoutput": "html" }

<Select languages='EN,DE' rtfoutput='html,style'>

"Select": { "languages": "EN", "rtfoutput": "html,style" }

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).

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:

1. 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.

2. 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.

3. 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.