TL;DR: Discover advanced query-building methods with Angular Query Builder, enabling the queries creation with various connectors within the same group. Learn to customize templates and use buttons for rule and group management, along with CSS adjustments for a tailored user interface.
What is Query Builder?
The Query Builder is a graphical user interface component designed for building queries. It supports various functionalities such as data binding, templates, importing/exporting queries from/to JSON and SQL, and parsing queries to predicates for the data manager. It can be populated using an array of Javascript objects.
Create queries with various connectors
The Angular Query Builder is a powerful tool for creating complex database queries. However, by default, it only allows users to create queries with a single connector within the same group. However, users often need to create queries with different connectors, such as ( X = ‘A’ AND Y = ‘B’ OR Z = ‘C’ ), which is not supported by default.
This blog post explains how to overcome this limitation in our Query Builder using rule and header templates. Before we dive into the solution, let’s briefly define these templates.
Rule Template
The rule template allows you to define your user interface for columns. To implement the ruleTemplate, you can create the user interface using ngTemplate and assign the values through the actionBegin event.
The #ruleTemplate is a template variable that identifies the NgTemplate content as the corresponding column.
Header Template
The header template allows you to define your own user interface for the header, which includes creating or deleting rules and groups and customizing the AND/OR condition and NOT condition options.
To implement the header template, you can create the user interface using ngTemplate and assign the values when requestType is header-template-create in the actionBegin event.
The #headerTemplate is a template variable that identifies the NgTemplate content as the header.
Create an Angular application with Query Builder
To harness the power of Query Builder within an Angular application, follow these steps outlined in the getting started documentation.
Refer to the following code example to specify the width and columns properties to render the query builder. The columns property is used to specify the columns that are used to create filters in the query builder.
app.component.html
<ejs-querybuilder id="querybuilder" [columns]="filter" #querybuilder width="100%">
</ejs-querybuilder>
app.component.ts
To specify the three columns, including EmployeeID, EmployeeName, and Designation, for creating filters in the query builder using the following code example.
export class AppComponent {
@ViewChild('querybuilder')
public qryBldrObj!: QueryBuilder;
public filter: ColumnsModel[] = [
{ field: 'EmployeeID', label: 'Employee ID', type: 'number'},
{ field: 'EmployeeName', label: 'Employee Name', type: 'string'},
{ field: 'Designation', label: 'Designation', type: 'string'}
];
}
app.module.ts
Import the QueryBuilderModule to integrate the query builder component into your Angular application.
import { QueryBuilderModule } from '@syncfusion/ej2-angular-querybuilder';
@NgModule({
imports: [
QueryBuilderModule
]
})
Rule model to connect conditions with different connectors
By default, conditions are connected with the same connector using the using the condition property of the rule model object, which has rules property (referred to as a group). To connect conditions with different connectors, you need to specify the condition property of the rule model object, which doesn’t have a rules property(referred to as a rule), to connect the conditions with different connectors.
Specify the condition property of the rule model object with rules property to join the groups with other connectors. The root-level group will not account for this since we have connected the conditions and groups within that group.
Refer to the following code example.
public importRules: any = {
'condition': '',
'rules': [{
'condition': 'or',
'rules': [{
'label': 'Employee ID', 'field': 'EmployeeID', 'type': 'number',
'operator': 'equal', 'value': 1001, 'condition': 'and'
},
{
'label': 'Employee Name', 'field': 'EmployeeName', 'type': 'string',
'operator': 'equal', 'value': 'Nancy', 'condition': 'or'
},
{
'label': 'Designation', 'field': 'Designation', 'type': 'string',
'operator': 'equal', 'value': 'Developer'
}]
},
{
'condition': '',
'rules': [{
'label': 'Employee ID', 'field': 'EmployeeID', 'type': 'number',
'operator': 'equal', 'value': 1002
}]
}]
};
In the above code example, we have connected the first two conditions(EmployeeID = 1001 and EmployeeName = ‘Nancy’)with the AND operator, and the third condition(Designation = ‘Developer’) with the OR operator. The two groups are then connected using the OR operator. The second group of conditions(EmployeeID = 1002) is connected with the first group (EmployeeID = 1001 and EmployeeName= ‘Nancy’ or Designation = ‘Developer’) using the OR operator.
So, the resulting SQL query looks like as follows.
(EmployeeID = 1001 and EmployeeName= 'Nancy' or Designation = 'Developer') or (EmployeeID = 1002)
User Interface for creating conditions with different connectors
Customizing the query builder’s user interface is necessary to achieve this, as our default interface groups conditions and connects them using a single connector. However, separate connectors are needed to link each condition and group.
This customization is accomplished by modifying the rule container and group header using rule and header templates, respectively.
The following image illustrates the query builder’s user interface to support this customization.
Customize the user interface of a group header
Customize the user interface of a group header by using the headerTemplate property. This customization involves rendering only radio buttons for connecting groups, while the options to add conditions/groups are displayed within the rule container. To achieve this interface, the group header for the root group and the first group’s header should be hidden. The actionBegin event manages the header creation, and the grpConditionChange event updates the connectors to the rule model.
Refer to the following code example; radio buttons are integrated using the header template property of a query builder, which helps to render only radio buttons without add/ delete options.
app.component.html
<ejs-querybuilder id="querybuilder" (actionBegin)="actionBegin($event)">
<ng-template #headerTemplate let-data>
<div class="e-groupheader">
<ejs-radiobutton id="{{data.ruleID}}_radio1" name="{{data.ruleID}}_andor" label="AND" value="and" [checked]="data.condition == 'and'" (change)="grpConditionChange($event)"></ejs-radiobutton>
<ejs-radiobutton id="{{data.ruleID}}_radio2" name="{{data.ruleID}}_andor" label="OR" value="or" [checked]="data.condition == 'or'" (change)="grpConditionChange($event)"></ejs-radiobutton>
</div>
</ng-template>
</ejs-querybuilder>
app.component.ts
In the following code example, the radio button component gets updated in the actionBegin event. This event is triggered when we’re adding groups. Also, we bind the radio button’s change event to update the rule model whenever there’s a change.
export class AppComponent {
…
actionBegin(args: any): void {
let target: HTMLElement; let childElems: Array<HTMLElement>;
let group: RuleModel;
if (args.requestType === 'header-template-initialize') {
target = document.getElementById(args.groupID) as HTMLElement;
if (target) {
childElems = Array.prototype.slice.call(target.querySelector('.e-rule-list')!.children);
if (childElems.length && childElems[childElems.length - 1]) {
group = this.qryBldrObj.getGroup(childElems[childElems.length - 1] as HTMLElement);
// To update the group condition user interface
args.condition = group.condition as string;
}
}
}
}
// To update the condition to connect the groups
grpConditionChange(args: any): void {
let ruleModel: RuleModel = this.qryBldrObj.getGroup(closest(args.event.target, ".e-group-container").previousSibling as HTMLElement);
ruleModel.condition = args.value;
}
}
app.module.ts
import { RadioButtonModule } from '@syncfusion/ej2-angular-buttons';
@NgModule({
imports: [
RadioButtonModule
]
})
app.component.css
.e-query-builder .e-group-container:first-child > .e-group-header,
.e-rule-list .e-group-container:first-child .e-group-header{
display: none;
}
Customize the user interface of a rule container
The rule container is default rendered with field, operator, and value controls. However, in this case, we also need to render radio buttons for connecting conditions and buttons to add/delete conditions and groups using the ruleTemplate property. These customizations can be handled in the actionBegin event.
We utilized single templates for all the columns, so template mapping can be performed programmatically using the dataBound event.
In the following code example, we use the ruleTemplate property of a query builder to render the required components for creating filters, which includes the creation of connectors via radio buttons.
app.component.html
<ejs-querybuilder id="querybuilder" (actionBegin)="actionBegin($event)" (dataBound)="dataBound()">
…
<ng-template #ruleTemplate let-data>
<div>
<div *ngIf="data.rule.custom.isRule===true" class="e-rules">
<div class="e-rule-header">
<ejs-radiobutton id="{{data.ruleID}}_rulerb1" class="e-and-condition" name="{{data.ruleID}}_andor" label="AND" value="and" [checked]="data.group.condition == 'and'" (change)="ruleConditionChange($event)"></ejs-radiobutton>
<ejs-radiobutton id="{{data.ruleID}}_rulerb2" class="e-or-condition" name="{{data.ruleID}}_andor" label="OR" value="or" [checked]="data.group.condition == 'or'" (change)="ruleConditionChange($event)"></ejs-radiobutton>
</div>
</div>
<div class="e-rule e-rule-template">
<div class="e-rule-filter">
<ejs-dropdownlist [dataSource]="data.columns" id="{{data.ruleID}}_filter" (change)="fieldChange($event)" [fields]="fields" [value]="data.rule.field">
</ejs-dropdownlist>
</div>
<div class="e-rule-operator e-operator">
<ejs-dropdownlist id="{{data.ruleID}}_operator" (change)="operatorChange($event)" [fields]="data.operatorFields" [dataSource]="data.operators" [value]="data.rule.operator">
</ejs-dropdownlist>
</div>
<div *ngIf="data.rule.type ==='number'" class="e-rule-value e-value e-custom-value">
<ejs-numerictextbox [value]="data.rule.value" format="####" (change)="valueChange($event)"></ejs-numerictextbox>
</div>
<div *ngIf="data.rule.type ==='string'" class="e-rule-value e-value e-custom-value">
<ejs-textbox [value]="data.rule.value" id = "{{data.ruleID}}_valuekey0" (change)="valueChange($event)">
</ejs-textbox>
</div>
<button *ngIf="data.rule.custom.isGroup!==true" class="e-removerule e-rule-delete e-css e-btn e-small e-round" (click)="removeRule($event)">
<span class="e-btn-icon e-icons e-delete-icon"></span>
</button>
</div>
<div class="e-group-btn">
<input *ngIf="data.rule.custom.isGroup===true" id="{{data.ruleID}}_addGroup" type="button" value="Add Group" class="e-flat e-btn e-small" (click)="addGroup()" />
<input *ngIf="data.rule.custom.isGroup===true" id="{{data.ruleID}}_addRule" type="button" value="Add Rule" class="e-flat e-btn e-small" (click)="addRule($event)" />
<input *ngIf="data.rule.custom.isGroup===true" id="{{data.ruleID}}_removeGroup" type="button" value="Remove Group" class="e-flat e-btn e-small" (click)="removeGroup($event)" />
</div>
</div>
</ng-template>
</ejs-querybuilder>
app.component.ts
The components created are updated in the actionBegin event, which triggers while rendering templates when rules are inserted. We bind the change event for all the components to update the rule model when their values change.
export class AppComponent {
@ViewChild('ruleTemplate')
public ruleTemplate!: TemplateRef<any>;
public fields: Object = { text: 'label', value: 'field' };
public operatorFields: Object = { text: 'text', value: 'value' };
actionBegin(args: any): void {
// Header Template to render the group container
…
// Header Template related codes ended
// Rule Template to render the rule container
if (args.requestType === 'template-initialize') {
args.columns = this.qryBldrObj.columns;
args.rule.operator = 'equal';
const group: RuleModel = this.qryBldrObj.getGroup(args.ruleID.split("_")[1]);
const grpId: string = args.ruleID.split("_")[0] + '_' + args.ruleID.split("_")[1];
let condition: string = '';
let ruleElem: HTMLElement = document.getElementById(args.ruleID) as HTMLElement;
if (ruleElem && ruleElem.previousSibling) {
const rule: RuleModel = this.qryBldrObj.getRule(ruleElem.previousSibling as HTMLElement);
if (rule && rule.condition) {
condition = rule.condition;
}
}
args.group = {condition: condition, not: group.not, groupID: grpId };
// To handle the Add Rule/ Add Group/ Delete Group option
if (isNullOrUndefined(args.rule.custom)) {
if (condition != '') {
args.rule.custom = { isRule : true };
} else {
args.rule.custom = { isGroup: true };
}
}
if (args.rule.type === '') {
args.rule.type = 'string';
}
}
}
dataBound(): void {
this.updateRuleTemplate(this.qryBldrObj.columns);
}
// To update a single template to all the fields
updateRuleTemplate(columns: ColumnsModel[]): void {
for (let i: number = 0; i < columns.length; i++ ) {
if (columns[i].columns) {
this.updateRuleTemplate(columns[i].columns as ColumnsModel[]);
} else {
columns[i].ruleTemplate = this.ruleTemplate as any;
}
}
}
}
app.module.ts
import { NumericTextBoxModule, TextBoxModule } from '@syncfusion/ej2-angular-inputs';
import { DropDownListModule } from '@syncfusion/ej2-angular-dropdowns';
@NgModule({
imports: [
NumericTextBoxModule, TextBoxModule, DropDownListModule
]
})
The CSS changes were required to create a user interface similar to the one depicted above.
.e-rule-header,
.e-rule-template {
padding: 12px 0px 0px 12px;
}
.e-radio-wrapper {
padding-left: 10px;
}
.e-group-btn {
padding: 0px 0px 12px 12px
}
.e-query-builder .e-rule-list > .e-rule-container::before,
.e-query-builder .e-rule-list > .e-rule-container::after,
.e-query-builder .e-rule-list .e-group-container::before,
.e-query-builder .e-rule-list .e-group-container::after {
border: none;
}
.e-query-builder .e-group-body,
.e-query-builder .e-rule-list {
padding: 0;
}
.e-query-builder .e-group-header {
margin-left: 40%;
height: 32px;
width: 130px;
}
.e-query-builder .e-group-header .e-btn {
padding-right: 20px;
}
Updating the Rule Model: Changing Field, Operator, and Values Controls
Refer to the below code example to bind the events to the components which are rendered as templates to update the rule model when the components change.
app.component.ts
// To update the field
fieldChange(e: any): void {
this.qryBldrObj.notifyChange(e.value, e.element, 'field');
}
// To update the operator
operatorChange(e: any): void {
this.qryBldrObj.getRule(e.event.target).operator = e.value;
}
// To update the value
valueChange(e: any): void {
if (e.isInteracted) {
this.qryBldrObj.notifyChange(e.value, e.event.target, 'value');
}
}
//To update the connectors for the rule
ruleConditionChange(args: any): void{
let ruleModel: RuleModel = this.qryBldrObj.getRule(closest(args.event.target, ".e-rule-container").previousSibling as HTMLElement);
ruleModel.condition = args.value;
}
Refer to the below code example to insert/delete rules and groups using the buttons created using ruleTemplate and headerTemplate.
// To add the rule to the query builder
addRule(args: any): void {
let target: HTMLElement; let ruleList: Array<HTMLElement>;
let ruleModel: RuleModel; let grpId: string;
// Get the group container to get the previous rule
target = closest(args.target, '.e-group-container') as HTMLElement;
ruleList = Array.prototype.slice.call(target.querySelector('.e-rule-list')?.children);
// Get the previous rule
ruleModel = this.qryBldrObj.getRule(ruleList[ruleList.length - 1]);
// To update the condition in the previous rule to connect rules
ruleModel.condition = "and";
grpId = closest(args.target, '.e-group-container').id.split('_')[1];
// Create a rule to insert
let rule: any = {label: ruleModel.label, field: ruleModel.field, operator: "equal", type: ruleModel.type, custom: { isGroup: false, isRule: true }};
// To insert a new rule
this.qryBldrObj.addRules([rule], grpId);
}
// To add the group to the query builder
addGroup(): void {
let target: HTMLElement; let ruleList: Array<HTMLElement>;
let ruleModel: RuleModel;
// Get the group container to get the previous group
target = document.getElementById(this.qryBldrObj.element.id + '_group0') as HTMLElement;
ruleList = Array.prototype.slice.call(target.querySelector('.e-rule-list')?.children);
// Get the previous group
ruleModel = this.qryBldrObj.getGroup(ruleList[ruleList.length - 1]);
// To update the condition in the previous group to connect groups
ruleModel.condition = "and";
let column: ColumnsModel = this.qryBldrObj.columns[0];
// Create a rule to insert it as a group.
let rule: any = { label: column.label, field: column.field, operator: "equal", type: column.type, custom: {isGroup: true, isRule: false }};
// To insert a new rule
this.qryBldrObj.addGroups([{not: false, rules: [rule]}], 'group0');
}
// To remove the rule from the query builder
removeRule(args: any): void {
let ruleElem: HTMLElement = closest(args.target.offsetParent, '.e-rule-container') as HTMLElement;
let idColl: string[] = ruleElem.id.split('_');
this.qryBldrObj.deleteRules([idColl[1] + '_' + idColl[2]]);
}
// To remove the group from the query builder
removeGroup(args: any): void {
this.qryBldrObj.deleteGroup(closest(args.target.offsetParent, '.e-group-container'));
}
GitHub References
For more details, refer to the Advanced Query Building Techniques in Angular: Queries with different Connectors GitHub demo.
Conclusion
Thanks for reading! In this blog, we’ve explored how to add a query or condition with different connectors in the same group using Syncfusion Angular Query Builder. We appreciate your feedback, which you can leave in the comments section below.
If you’re an existing customer, you can download the latest version of Essential Studio from the License and Downloads page. If you’re not a Syncfusion customer, we invite you to try our 30-day free trial to explore our available features.
If you have any questions or need assistance, please don’t hesitate to contact us through our support forums, support portal, or feedback portal. We’re always here to help!
Top comments (0)