Queries

Dynamicweb features a generalized search framework usually referred to as Indexing, which can be used to index and query all kinds of data on your solution – productscontentusersfiles, etc.

Broadly speaking, Indexing consists of the following elements:

  • Indexes are data structures optimized for data retrieval
  • Queries are used to retrieve data based on criteria you define
  • Facets are used to create filters in frontend
  • Tasks are used to rebuild indexes on a schedule

All of these elements can be heavily configured to suit your particular scenario – and all exist within a so-called repository, which is simply a folder in the file archive containing configuration files.

This article will tell you more about Queries.

A 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 number of expression which will in turn return the data you want to retrieve (Figure 2.1).

Figure 2.1 Implementing expressions for queries

To create a query:

  • Click Add Query in the repository toolbar (Figure 2.2)
  • Name it
  • Select a data source (an index)
  • Click OK
Figure 2.2 Creating a query

This opens the query configuration page (Figure 2.3).

Figure 2.3 The query configuration page

From here you can configure the query by:

  • Creating parameters
  • Defining expressions
  • Add default sorting criteria

You can view (and change) the source index using the button in the ribbon bar.

A parameter is a variable which can be used as test values 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.

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

Expressions are used to limit the data returned by a query – a query without any expressions at all will return all data in an index.

Expressions consist of the following elements:

  • A field in the index to query
  • An operator
  • A test value to test against

For instance, a simple expression returning all Active products in a product index would look like Figure 5.1 – where the field Active is checked against the boolean constant true.

Figure 5.1 Example of a simple expression

To create an expression:

  • Click Add group
  • Select a field in the index using the dropdown
  • Select an operator
  • Use the pencil-icon to set a test value
  • Click OK

Expressions may be combined in AND-groups and OR-groups, and can be negated to return all elements not matching the expressions. This makes it possible to create pretty powerful and complex queries.

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

Figure 6.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
    • 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 7.1)
    • Code: Select a provider and configure the associated settings
Figure 7.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 7.3).

Figure 7.3 Matching null values

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