Available only in PRO Edition

Converting Value to SQL

You can convert the rules object of the Query Builder value to SQL. The widget provides the related API for this purpose - the toSQL method. It returns an object with an SQL query string and an array of values.

var sql = $$("qb").toSQL();

The returned object has two properties:

  • code - an SQL query string
  • values - an array of values

For example:

{
    code: "fname = Alex",
    values: [ "Alex" ]   
};

You can pass two optional parameters to the toSQL() method:

  • config - (object) optional, a configuration object of the SQL query
  • rules - (object) optional, an object of rules for filtering

Configuring the returned object

The configuration object contains the placeholders attribute with the boolean value:

var sql = $$("qb").toSQL({placeholders:true});

It provides two variants of converting the rules object of the Query Builder value. For example, let's take the following object:

{
   "glue": "and",
   "rules": [
       {
          "key": "fname",
          "value": "Alex",
          "rule": "equal"
       }
    ]
};
  • if the placeholders attribute is set to true, the rules object of the Query Builder value is converted into an SQL query with placeholders instead of values, as follows:
{
    code: "fname = ?",
    values: [ "Alex" ]   
};
  • if the placeholders attribute is set to false, the rules object of the Query Builder value is converted into an SQL query with real values, like this:
{
    code: "fname = Alex",
    values: [ "Alex" ]   
};

The returned object has two properties:

  • code - an SQL query string
  • values - an array of values

Referring to the rules object

To refer to the rules object, you should get the first element of the value array returned by the getValue method. For example:

// converts Query Builder value into SQL query with placeholders instead of real values
var sql = $$("qb").toSQL({placeholders:true},$$("qb").getValue()[0]);

Related sample:  Extension to generate SQL code

Using Necessary SQL Operators

To specify particular SQL operators, you need to refer to the sqlOperators object. It contains the list of Query Builder rules and configuration objects of SQL operators that correspond to them.

The sqlOperators object contains a set of key:value pairs, where:

  • key - the name of the rule in Query Builder
  • value - an SQL operator object with necessary properties:
    • op - an operator;
    • sep - a separator;
    • mod - a mode that defines a pattern of search. The percent sign represents zero, one, or multiple characters;
    • no_val - without value.
sqlOperators: {
    equal:            { op: '= ?' },
    not_equal:        { op: '!= ?' },
    less:             { op: '< ?' },
    less_or_equal:    { op: '<= ?' },
    greater:          { op: '> ?' },
    greater_or_equal: { op: '>= ?' },
    between:          { op: 'BETWEEN ?',      sep: ' AND ' },
    not_between:      { op: 'NOT BETWEEN ?',  sep: ' AND ' },
    begins_with:      { op: 'LIKE(?)',        mod: '{0}%' },
    not_begins_with:  { op: 'NOT LIKE(?)',    mod: '{0}%' },
    contains:         { op: 'LIKE(?)',        mod: '%{0}%' },
    not_contains:     { op: 'NOT LIKE(?)',    mod: '%{0}%' },
    ends_with:        { op: 'LIKE(?)',        mod: '%{0}' },
    not_ends_with:    { op: 'NOT LIKE(?)',    mod: '%{0}' },
    is_empty:         { op: '= \"\"', no_val: true },
    is_not_empty:     { op: '!= \"\"', no_val: true },
    is_null:          { op: 'IS NULL', no_val: true },
    is_not_null:      { op: 'IS NOT NULL', no_val: true }
}
Back to top