Available only in PRO Edition
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:
For example:
{
code: "fname = Alex",
values: [ "Alex" ]
};
You can pass two optional parameters to the toSQL() method:
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"
}
]
};
{
code: "fname = ?",
values: [ "Alex" ]
};
{
code: "fname = Alex",
values: [ "Alex" ]
};
The returned object has two properties:
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
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:
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