Available only in PRO Edition
You can use Query Builder as a standalone solution for extented filtering of large datasets or combine it with Webix data widgets, e.g. DataTable and Pivot. What is more, this widget can be embedded into DataTable and used as a built-in filter.
In order to use a Query Builder to filter data components, you need to get the function that implements the filtering logic. You can do this with the help of the getFilterHelper method.
var filter_helper = $$("querybuilder").getFilterHelper();
The returned "helper" function iterates through the dataset items and checks whether they correspond to the filter rules. The "helper" function returns true if the item complies with the rule and false, if it doesn't.
After receiving the filtering function, you should pass it to the Webix filter method to enable filtering of data components with Query Builder. For example:
$$("$datatable1").filter(helper);
Let's have a look at an example of using Query Builder with DataTable in detail.
webix.ui({
view:"datatable",
columns:[
{ id:"title", header:"Film title", width:350, sort:"string"},
{ id:"year", header:"Released", width:200, sort:"int"},
{ id:"rating", header:"Rating", width:250, sort:"int"}
],
data:[
{ id:1, title:"The Shawshank Redemption", year:1994, rating:9},
{ id:2, title:"The Godfather", year:1972, rating:9},
{ id:3, title:"The Godfather: Part II", year:1974, rating:9}
]
})
webix.ui({
view: "querybuilder",
id: "querybuilder",
fields:[
{ id:"rating", value:"Rating", type:"number" },
{ id:"title", value:"Title", type:"string" },
{ id:"votes", value:"Votes", type:"number" }
]
})
var buttonFilter = {
view:"button",
value:"Apply Filter",
width:150,
click: function() {
if($$("querybuilder")){
helper = $$("querybuilder").getFilterHelper();
}
$$("$datatable1").filter(helper);
}
};
Thus, we have defined that on clicking the button the getFilterHelper() of the query builder will be called. It will return the helper function that implements filtering in the query builder.
This function will be passed as an argument to the filter() method called for the datatable. So, the datatable data will be filtered according to the rules set in the query builder.
In this section we will discuss an example that shows how to use Query Builder with Pivot. Our steps will be as follows:
webix.ui({
id:"pivot",
readonly: true,
view:"pivot",
height:400,
width:1500,
data:[
{"name": "Argentina", "year": 2005, "continent": "South America"},
{"name": "Austria", "year": 2006, "continent": "Europe"},
{"name": "Chad", "year": 2007, "continent": "Africa"}
],
readonlyTitle: "Oil Export Statistics",
max: true,
structure: {
rows: ["form", "name"],
columns: ["year"],
values: [{ name:"oil", operation:["min","sum"]}],
filters:[]
}
})
webix.ui({
view: "querybuilder",
id: "querybuilder",
fields:[
{ id:"name", value:"name", type:"string" },
{ id:"year", value:"year", type:"number" },
{ id:"continent", value:"continent", type:"string" }
]
})
$$("querybuilder").attachEvent("onChange", function() {
$$("pivot").filter($$("querybuilder").getFilterHelper());
});
Thus, in the event handler we have defined that when filter rules will be modified the filter() method will be called for Pivot. This method will make the query builder to re-filter data according to the currently set rules.
Related sample: Querybuilder as pivot filter
You can use the Query Builder widget as a built-in filter for DataTable. It works the same as any other DataTable filter.
webix.ui({
view:"datatable",
id: "datatable1",
columns:[
{ id:"title", header:"Film title", width:350, sort:"string"},
{ id:"year", header:"Released", width:200, sort:"int"},
{ id:"rating", header:"Rating", width:250, sort:"int"}
],
data:[
{ id:1, title:"The Shawshank Redemption", year:1994, rating:9},
{ id:2, title:"The Godfather", year:1972, rating:9},
{ id:3, title:"The Godfather: Part II", year:1974, rating:9}
]
})
var fields = [
{ id:"rating", value:"Rating", type:"number" },
{ id:"title", value:"Title", type:"string" },
{ id:"votes", value:"Votes", type:"number" }
];
webix.ui({
view:"datatable",
id: "datatable1",
columns:[
{ id:"title", header:"Film title", width:350, sort:"string"},
{ id:"year", header:"Released", width:200, sort:"int"},
{ id:"rating", header:"Rating", width:250, sort:"int"}
{
id:"category",
header:{ content:"queryBuilder", label:"Category", fields:fields },
width:100
},
]
})
The value of the queryBuilder filter can be stored in the state object, saved together with all other information about the current Datatable state and restored when it's necessary. Read more about saving of DataTable state in the related article.
You can change the width of the filtering popup by using the popupConfig attribute in the datatable header configuration:
webix.ui({
view:"datatable",
id: "datatable1",
columns:[
{ id:"dummy", width:40, header:
{ content:"queryBuilder", popupConfig:{width: 900},
maxLevel: 3, fields:fields }
},
{ id:"title", header:"Film title", fillspace:true, sort:"string"},
{ id:"year", header:"Released", width:100, sort:"int"}
// more columns
]
})
Related sample: Querybuilder as datatable filter
To sort data in data widgets with Query Builder, you need a helper function that provides sorting logic. You can do this with the help of the getSortingHelper method.
var sorting_helper = $$("querybuilder").getSortingHelper();
Read the details on the work of the sorting helper function in the related article.
After receiving the sorting function, you should pass it to the Webix sort method to enable sorting of data components with Query Builder. For example:
$$("$datatable1").sort(helper);
The common logic of enabling and manipulating sorting in Query Builder is described in the article Sorting Data.