Queries

Dynamicweb 9 features a lightning fast generalized search framework usually referred to as New Indexing, which can be used to index and search through all kinds of data on your solution – productscontentusers, etc.

Broadly speaking, New Indexing consists of the following elements:

  • Indexes – which are data structures optimized for data retrieval
  • Queries – which are used to retrieve data based on criteria you define
  • Facets – which are used to create filters in frontend
  • Tasks – which are used to rebuild indexes at an interval

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

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

This makes it easy to select the appropriate operator for an expression.

Figure 6.1 Modifying expressions using operators

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.

At the bottom, you have the following tables available (Figure 9.1):

Figure 9.1 Show settings

Here, you can choose which fields and languages to include and exclude from the query.