Query Builder

Query Builder Specification

Owned by

CodeX

Developer Name

Dimitar Dimitrov
Mike Cherkasov
Teodosia Hristodorova
Ivan Petrov

Designer Name

Simeon Simeonov
Marin Popov

Requires approval from

Dimitar Dimitrov | Date:
Mike Cherkasov | Date:
Simeon Simeonov | Date:

Signed off by

Radoslav Mirchev | Date: 10-Oct-2024
Damyan Petev | Date:
Radoslav Karaivanov | Date:
Stamen Stoychev | Date:

Revision History

Version	Users	Date	Notes
1	Names of Developers and Designers	Date
1.1	Teodosia Hristodorova	Aug 20, 2024	Add test scenarios
1.2	Galina Edinakova	Aug 27, 2024	Update spec to reflect the addition of features required to support queries and subqueries.

1. Overview

Objectives

Query Builder provides means for operating with complex filters by creating or editing conditions and grouping them using AND/OR logic. The set of operands depends on the type of data that the filter is applied on. It outputs an object which could be serialized to JSON.

Acceptance criteria

Have query builder that shows filtering options.
The query builder should load predetermined filter expressions.
The filter expressions must be set/edit by user interaction.
The query builder must support adding/removing conditions and group filters.
The filter operands must correspond to the type of data being filtered.
The query builder must show whether AND or OR logic is applied to a group.
The query builder must output an object representing the currently applied expression.
(NEW) The query builder should allow selecting from multiple entities (tables).

2. User Stories

Developer stories:

As a developer, I want to be able to define the initial state of the Query Builder expression tree.
As a developer, I want to be able to clear the applied filtering expression programmatically.
As a developer, I want to be able to specify the operands applicable for a field corresponding to the type of data of that field.
As a developer, I want to have preset option for the type of filters that I can apply on data (numeric, string, boolean, date).
As a developer, I want to create custom filtering conditions.
As a developer, I want to be able to set display density for the Query Builder.
As a developer, I want to change the title and show/hide the legend, so that I can customize the header of the component.
(NEW) As a developer, I want to have the option to pass fields from different entities to the Query Builder.
(NEW) As a developer, I want to choose which fields from a selected entity should be returned by the query.
(NEW) As a developer, I want to be able to customize/template the search value input.

End-user stories:

As an end user, I want to be able to create groups of filters (expression). I want to be able to add interactively group of filters having predefined logical operation (AND, OR).
As an end user, I want to be able to edit a filter condition: specifying field, filtering operand and value for each filter. All this should be achieved interactively.
As an end user, I want to be able to choose the type of operand based on the field type.
As an end user, I want to be provided with a convenient end-user interface for creating and editing complex filtering expressions that contain multiple filters, spreading across and combining different data fields.
As an end user, I want to be able to interactively select the logical operation (AND, OR) that will define the connection between filters and filter expressions.
As an end user, I want to be able to add or remove filters interactively.
As an end user, I want to be able to add or remove group of filters interactively.
As an end user, I want to be able to group filters interactively.
As an end user, I want to be able to ungroup filters that have been grouped before.
As an end user, I want to have always exposed the name of the field on which the filter is applied on, the name of the filter operand applied and the typed filter value.
As an end user, I want to have always exposed the logical operation (AND, OR) applied for group of filters.
As an end user, I want to be able to perform selection on condition and group of conditions.
As an end user, I want to have exposed color code indication about the logical operation (AND, OR) applied for group of filters.
As an end user, I want to be be able to insert filter or group of filters after selected filter or group of filters.
As an end user, I want to be able to select multiple filter conditions.
As an end user, I want to have exposed contextual menu having meaningful actions when more than one filtering conditions are selected.
As an end user, I want to be able to clear all created filters.
As an end user, I want to be able to apply all created filters.
As an end user, I want to be able to quickly remove a filter condition using a clear icon in the filter chip.
(NEW) As an end user, I want to be able to select the entity whose fields I want to apply filtering on.
(NEW) As an end user, I want to be able to select which fields the query should return.
(NEW) As an end user, I want to be able to easily select to return all fields.
(NEW) As an end user, I want to be warned that all conditions would be cleared on switching the selected entity and be provided with an option to cancel/confirm this change.
(NEW) As an end user, I want to be able to easily create sub-queries.
(NEW) As an end user, I want to be able to identify subqueries without having to put the condition in edit mode.
(NEW) As an end user, I want to be able to show & hide sub-queries' inner conditions.

3. Functionality

3.1. End-User Experience

** Integration scenarios or functionality with other features/components prototype ** End-to-end user experienceprototype ** Prepared design files for styling e.g. interplay with features and light/dark variants design hand-off

3.2. Developer Experience (UPDATED)

The simple Query Builder component could be defined like this:

<igx-query-builder
    [entities]="this.entities">
</igx-query-builder>

The developer could customize the header by specifying a different title and/or hiding the legend using the IgxQueryBuilderHeaderComponent.

<igx-query-builder [entities]="this.entities">
    <!-- Custom header -->
    <igx-query-builder-header [title]="'Custom title'"
        [showLegend]="false">
    </igx-query-builder-header>
</igx-query-builder>

The developer could customize the search value input using the IgxQueryBuilderSearchValueTemplateDirective.

<igx-query-builder [entities]="this.entities">
    <ng-template igxQueryBuilderSearchValue>
        <span>Custom Search Value</span>
    </ng-template>
</igx-query-builder>

Entities (NEW)

The developer can define the entities whose fields all further operations would be applied on via the Query Builder's entities input. An entity is represented by its name and its collection of fields.

export interface EntityType {
    name: string;
    fields: FieldType[];
}

Fields

The developer can define the fields to be filtered as well as their data type, label, filtering operands. If not specified, default filtering operands are set based on the data type.

This can be done via the fields property with the following interface:

export interface FieldType { (UPDATED)
    /** The label of the data field */
    label?: string;
    /** The name of the data field */
    field: string;
    /** The displayed text for the field. If not set, the field is used. */
    header?: string;
    /** The field data type */
    dataType: DataType;
    /** Provides filtering operations. If not set, default ones are used based on data type */
    filters: IgxFilteringOperand;
    /** Sets the format pipe arguments for the field */
    pipeArgs: IFieldPipeArgs;
    /** The default time format */
    defaultTimeFormat: string;
    /** The default date time format */
    defaultDateTimeFormat: string;
    /** Applies custom formatter to the displayed values */
    formatter(value: any, rowData?: any): any;
}

Expressions Tree

The developer can set the initial expressions tree, or get the one modified by the user, by the expressionsTree property with the following interface:

export interface IExpressionTree { (UPDATED)
    filteringOperands: (IExpressionTree | IFilteringExpression)[];
    operator: FilteringLogic;
    field?: string;
    entity?: string;
    returnFields?: string[];
}

export declare interface IFilteringExpression { (UPDATED)
    field: string;
    condition?: IFilteringOperation;
    conditionName: string;
    searchVal?: Serializable;
    searchTree?: IExpressionTree;
    ignoreCase?: boolean;
}

export interface IFilteringOperation {
    name: string;
    isUnary: boolean;
    iconName: string;
    hidden?: boolean;
    logic: (value: any, searchVal?: any, ignoreCase?: boolean) => boolean;
}

3.3. Globalization/Localization

3.4. Keyboard Navigation

Keys	Description
Tab/Shift+Tab	Navigates through buttons for adding new groups or conditions, buttons and fields of a condition in edit mode and chips of already set conditions and their corresponding remove, edit and add buttons.
Enter/Space	Enters edit mode for a field. Selects specified value for a field. Applies the action of an active button. Selects/Deselects a chip.
Arrow Up/Down	Navigates up/down through the dropdown menu items.
Esc	Closes the drop down menu.

3.5. API

IgxQueryBuilderComponent

Options (UPDATED)

Name	Description	Type
entities	An array of entities to select fields from. Contains information about the entity's name and fields.	EntityType[]
expressionTree	Gets/Sets the displayed expressions tree.	IExpressionTree
locale	Locale settings for the component. If this locale is not set, its value to be determined based on the global Angular application LOCALE_ID.	string
resourceStrings	Gets/sets the resource strings.	IQueryBuilderResourceStrings

Events

Name	Description	Cancelable	Parameters
expressionTreeChange	Emitted when entity, return fields, condition, field, operand, value is changed.	no	-

IgxQueryBuilderHeaderComponent

Options

Name	Description	Type
title	Sets the title displayed in the header.	string
showLegend	Determines whether the legend items are displayed or not. Defaults to true.	boolean
resourceStrings	Gets/sets the resource strings.	IQueryBuilderResourceStrings

IgxQueryBuilderTreeComponent (NEW)

Options

Name	Description	Type
entities	An array of entities to select fields from. Contains information about the entity's name and fields.	EntityType[]
parentExpression
fields	An array of fields to be filtered. Contains information about label, field, type, operands.	FieldType[]
expressionTree	Gets/Sets the displayed tree.	IExpressionTree
locale	Locale settings for the component. If this locale is not set, its value to be determined based on the global Angular application LOCALE_ID.	string
resourceStrings	Gets/sets the resource strings.	IQueryBuilderResourceStrings

Events

Name	Description	Cancelable	Parameters
expressionTreeChange	Emitted when entity, condition, field, operand, value is changed.	no	-
inEditModeChange	Emitted when the expression item's inEditMode changes.	no	-

4. Test Scenarios

Automation

Basic

Should render Query Builder with header and query-builder-tree.
Should display a message, 'and' and 'or' buttons and if there is no expression tree in Query Builder.
Should show/hide initial adding buttons depending on the expression tree value.
Should display correct inputs according to the expression tree when it is set initially.
All nested queries initially should be displayed as collapsed.
Entity select should have correct items based on Query Builder entity input.
Fields drop-down should have correct items based on Query Builder entity input.
'And' and 'Or' groups should have correct operator lines.
Should display custom header correctly.

Interaction

Should add a new 'And' group when clicking the respective 'add and' button.
Should add a new 'Or' group when clicking the respective 'add or' button.
Newly added groups should have disabled adding buttons.
Newly added group should have disabled 'end group' button.
Newly added group should have an empty entity and field inputs.
Newly added group should have a single empty condition, a disabled 'commit' button and an enabled 'cancel' button.
Newly added group should be discarded when clicking the 'cancel' button of its initial condition.
Newly added condition should be empty and have a disabled 'commit' button and an enabled 'cancel' button.
Should display an alert dialog when the entity is changed.
Should reset all fields and conditions when the entity is changed.
Should have correct fields based on the selected entity.
Each condition field should be enabled only if the field on its left is populated. (except for unary operators).
The first condition field/dropdown should contain proper items based on the selected entity.
Second condition field/dropdown should contain operators based on the column's datatype.
Third condition field should open the calendar when the column is of 'date' type.
Third condition field should contain editable text when the column is not of type 'date'.
Condition should have enabled 'commit' button only when all fields are populated.
A group with a single condition should have disabled 'end group' button.
Test ‘in’/’not-in’ conditions.
Choosing the ‘In’/’Not-In’ condition should render an empty QB-tree and the third condition field should be disabled.
‘In’/’Not-In’ condition should have enabled ‘commit’ button when the QB-tree is committed.
The ‘commit’ button of the parent query should be disabled if a child query is currently added and not committed.
The 'commit' button of the parent query should remain enabled if the child query is edited.
Clicking on the parent query 'commit' button should apply all child committed changes and discard all uncommitted changes.
Should commit currently edited condition when the 'commit' button is clicked.
Should correctly filter by string, number, date and boolean columns through UI.
Should not commit currently edited condition when the 'close' button is clicked.
Should close the condition that is currently in edit mode when the 'close' button is clicked.
Should add a new condition or a group to an existing group by using add buttons.
Should end a group when clicking its 'end group' button.
Should remove a condition from an existing group by using the delete icon of the respective chip.
Should delete the newly added group when clicking the 'close' button of its only left condition.
Should select/unselect a condition when its respective chip is clicked.
Clicking/Hovering a single condition should display its 'edit' button and the '+' button for adding more conditions/groups to the group.
Should display the ‘expand’ button only for ‘in’/’not-in’ conditions/groups.
Clicking on the ‘expand’ button should expand/collapse the conditions and groups defined in the ‘in’/’not-in’ condition/group.
The root ‘in’/’not-in’ group should not be collapsible and should not have an ‘expand’ button.
Selecting multiple conditions should display the (create group)/(delete filters) context menu.
Unselecting conditions until one selected remains should hide the (create group)/(delete filters) context menu.
When a condition is selected, clicking its 'edit' button should put it in edit mode.
Double-clicking a condition (nested as well) should put it in edit mode.
Should create an 'and'/'or' group from multiple selected conditions when the respective context menu button is clicked.
Should delete all selected conditions when the 'delete filters' option from the context menu is clicked.
Should select/deselect all child conditions and groups when clicking a group's operator line.
Should show/hide the group's context menu when clicking its operator line.
Should close the context menu when clicking its close button.
Should discard the added group when clicking its operator line without having a single expression.
Should change the group's operator when using its context menu buttons.
The Ungroup button of the root group's context menu should be disabled.
Should ungroup the group's children and append them to the next parent group when clicking 'ungroup' from the context menu.
Should remove a group from the expr tree when clicking 'delete' from the context menu.
Should return correct expressionTree with applied filtering.
Should emit the expressionTreeChange event.

Keyboard Navigation

Should navigate with Tab/Shift+Tab through the entity and fields inputs, chips, their respective delete icons and the operator lines.
Should navigate with Tab/Shift+Tab through chips' 'edit', 'cancel' and 'adding' buttons.
Should navigate with Tab/Shift+Tab through the buttons and fields of a condition in edit mode.
Should select/deselect a chip when pressing 'Enter'/’space’ on it.
Should add a condition/group by pressing 'Enter'/’space’ on its respective 'add' button.
Should select/deselect all child conditions and groups when pressing 'Enter'/’space’ on a group's operator line.
Should open the group's context menu when pressing 'Enter'/’space’ on its operator line.