Available only in PRO Edition

Filtering and Sorting Data Widgets

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.

Using Query Builder as a Separate 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);

Example for DataTable

Let's have a look at an example of using Query Builder with DataTable in detail.

  • Let's assume that we have the following datatable:
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}
    ]
})
  • Now we need to initialize a Query Builder and set the fields values corresponding to the necessary dataset property names:
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" }
    ]
})
  • Then we will add a button and specify the filtering logic in its handler function:
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.

Related sample:  Basic usage

Example for Pivot

In this section we will discuss an example that shows how to use Query Builder with Pivot. Our steps will be as follows:

  • First, we will create a Pivot with data
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:[]
    }
})
  • Then we will initialize a Query Builder and set the fields values corresponding to the necessary dataset property names
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" }
    ]
})
  • Finally, we will specify the handler for event that will fire on some changes in filters
$$("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

Embedding Query Builder into DataTable

You can use the Query Builder widget as a built-in filter for DataTable. It works the same as any other DataTable filter.

  • specify the datatable configuration
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}
    ]
})
  • set the necessary fields for Query Builder
var fields = [
    { id:"rating", value:"Rating", type:"number" },
    { id:"title", value:"Title",  type:"string" },
    { id:"votes",  value:"Votes", type:"number" }
];
  • set the query builder as a filter in the datatable header by using the content property. Use the "queryBuilder" type of filter for this purpose. Also you need to specify the dataset fields that will be used for filtering.
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.

Defining the popup configuration

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

Sorting Data Widgets with Query Builder

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.

Related samples

Back to top