Queries
A query is a request for data from an index. It specifies criteria that the data must meet to be included in the search results. Queries are essential in search engines and databases to retrieve specific information based on various conditions.
An example of a query could be ‘Return all active products with a price below 100’ or ‘Return all users who live in Denmark’.
Creating a Query
To create a query, follow these steps:
- Navigate to Settings > Repositories and enter the repository you wish to add a query to
- In the Queries section, click Manage
- Click New Query
- Provide a name and select the Source index you want to query
- A source index in e-commerce queries refers to a specific data repository or database from which the query retrieves information. It is essentially the origin of the data that is being queried. The source index serves as the foundation for data extraction. It contains the raw data that the query will process, filter, or analyze.
- Click Save and close
You have now created an empty query you can access:
Before we move on, let's have a look at the anatomy of the query that we've just created. We can see that the query has three sections:
- Parameters: Allow the query to accept dynamic input values, making it flexible
- Expressions: Define the criteria to filter data
- Sortings: Organize the search results based on specified fields
We'll now delve deeper into how these work, starting with:
Expressions
The core of a query lies in the set of expressions which dictates the result it returns. You can think of an unmodified or 'empty' query as one that returns everything in the index. Each expression you add, acts as a filter, narrowing down the results to match your specific needs. In other words, an expression limits the returned data to exactly what you want to see.
The anatomy of an expression is this:
- Field: A category or type of information about a product e.g. Price or Name
- Operator: Specifies the kind of comparison or condition you want to apply, e.g. are values equal, greater than, or less than others
- Type: Determines the kind of test value the query should return e.g. a fixed number, a dynamic value, a user input, or a specific search term
In the example below, we're testing if a product is marked as Active and whether its Price is less than or equal to 0. If a product meets both these conditions, it will be included in the results of the query.
Expression Groups
Expressions can be grouped together to refine your search criteria. There are two main types of groups:
- AND-groups: All expressions inside the group must be true for a product to be included in the results
- Example: Brand = "Apple" AND Rating >= 4.5
- OR-groups: At least one of the expressions inside the group must be true for a product to be included in the results
- Example: Color = "Red" OR Color = "Blue"
To create a group for Expressions:
- In your query, navigate to the Expressions section and click Manage
- Click New group
- Choose whether the group should be an 'AND-group' or an 'OR-group'
- If needed, check the Negate option
- The negation returns all products that do not match the expressions in the group, in a group with multiple expressions, each expression is evaluated on its own first and then negated
Example: Expression
Let's return to our example query that checks if a product is marked as Active and whether its Price is less than or equal to 0. Remember, it consists of two expressions in an AND-group:
- First Expression
- Field: Active
- Operator: Equal
- Value: True
- Second Expression
- Field: Price
- Operator: LessThanOrEqual
- Value: 0
Too see this simple expression in a concrete example, let's see what it does on this dummy product index:
| ProductID | Name | Active | Price |
|---|---|---|---|
| 1 | Laptop | Active | 1200 |
| 2 | Headphones | Not Active | 150 |
| 3 | Mouse | Active | 25 |
| 4 | Keyboard | Active | 0 |
| 5 | Monitor | Not Active | 300 |
In this table, the columns 'ProductID', 'Name', 'Active', and 'Price' are referred to as fields. The specific values in the table, such as '1', 'Laptop', 'Active', and '1200', are called terms.
Step-by-Step Query Execution:
- First expression: Retrieve products with Status = Active
-
- Matching products: ProductID 1, ProductID 3, ProductID 4
- Second Expression: From the matching products, retrieve those with Price <= 0
- Matching products: ProductID 4 (since only ProductID 4 has a price of 0)
So, the query only returns ProductID 4 (keyboard), since its the only product that meets both expressions. If we had checked the negate button, all the products except ProductID 4 would have been returned.
Operators
When creating expressions, choosing an operator is mandatory to specify the kind of comparison or condition you want to apply.
The operators available to you depend on the type of field you select. This ensures that you only see and select operators that are relevant to your chosen field, making the process more intuitive. It also limits logical inconsistencies and provides a better understanding of the field-specific logic where e.g. operators like Contains or Equal serves Text fields better than numeric comparison operators.
Let's look at the operators in our example expression:
- Price (Numeric Field)
- Available Operators: Between, Equal, GreaterThan, GreaterThanOrEqual, In, IsEmpty, LessThan, LessThanOrEqual
- Usage in example expression:
- Expression: Price <= 0
- Field: Price
- Operator: LessThanOrEqual
- Active (Boolean Field)
- Available Operators: Equal, In
- Usage in example expression:
- Expression: Active = Active
- Field: Active
- Operator: Equal
An overview of some operators that may not be self-explanatory:
| Operator | Description | Example |
|---|---|---|
| Equal | Checks if the field value is equal to the test value. If the test value is an array, this operator works like the MatchAll operator |
Find products with a status of 'Active': Status Equal 'Active' |
| MatchAny | Checks if the field contains any of the test values | Find products in either 'Electronics', 'Books', or 'Clothing' categories: Category MatchAny ['Electronics', 'Books', 'Clothing'] |
| MatchAll | Checks if the field contains all the test values | Find products that have all tags 'New', 'OnSale', and 'Featured': Tags MatchAll ['New', 'OnSale', 'Featured'] |
| In | Works like MatchAny but only accepts arrays as the test value, whereas MatchAny also accepts non-arrays as test values |
Find users with IDs 101, 102, or 103: UserID In [101, 102, 103] |
| Contains | Looks for a partial match between the field value and the test value, matching values from their beginning (e.g., using [term]) |
Find products whose names start with 'Smart': Name Contains 'Smart' |
| ContainsExtended | Looks for a partial match between the field value and the test values, matching values anywhere (e.g., using [term]) This comes with a significant performance and memory overhead. |
Find products whose descriptions contain 'wireless': Description ContainsExtended 'wireless' |
| IsEmpty | Checks if a field is empty or not set | Find records where the Email field is empty: Email IsEmpty |
Types & Test Values
The Type in an expression determines the kind of test value you're working with.
A Test Value is the specific data or input that an expression evaluates against to determine if a condition is met. For example, in the expression Category = 'Electronics', the test value is 'Electronics', which the expression uses to check if the Category of an item is 'Electronics'.
The following Types are available:
| Type | Description | Use cases | Example |
|---|---|---|---|
| Constant | A fixed unchanging value that is defined in a query. It is a specific value that does not vary during the execution of the query, regardless of the context or user interactions | Filtering: When you want to filter data based on a fixed criterion. Thresholds: Setting limits or boundaries that do not vary |
Find all products priced below $100 with a filter, the query is 'Price < 100'. The value '100' is a constant and is used to compare each product's price |
| Macro | A dynamic placeholder that gets replaced with actual values derived from the current context at runtime. Macros are useful for inserting context-specific data into a query, making the query adaptable to different users or situations | User-specific Data: Tailoring results to the current user. Session-specific Data: Adjusting queries based on the session information |
Retrieve records associated with the currently logged-in user, the query is 'UserID = CurrentUserID'. 'CurrentUserID' is a macro that gets replaced with the actual user ID at runtime |
| Parameter | A dynamic value passed to the query from an external source, such as through a search field, a facet, or other user inputs. We'll delver deeper into parameters here | Dynamic Filtering: Allowing users to refine search results based on their input | Users should be able to filter products by category, the query is 'Category = SelectedCategory'. 'SelectedCategory' is a parameter that gets its value from the user's selection in e.g. a dropdown menu or search field, and will be dynamically replaced with the actual category chosen by the user at runtime |
| Term | A static value existing in the field being queried. It's akin to a word or phrase someone enters into a search box | Keyword Search: Finding records that contain a specific word or phrase | Find products that mention "laptop" in their description, the query is 'Description Contains "laptop"'. "Laptop" is a term used within the 'Description' field. The query checks for records where the description includes this specific word |
When selecting a Type, specific options for that individual type will appear. To define the specific types:
- Constant: Select an available data type and enter a value
- Macro: Select one of the context-specific values available
- Parameter: Select a parameter - see later section
- Term: Select the terms existing in the Field you've selected
Tip
Matching null values
Lucene, the software library used to build repositories, 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.
Parameters
A parameter is a dynamic value that can be incorporated into a query, allowing the query to adapt to different inputs and conditions. Parameters are defined at runtime based on user inputs or other external sources, making your queries more dynamic and flexible.
It is also used by facets, with each facet passing values to a specific parameter. For example a parameter called 'Color' with facet options 'Red', 'Blue', and 'Green'. When a user selects e.g. 'Red', this value is passed to the 'Color' parameter. The query then filters the products to include only those that match the selected color.
To use a parameter within an expression, you must do two things:
- Create the parameter
- Integrate it into an expression
To create a parameter:
- Navigate to the query where you want to add a parameter: Settings > Repositories > YourRepository > YourQuery
- In the Parameters section, click Manage
- In the Parameters section, click Add
- In the slide-over panel you need to:
- Provide a name, e.g. 'Search', 'Category', or 'MinPrice'
- Set the type name of the parameter which determines the kind of data it can hold, e.g. the type System.String is suitable for textual input like keywords or product names
- If needed set a default value that is assigned to the parameter if no value is provided at runtime, this ensures that the query has a valid input to work with, even if the user does not provide one
Once created, a parameter will be listed in the parameters table:
Integrating a Parameter into an Expression
After defining a parameter, the next step is to integrate it into an expression. This allows the query to utilize the dynamic value provided by the parameter to filter or manipulate data according to the user's input or other external sources.
Here’s how they are utilized:
- Dynamic Filtering:
- Parameters enable real-time filtering of data based on external inputs
- Example: Using a 'Search' parameter to filter products by name based on user input
- Flexibility and Adaptability:
- They make queries more flexible and adaptable to varying conditions
- Example: A 'Category' parameter can filter products by different categories selected by users
- User-Driven Criteria:
- Parameters allow queries to incorporate criteria that users define at runtime
- Example: A 'PriceRange' parameter allows users to filter products within a specific price range
To integrate a parameter into an expression:
- Navigate to the Expressions section of your query and click Manage
- Go to the context menu of the group that you want to add an expression with a parameter to, and click New Expression
- Set up the expression and in the Type input, select Parameter
- In the Parameter section select your newly created parameter from the list:
Example: Parameter Definition and Integration
To get an idea how how this actually works in DynamicWeb, let's go through an example of defining a parameter and integrating it into an expression.
We'll create a parameter named 'ProductSearch' that allows users to filter products by name dynamically in e.g. a search box. We'll also use our dummy index from before as our source index:
| ProductID | Name | Active | Price |
|---|---|---|---|
| 1 | Laptop | Active | 1200 |
| 2 | Headphones | Not Active | 150 |
| 3 | Mouse | Active | 25 |
| 4 | Keyboard | Active | 0 |
| 5 | Monitor | Not Active | 300 |
Create the parameter:
- Navigate to the query where you want to add a parameter: Settings > Repositories > YourRepository > YourQuery
- In the Parameters section, click Manage
- In the Parameters tab, click Add
- Now we need to define the parameter:
- Name: Name the parameter 'ProductSearch', since the parameter will be used within an expression that enables users to search for products
- Type: Select the System.String[] type, since this enables the parameter to take in multiple input strings from the user
- Default Value: Leave the default value empty, since the user will provide a value at runtime when they enter a search word
To integrate the created parameter into an expression, follow these steps:
- Navigate to YourQuery
- Go to the Expression section and click Manage
- Go to the context menu of the group that you want to add the expression with a parameter to, and click New Expression
- Now we need to define the Expression:
- Field: The field depends on the source index our repository is referring to. In our source index the fields available would be: 'ProductID', 'Name', 'Active', and 'Price'. Since we're creating a ProductSearch query, we would choose the 'Name' field
- Operator: We would choose the operator 'Contains' for this example, since it looks for partial matches between the field value and the test value, matching the values from their beginning.
- Other relevant operators could be: Equal as it checks if the field value is exactly equal to the test value or ContainsExtended which looks for a partial match between the field value and the test values, matching values anywhere in the string
- Type: We would obviously choose 'Parameter' as the type and select the 'ProductSearch' parameter.
This simple parameter inside an expression would enable a user to search for products by name dynamically. For example, if a user enters "Laptop" into the search box, the query will filter the products to show only those with names starting with "Laptop".
Sorting
The data returned by a query can be organized in various ways using sorting. Typically, sorting is controlled at the app level - either through the Product Catalog app instance or the Query publisher app instance. However, you also have the option to set default sorting criteria directly on the query level.
To set the default sorting criteria on the query:
- Navigate to the query where you want to add a sorting: Settings > Repositories > YourRepository > YourQuery
- In the Sortings section, click Manage
- Under SortOrder, click Add
- Select a Field and a Sort direction
- Click OK and then Save and close
This is a default sorting and that any sorting applied at the app-level will override it.
Tip
Please note, that if a field is analysed, it cannot be sorted in a meaningful manner and will yield nonsense results. Instead, add a separate un-analysed product name field to the index and use that to sort a query result.
Data Types
Data types define the kind of data a variable can hold, such as integers, strings, dates, or booleans. They determine how data is compared, filtered, and processed.
Using the correct data type ensures efficient queries and accurate results. For example, using a decimal for prices ensures precise calculations, while a string for product names enables effective searching.
Overview of Data Types
| Type | Explanation | Example |
|---|---|---|
| System.String | Used to store a single string | 'mountainbike' |
| System.String[] | Used to store multiple strings | 'mountainbike', 'electric' |
| System.Boolean | Used to store a true or false value | true |
| System.Boolean[] | Used to store multiple true or false values | true, false |
| System.DateTime | Used to store a single date and time | '2024-05-15' |
| System.DateTime[] | Used to store multiple dates and times | '2024-05-15', '2023-12-25' |
| System.Decimal | Used to store a precise decimal number | 99.99 |
| System.Decimal[] | Used to store multiple precise decimal numbers | 99.99, 199.99 |
| System.Double | Used to store a double-precision floating-point number | 99.99 |
| System.Double[] | Used to store multiple double-precision floating-point numbers | 99.99, 199.99 |
| System.Int16 | Used to store a 16-bit integer | 32,000 |
| System.Int16[] | Used to store multiple 16-bit integers | 32,000, -32,000 |
| System.Int32 | Used to store a 32-bit integer | 2,147,483,647 |
| System.Int32[] | Used to store multiple 32-bit integers | 2,147,483,647, -2,147,483,648 |
| System.Int64 | Used to store a 64-bit integer | 9,223,372,036,854,775,807 |
| System.Int64[] | Used to store multiple 64-bit integers | 9,223,372,036,854,775,807, -9,223,372,036,854,775,808 |
| System.Single | Used to store a single-precision floating-point number | 99.99 |
| System.Single[] | Used to store multiple single-precision floating-point numbers | 99.99, 199.99 |