Queries

query is simply a request for data from an index – e.g. ‘Return all active products with a price below 100’ or ‘Return all users who live in Denmark’.

Queries are created by stringing together a set of expressions – as in Figure 1.1 where we’re asking for all products which are Active and in SHOP2.

Figure 1.1 An expression

You can think of each expression as being a kind of filter which limits what the query returns by only returning products which match the expression criteria, typically by checking if a field in the index has a specific value.

The anatomy of an expression is this:

  • On the left side you have an index field – this can be a standard field or a generated field
  • In the middle you have an operator – it defines how you’re comparing the left and right-side values, e.g. GreaterThan, LessThan, Equal, or IsEmpty.
  • On the right side you have a test value – this is the value you’re comparing with the value(s) in the index field

Expressions may be combined in AND-groups (all expressions inside the group must be true for a product/user/page to be included in the result) and OR-groups (one of the expressions in this group must be true for a product/user/page to be included in the result). You can also check Negate for a group to return all elements not matching the expressions. All in all, this makes it possible to create pretty powerful and complex queries – although in most cases it’s not necessary to complicate things!

To create a query:

  1. Go to Settings > Repositories and open a repository
  2. Click Add Query in the toolbar
  3. Provide a name and select an index as a data source
  4. Under the Expressions section:
    • Click Add group or Add expression
    • Select an index field using the dropdown
    • Select an operator
    • Use the term selector/pencil icon to set a test value
    • Click OK

Negation and groups

Please note that if you have multiple expressions in a negated group, each expression is evaluated individually and then negated. If you in a negated group have two expressions, and the first expression gives two results, and second expression filters to one result, then it will negate, and show all products but the one (ending up doing the opposite of what you want). 

When creating expressions, you must select an operator.

The available operators depend on the type of field selected - for a Boolean field you only have access to true/false, for a string array you have Contains, MatchAll, MatchAny and In, and so on.

This makes it easier to select an appropriate operator for an expression (Figure 2.1).

Figure 2.1 Modifying expressions using operators

Some of the operators are a little hard to differentiate at a glance - here's a short explanation:

Operator

Description

Equal

Checks if the field value is equal to the test value. If test value is an array this operator works like the MatchAll operator.

MatchAny

Checks if the field contains any of the test values – If FieldValue1 == TestValue1 OR FieldValue1 == TestValue2 OR FieldValue1 == TestValue3

MatchAll

Checks if the field contains all the test values – If FieldValue1 == TestValue1 AND FieldValue1 == TestValue2 AND FieldValue1 == TestValue3

In

Works like MatchAny but only accepts arrays as the test value, where MatchAny also accepts non-arrays as test values

Contains

Looks for a partial match between the field value and the test value, matching values from their beginning – i.e. using [term]*

ContainsExtended

Looks for a partial match between the field value and the test values, matching values anywhere – i.e. using *[term]*

This comes with a significant performance and memory hit, so please think carefully before using this operator.

Please note that due to performance reasons, the Contains operator does not match values in the middle of terms – i.e. it matches [TERM*] but not [*TERM*]. This is not uncommon or odd - it's what Google does, after all.

If you want to match on *[TERM]* instead you can use the ContainsExtended operator – but please be aware that this comes with a significant performance and memory overhead. Don't say we haven't warned you ;)

The right side of an expression contains the test value. Test values can be either static or dynamic depending on the type of test value you use.

The following test value types are available:

  • Constant – a static value of a particular data type – string, double, datetime, etc.
  • Parameter – a dynamic value passed to the query from frontend, e.g. through a search field
  • Macro – a dynamic value retrieved from the context, e.g. PageID, WebsiteID, UserID, CartID, etc.
  • Term – a static value present in the field being queried
  • Code – lets you select a provider which provides a graphical interface to manipulating e.g. a DateTime C# object (CurrentDate – 1 month)

To define a test value:

  • Click the pencil icon on the right side of the expression
  • Select a test value type
  • Configure the type selected:
    • Constant: Select a data type and enter a value
    • Parameter: Select a parameter - see later section
    • Macro: Select one of the context-specific values available
    • Term: Use the dropdown to select one or more of the terms that exist in the field (Figure 3.1)
    • Code: Select a provider and configure the associated settings
Figure 3.1 Selecting terms when defining a test value

The following macros are currently available as test values:

Macro

Context

Description

PageID

Dynamicwen.Frontend.PageView.Context

 

WebsiteID

Dynamicwen.Frontend.PageView.Context

 

AssortmentsID

Dynamicweb.UserManagement.Context

 

MostFrequentBoughtProducts

Dynamicweb.UserManagement.Context

Returns an array of of product IDs most frequently bought by the current user within the last 3 months

MostBoughtProducts

Dynamicweb.UserManagement.Context

Returns an array of product IDs which the current user buys in the largest quantities within the last 3 months

FavoritesByUserId

Dynamicweb.UserManagement.Context

Returns an array of product IDs which are on the default favorites list of the accessuserid called

FavoritedByCustomerNumber

Dynamicweb.UserManagement.Context

Returns an array of product IDs which are on the default favorites list of the current user (customer number)

FavoritesAutoIdByUserId Dynamicweb.UserManagement.Context Returns a list of product autoids based one the current user

UserID

Dynamicweb.UserManagement.Context

Returns the UserID of the current user

UserGroups

Dynamicweb.UserManagement.Context

 

CustomerNumber

Dynamicweb.UserManagement.Context

Returns the Customer Number of the current user

LanguageID

Dynamicweb.Ecommerce.Context

 

ShopID

Dynamicweb.Ecommerce.Context

 

CartID

Dynamicweb.Ecommerce.Context

 

StockLocationID

Dynamicweb.Ecommerce.Context

 

 

Matching null values

Apache Lucene, the software library used for the standard IndexProvider in New Indexing, does not index Null values or empty strings at all, which means that you cannot easily isolate index entries without a value in them.

The workaround is to add an expression group matching all records with a value, and then negate it (Figure 3.3).

Figure 3.3 Matching null values

A parameter is a variable which can be used as a test value in an expression to make the query dynamic – so you could create a parameter called Search, use it as a test value in an expression, and then pass dynamic values to it from frontend through a search filter.

It is also used by facets, with each facet passing values to a specific parameter - e.g. Color or Size.

To create a parameter:

  • Click Add Parameter on the query page
  • Name the parameter
  • Select an appropriate data type for the parameter – this should match the data type of the field your will be testing against
  • (Optional) Specify a default value
  • Click OK

Once created, a parameter will be listed in the parameters table (Figure 4.1) – and will be available as a test value when creating expressions.

Figure 4.1 Creating parameters

The data returned by a query can be sorted in a number of ways. Most of the time, you will want to control sorting at the app-level, on either the Product Catalog app instance or the Query publisher app instance used to publish the data.

Should you want to not do that, or should those settings fail for some reason, you can create default sorting criteria on the query level:

  • Click Add sorting on the query page
  • Select a field and a direction
  • Click OK
Figure 5.1 Add sorting

Please note that this is a default sorting and that any sorting applied at the app-level will override it.

Sorting on an analyzed field

You can’t sort on a field which is analyzed and expect things to behave in any meaningful manner – so to sort on e.g. the product name, add a separate, unanalyzed product name field to your index. Keep in mind that all string type fields from the Schema Extender are analyzed by default.