Using Query Builder Control

Query Builder control comes in hand when we need to filter data according to certain conditions on the server side. Find more information about the query builder control here. We will see how to configure and use it with relational as well as non-relational databases.

Process

  1. Creating database Connector query with dynamic filter variable
  2. (relational [MySql] / Non-Relational [MongoDB])
  3. Configuring Query Builder control
  4. using query builder output with database Connector query’s filter variable.

Relational Database [MySql]

Creating Database connectors and Queries

To setup Relational Database connectors, follow this article. Once our connector is ready,

  • click on + Add query
  • In the query section, provide the below given query

Select * from “Customers” {{filter}}

  • Here in place of “Customers” use your table’s name. The {{filter}} variable will be used for inserting query dynamically from query builder control.

  • Test and save the query.

Configuring Query Builder control

  • From the left panel drag and place a Query builder control.
  • Go to properties > Database and select SQL from dropdown.

  • For the Schema field below, provide a schema of your data. In our case for instance, the schema is

    [
    {
    “label”: “id”,
    “value”: “id”,
    “type”: “number”
    },
    {
    “label”: “Name”,
    “value”: “Name”,
    “type”: “text”
    },
    {
    “label”: “Companies”,
    “value”: “Companies”,
    “type”: “number”
    },
    {
    “label”: “Email”,
    “value”: “Email”,
    “type”: “text”
    },
    {
    “label”: “Website”,
    “value”: “Website”,
    “type”: “text”
    }
    ]

  • We define each column with Label, Value and Type. This is necessary to configure query builder control. Similarly you can create schema for your data.

Integrating Both to filter data

Now our connector and query builder is configured, all we are left with is connecting both of them to filter data. And here comes the final step

  • Drag and place a tablegrid control
  • Select tablegrid > data > connector > MySql Connector. Click Continue.
  • In the Filter Field, select querybuilder.Query Using Keywords
  • for MySql it will be sqlfilter.Query
  • Select Columns to bind and finally click on test and finish.

  • By default all the customers are displayed. Now if we apply a filter to see those customers having more than 5 companies and less than 20 companies, we do it using the query builder and we get the filtered result.

Non-Relational Database [NoSql]

Creating Database connectors and Queries

To setup Relational Database connectors, follow this article. Once our connector is ready,

  • click on + Add query
  • In the Filter section, provide a variable named Query, like given below.

{{query}}

  • Test and save the query.

Configuring Query Builder control

  • From the left panel drag and place a vQuery builder control**.
  • Go to **properties > Databasev and select NoSQL from dropdown

  • For the Schema field below, provide a schema of your data. In our case for instance, the schema is

    [
    {
    “label”: “id”,
    “value”: “id”,
    “type”: “number”
    },
    {
    “label”: “Name”,
    “value”: “Name”,
    “type”: “text”
    },
    {
    “label”: “Companies”,
    “value”: “Companies”,
    “type”: “number”
    },
    {
    “label”: “Email”,
    “value”: “Email”,
    “type”: “text”
    },
    {
    “label”: “Website”,
    “value”: “Website”,
    “type”: “text”
    }
    ]

  • We define each column with Label, Value and Type. This is necessary to configure query builder control. Similarly you can create schema for your data.

  • Additionally we can create Dynamic operators to introduce new conditions. This option is ONLY AVAILABLE FOR NoSQL database. This is used to add operators that are nor available in the query builder by default. For more information on the available query operators , refer here.

Integrating Both to filter data

Now our connector and query builder is configured, all we are left with is connecting both of them to filter data. And here comes the final step

  • Drag and place a tablegrid control

  • Select vtablegrid > data > connector > NoSql Connector**. Click Continue.

  • In the Filter Field, select querybuilder. Using Keywords

  • For NoSql it will be nosql.Query

  • Select Columns to bind and finally click on test and finish.

  • By default all the customers are displayed. Now if we apply a filter to see those customers having more than 10 companies, we do it using the query builder and we get the filtered result.