The Perfion Query - WHERE

The Where part is built up from a list of clauses that are AND’ed together in the following manner:

XML:

<Where>
    <Clause id='Category.ID' operator='=' value='3' />
    <Clause id='NoOfCups' operator='=' value='6' />
    <Clause id='PowerW' operator='BETWEEN'>
        <Value>100</Value>
        <Value>300</Value>
    </Clause>
</Where>

JSON:

"Where": {
    "Clauses": [
        { "Clause": { "id": "Category.ID", "operator": "=", "value": "3" } }
        { "Clause": { "id": "NoOfCups", "operator": "=", "value": "6" } }
        { "Clause": { "id": "PowerW", "operator": "BETWEEN", "value": [ "100", "300" ] } }
    ]
}

Features

Each feature can be written as either the unique featurename or the unique featureID. It is possible to change the feature name in Perfion so the featureID is more safe to use, but using the name makes it considerably easier to read and write the queries.

When more features are written after one another, they refer to the values of features on features such as e.g. Actor.Age>35.

When no features are written the clause refers to the type of items being returned, i.e. the FROM features e.g. the Product name. In this case a Property must be specified e.g. String='1923-18'

Wildcard features

Instead of naming a single feature explicitly to search for the same value across several features by using a wildcard instead of the feature name, such as:

*.Number>100 (In case the retrieved Items are Products , this would mean any feature on Products that has a numeric value greater than 100, but it could also be written as Material.*.Number>100 (meaning any products from Manufacturers that have any numeric feature with values greater than 100).

Instead of using wildcards, it is also possible to specify exactly which features to search, e.g.

(105,200,265).Number>100 (meaning any Products that have a numeric value greater than 100, on the features with ID 105, 200 or 265. For this special notation the feature ID values must be used, the feature names are not supported.

Properties

For a feature such as Actor, its datatype would most likely be defined as a string whereas a feature such as Age would be a Number. By writing the feature Actor it would then be interpreted as a string value and Age as a Number value. However, for all features it is possible to explicitly access the following properties:

String, Number, Text, Date, BinaryID

In many cases it is irrelevant to write which property to use since it is derived automatically from the feature, e.g. writing; Actor='Gibson' or Actor.String='Gibson' has the same meaning since the feature Actor is defined as datatype string. For the feature Actor it does not make sense to access any of the other properties since they give no meaning.

For some features it does give meaning to access the properties directly. When working with images or files they consist of both the binary file and a file name. In these cases these values can be accessed as Image.String='filename.jpg' and Image.BinaryID='<Guid>' where the Guid is the unique identity used to reference the binary file.

StringText

The StringText property combines the String and Text properties for searching purposes, and is mostly relevant when doing Feature Wildcard searches across multiple features such as e.g.

*.StringText LIKE '%ST%'

This would find any Product that contains the value Audi on any feature irregardless of whether it is a String or Text feature.

ModifiedBy, ModifiedDate, CreatedBy, CreatedDate

These properties would f.x. make it possible to find all products that have values modified since a specific date. The ModifiedBy and ModiFiedDate properties can be applied anywhere.

The CreatedBy and CreatedDate Properties can be applied anywhere, with the exception that they cannot follow non-selectable features such as e.g. ItemNumber.CreatedBy='Admin'. In this case simply write: CreatedBy='Admin' which would yield all Items that were created by the specified user.

ModifiedDate can be used to find both all items modified at some point or items where some spec is modified at some point. Consider the following examples:

<Clause id="ModifiedDate" operator="BETWEEN"> 
  <Value>2020-01-03 10:00:00</Value> 
  <Value>2020-01-03 11:00:00</Value> 
</Clause> 

The above clause will match any item modified between 10:00 and 11:00, January 3rd in 2020. And by modified it means that any feature value (Spec) has been either inserted/updated/deleted.

<Clause id="ItemNumber.ModifiedDate" operator="BETWEEN"> 
  <Value>2020-01-03 10:00:00</Value> 
  <Value>2020-01-03 11:00:00</Value> 
</Clause> 

The above clause will find all items on which their ItemNumber has either been updated or inserted between 10:00 and 11:00. Notice that it will not find items in which their ItemNumber has been deleted.

For a selectable feature, say Color, the query must be written slightly different:

<Clause id="Color.:ModifiedDate" operator="BETWEEN"> 
  <Value>2020-01-03 10:00:00</Value> 
  <Value>2020-01-03 11:00:00</Value> 
</Clause> 

Notice the extra colon in between Color and ModifiedDate. The above will find items that either have some Color updated or inserted. It could be a product that where once color “Red” was picked but now has been changed to instead point to “Red”.

If you want to find all items in which their selectable values have changed. Not the link as above, but rather the Item (here the Color pointed to), you would write:

<Clause id="Color.ModifiedDate" operator="BETWEEN"> 
  <Value>2020-01-03 10:00:00</Value> 
  <Value>2020-01-03 11:00:00</Value> 
</Clause>

As can be seen here the colon simply is removed. This will find all Items in which their Colors pointed to have changed. And by changed it means the item or any spec on it has changed.

Typically you would combine the two, i.e. you will select all items where Color has been changed regardless of where the change happened (the item or the item referred to). This is done like this:

<Clause id="Color.ModifiedDate" operator="BETWEEN"> 
  <Value>2020-01-03 10:00:00</Value> 
  <Value>2020-01-03 11:00:00</Value> 
</Clause>
<OR />
<Clause id="Color.:ModifiedDate" operator="BETWEEN"> 
  <Value>2020-01-03 10:00:00</Value> 
  <Value>2020-01-03 11:00:00</Value> 
</Clause>

Finally you can select all items upon which any feature has changed (i.e. been updated or inserted, but not deleted). This is done using a star (*) instead of a feature name like this:

<Clause id="*.ModifiedDate" operator="BETWEEN"> 
  <Value>2020-01-03 10:00:00</Value> 
  <Value>2020-01-03 11:00:00</Value> 
</Clause> 

Please note, that when it comes to remote features Perfion has no knowledge of when they were created and modified.

ID, ParentID, Brand

For Selectable features that have their own identities and can be attributed with other features themselves, the following extra identity properties are available:

This makes it possible to query against the ID of the actual items as well as their ParentID, which will probably be the preferred approach for programmatic control e.g. for web sites or other solutions, since data resultsets will always include the ID and ParentID of all items.

ParentID=0 (will only return Items in the root, i.e. they have no parents)

Furthermore it is also possible to specify which types of Items (e.g. Normal, Virtual, CatalogRoot or Branded ) to include in the query in the following manner:

Brand='Normal' (will only return normal items)

Languages

Using languages in clauses are always optional, and can only be relevant for localizable features. The default behavior is to search for a value in any language. If the query should only take values from 1 or more languages into account it is possible to do this by including the language codes as in the following example:

Color(EN,DE) LIKE 'Bl%'

This example will match all values from English and German language with Color values that starts with ‘Bl’ in their name such as Blue or Blau.

Operators

The Perfion query syntax offers the following operators.

The operators will be described with examples below.

LIKE, NOT LIKE, =, !=, <, >, <=, >=

These operators are well known from normal SQL syntax and behave the same. The syntax for writing the Perfion clause looks like the following example:

XML:

<Clause id='PowerW' operator='>=' value='170' />

JSON:

{ "Clause": { "id": "PowerW", "operator": ">=", "value": "170" } }

The LIKE operator behaves exactly like the well known LIKE operator from normal SQL syntax, with the possibility of using % for Wildcard searches.

MATCH, NOT MATCH

The MATCH operator is similar to the LIKE operator in that it can be used to perform wildcard searches. The primary difference between the 2 are, that normal users do not easily understand the LIKE behavior with explicit use of % as wildcard, but assumes that it is possible to just type a part of a word, or multiple words, and get some results.

Therefore, the MATCH operator automatically surrounds the search value with % wildcards (invisible to the user). But the MATCH operator goes even further, to split up multiple words into multiple clauses. The following table illustrates various searches and how they are interpreted:

IN, NOT IN

The syntax for using an IN clause (with multiple search values) must be written as in the following example:

XML:

<Clause id='ID' operator='IN'>
    <Value>119</Value>
    <Value>137</Value>
</Clause>

JSON:

{ "Clause": { "id": "ID", "operator": "IN", "value": [ "119", "137" ] } }

BETWEEN

The syntax for using a BETWEEN clause must be written as in the following example:

XML:

<Clause id='PowerW' operator='BETWEEN'>
    <Value>100</Value>
    <Value>300</Value>
</Clause>

JSON:

{ "Clause": { "id": "PowerW", "operator": "BETWEEN", "value": [ "100", "300" ] } }

Both of the Value elements must always be present. Leaving one of them blank will make the BETWEEN clause behave as either > or < instead.

HAS, NOT HAS

The syntax is shown by the following example.

XML:

<Clause id='PowerW' operator='HAS' />

JSON:

{ { "Clause": { "id": "PowerW", "operator": "HAS" } }

The above example will find all items that has a value defined for the PowerW. NOT HAS will find all items that does not have a value defined for the specified feature.

EXECUTE

The EXECUTE operator makes it possible to execute predefined behavior. This is a very powerful ability, which makes it fast and easy to create much more advanced functionality (that would otherwise be too complicated for normal day-to-day users to figure out), or impossible to defined via the easy-to-use GUI filters.

<Clause id='FavoriteSearches' operator='EXECUTE' value='FindSpecialItems' />

The syntax is shown by the following example.

XML:

<Clause id='FavoriteSearches' operator='EXECUTE' value='FindSpecialItems'/>

JSON:

{ "Clause": { "id": "FavoriteSearches", "operator": "EXECUTE", "value": "FindSpecialItems" } }

The above example will find an Item named ‘FindSpecialItems’ from the feature named ‘FavoriteSearches’, retrieve the Text property value of this Item. In this case, the Text property value must contain a valid Perfion query, and the entire Clause part of this query will be added instead of the above Clause Element.

No retrictions apply to the embedded queries. This means, that it is possible to embed queries into other predefined queries recursively, and thereby reuse predefined queries in many powerful ways.

If a predefined query is added in the GUI search filter with the EXECUTE operator, the clauses of the predefined query is added to any other search criteria chosen by the user, which make it possible to expose the predefined queries in a way that is very easy to understand for the users.

ISINVALID

The ISINVALID operator makes it possible to query items having invalid feature values. An invalid value can either be due to the feature having validation rules being violated or due to Filtered Values configuration being violated.

The id of the clause specifies which feature(s) to check validation for. The value of the clause is not used.

Using the ISINVALID operator together with other operators inside a nested AND/OR structure is not allowed.

The syntax is shown by the following example.

XML:

<Clause id='Color' operator='ISINVALID'/>

JSON:

{ "Clause": { "id": "Color", "operator": "ISINVALID" } }

ISINVALID wildcard features

Wildcard features can be used to search for invalid values across several features. The wildcard can either be * for all features relevant or a comma separated list of feature IDs. Wildcard features are specified either for features having validation rules using the _FeatureValidation keyword or for features controlled by a Filtered Value configuration using the _FilteredValues keyword.

The syntax for wildcard feature usage for features having validation rules is shown by the following example.

XML:

<Clause id='(1107,1278)._FeatureValidation' operator='ISINVALID'/>

JSON:

{ "Clause": { "id": "(1107,1278)._FeatureValidation", "operator": "ISINVALID" } }

In the example features having ID 1107 and 1278 are checked for values violating a feature validation rule.

The syntax for wildcard feature usage for features being controlled by a Filtered Values configuration is shown by the following example.

XML:

<Clause id='*._FilteredValues' operator='ISINVALID'/>

JSON:

{ "Clause": { "id": "*._FilteredValues", "operator": "ISINVALID" } }

In the example all features being part of a Filtered Values configuration are checked for invalid values.

Boolean

The Boolean data type can use “1” or “true” as true and “0” or “false” as false. The Boolean only supports these operators:

Hierarchy Notation

As presented earlier, the syntax of the Clause identity part is as follows:

Feature1.Feature2.FeatureN.Property(Language1,Language2,LanguageN)

Each feature is separated by a . (dot), and this dot-notation is widely used and understood from many other syntaxes. The Perfion query extends this dot-notation with a very powerful concept that has been denoted Hierarchy-notation. In addition to the dot, it is possible to use the curly parentheses { or } to denote a downwards or upwards hierarchy of child or parent Items, like:

Feature1}Feature2.FeatureN{Property(Language1,Language2,LanguageN)

Child-hierarchy

The following example illustrates how it works:

With normal syntax, all Coffee Machines can be found by writing the following Clause:

Category='Coffee Machines'

With hierarchy-notation all Coffee & Tea (i.e. all Items in both Accessories, Coffee Machines and Coffee Makers, or any other Category at any depth contained inside Coffee & Tea) can be found by the following Clause:

Category{String='Coffee & Tea’

…and since the String property is default for e.g. the Category feature, simply write:

Category{='Coffee & Tea'

…or even more powerful, combine it with other Properties such as:

Category{ LIKE 'Coffee%'

The above example illustrates the use of hierarchy-notation on the feature Category assuming that the Items being found are Products. However, the same is possible within the feature itself (e.g. products without thinking about its features), as illustrated by the following example:

With normal dot-syntax, the Cocotte round Item above can be found by writing:

String='Cocotte round'

With hierarchy-notation all Cocotte round items can be found by writing:

{String='Cocotte round'

Parent-hierarchy

The hierarchy-notation can also be used in the parent (upwards) direction. This could be e.g. be useful to retrieve the entire parenthood of an Item, such as:

}String='1101225'

…will return the specific ‘1101225’ Item as well as all its parent Items all the way up to the root Item (in this case the Item named ‘Cocotte round’).

In most application scenarios the Items will be referred to by their unique ID values. In this case the above example would be written as:

}ID=672

Clause Methods

Another powerful concept in the Perfion query are the Clause Methods, which introduce very specific query clauses that would not be possible to write using normal query syntax.

COMPARE

The Compare Clause Method makes it possible to compare an entire group of Items to another group of Items, e.g. to find Items that are not in another group or Items that differs on specified feature values as compared to Items in another group.

Specifically, this functionality is very useful when working with Items and their feature values at different Life Cycle Stages.

The syntax of the COMPARE method is as follows:

XML:

<Query>
   <Select languages='EN' >
      <Feature id='ItemName'  />
   </Select>
   <From id ='100'/>
   <Where>
      <ClauseMethod id='Compare' operator='!='>
         <Parameter id='StageFeature' value='ProductStatus' />
         <Parameter id='KeyFeature' value='String' />
         <Parameter id='Stage1ID' value='2109' />
         <Parameter id='Stage2ID' value='2119' />
         <Parameter id='IncludeOtherID' value='1' />
         <Parameter id='CompareFeatures'>
            <Value>HeightCM</Value>
            <Value>WidthCM</Value>
            <Value>DiameterCM</Value>
         </Parameter>
      </ClauseMethod>
   </Where>
</Query>

JSON:

{
   "Query": {
      "Select": {
         "languages": "EN",
         "Features": [
            { "id": "ItemName" }
         ]
      },
      "From": { "id": "100"},
      "Where": {
         "Clauses": [
            { "ClauseMethod": {
               "id": "Compare",
               "operator": "!=",
               "parameters": [
                  { "id": "StageFeature", "value": "ProductStatus" },
                  { "id": "KeyFeature", "value": "String" },
                  { "id": "Stage1ID", "value": "2109" },
                  { "id": "Stage2ID", "value": "2119" },
                  { "id": "IncludeOtherID", "value": 1 },
                  { "id": "CompareFeatures", "value": [ "HeightCM", "WidthCM", "DiameterCM"] }
               ] }
            }
         ]
      }
   }
}

The Operator Values of the Clause Method can be:

The Parameters and their values are as follows:

Nested Clauses - AND, OR, ( … )

The Perfion query syntax also offers the possibility of creating more complex queries clauses using OR and nested with parentheses. The syntax is as follows:

XML:

<Where>
    <Clause id='Category.ID' operator='=' value='787' />
    <Clause id='Status' operator='=' value='Approved' />
    <Sub>
        <Clause id='Brand' operator='NOT IN'>
            <Value>Siemens</Value>
            <Value>Jura</Value>
        </Clause>
        <Clause id='ItemType' operator='=' value='SKU' />
        <Or/>
        <Clause id='Brand' operator='IN'>
            <Value>Stelton</Value>
            <Value>Bodum</Value>
        </Clause>
        <Clause id='ItemType' operator='=' value='Color' />
    </Sub>
</Where>

JSON:

"Where": {
    "Clauses": [
        { "Clause": { "id": "Category.ID", "operator": "=", "value": "787" } },
        { "Clause": { "id": "Status", "operator": "=", "value": "Approved" } },
        { "Clauses": [
            { "Clause": { "id": "Brand", "operator": "NOT IN", "value": [ "Siemens", "Jura" ] } },
            { "Clause": { "id": "ItemType", "operator": "=", "value": "SKU" } },
            { "Or": {} },
            { "Clause": { "id": "Brand", "operator": "IN", "value": [ "Stelton", "Bodum" ] } },
            { "Clause": { "id": "ItemType", "operator": "=", "value": "Color" } }
        ] }
    ]
}

The <Sub> / { "Clauses": [] } and <Or/> / { "Or": {} } elements/objects are used to construct nested queries.

The OR element/object can only be used within SUB elements/objects, so if OR is needed at the top level, then the first element within the Clause must be a Sub element. This design choice allows merging of embedded queries and extra criteria based on user choices in the application GUI.

System Variables – User

Instead of explicitly defining values to query for, it is also possible to use the following System defined variables:

This makes it possible to create a query to find all Items that has been created by the current user by writing a clause like <Clause id='CreatedBy' operator='=' value='@@USER' />

System Variables – Datetime

From version 5.1, the Perfion API supports a new set of Date/time variables as listed below. Before this version, only @@DATENOW was supported.

From version 5.1, the Perfion API supports simple calculations based on the chosen date variable. This makes it possible to write a clause, for example, stating that you want items modified the last 14 days <Clause id='ModifiedDate' operator='&gt;' value='@@UTCNOW-14d' />

Calculations support adding or subtracting a number of seconds (s), minutes (m), hours (h), days (d) or years (y).

Create Values

When a Section is setup to use a categorizer using a search feature it is possible to define feature values to be used when inserting new items. The createvalue attribute of a where clause is used for this (only XML syntax available for the search feature where clause):

XML:

<Where>
    <Clause id='Category.ID' operator='=' value='787' createvalue='787' />
    <Clause id='Supplier' operator='=' value='Supplier A' createvalue='Supplier A' />
    <Clause id='ProductStatus' operator='!=' value='Approved' createvalue='ERP' />
</Where>

Whenever a new item is inserted the new item will get Category ID set to 787, Supplier set to ‘Supplier A’ and ProductStatus to ‘ERP’.

Remote features

Note that the WHERE clause does not recognize criteria on remote feature values. Please see the The Perfion Query - HAVING section for how to apply criteria to Remote Features.