Home > Help center > API > QueryBuilder

QueryBuilder

What is Query Builder

The QueryBuiler is very powerful functionality that gives the ability to the user to make complex SQL 'select' queries without dropping the security. The query builder uses pure GET params to construct queries. It supports all where clauses, order, group by, count, limit, and event joining tables.

For the example we will use the PHP function http_build_query, that converts the array to URL compatible strings.

If you are using different language search for similar function or check the algorithm implemented in this javascript alternative:

var build_query = function(obj, num_prefix, temp_key) {
    var output_string = []
    Object.keys(obj).forEach(function(val) {
        var key = val;
        num_prefix && !isNaN(key) ? key = num_prefix + key : ''
        var key = encodeURIComponent(key.replace(/[!'()*]/g, escape));
        temp_key ? key = temp_key + '[' + key + ']' : ''
        if (typeof obj[val] === 'object') {
            var query = build_query(obj[val], null, key)
            output_string.push(query)
        } else {
            var value = encodeURIComponent(obj[val].replace(/[!'()*]/g, escape));
            output_string.push(key + '=' + value)
        }
    })
    return output_string.join('&')
}

Example

Resource

First of all we have to locate the needed resource. For the example, this pattern will be used:

https://api.billia.io -> Database -> Order

So the URL looks like this:

http://your-prime-instance.io/database/order

Request Function

Next step is to create URI parameters and send them to the server. The easiest way to keep the the code readable and system syntax, in the same time, is to use httpbuildquery (in php) or its equivalent.

$params = http_build_query(
  array(
    'where' => array(
      array(
        'field' => 'status',
        'where' => 'and',
        'type' => 'equalTo',
        'value' => 'pending'
      ),
    )
  )
);
echo $params;
where%5B0%5D%5Bfield%5D=status&where%5B0%5D%5Bwhere%5D=and&where%5B0%5D%5Btype%5D=equalTo&where%5B0%5D%5Bvalue%5D=pending
http://your-prime-instance.io/database/order?where%5B0%5D%5Bfield%5D=status&where%5B0%5D%5Bwhere%5D=and&where%5B0%5D%5Btype%5D=equalTo&where%5B0%5D%5Bvalue%5D=pending

Final result

The result looks like as follows:

status 200 - The request was fulfilled.

GET /database/order?where%5B0%5D%5Bfield%5D=status&where%5B0%5D%5Bwhere%5D=and&where%5B0%5D%5Btype%5D=equalTo&where%5B0%5D%5Bvalue%5D=pending HTTP/1.1
Accept: application/json

Operators and clauses

Count

$params = http_build_query(
	array(
    'count' => true;
	)
);

Where

Where is consistent function, which has several clauses

ClauseSQL analogDescription
equalTo=Checks if the values of two operands are equal or not, if yes then condition becomes true.
notEqualTo<> or !=Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
lessThan<Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.
greaterThan>Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.
lessThanOrEqualTo<=Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.
greaterThanOrEqualTo>=Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.
likeLIKEThe LIKE operator is used to compare a value to similar values using wildcard operators.
notLike-The notLike operator is used to compare a value to similar values using wildcard operators.
expression-The expression operator is used to integrate aditional function in the where clause.
literal-The literal operator is used for extremely complex queries and returns the same value.
isNull-Checks if the value is NULL.
isNotNull-Checks if the value is different from NULL.
inINThe in operator is used to compare a value to a list of literal values that have been specified.
notIn-The notIn operator works the same way the in operator does, but logically the opposite.
betweenBETWEENThe between operator is used to search for values that are within a set of values, given the minimum value and the maximum value.

Templates for all clauses:

equalTO

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'equalTo',
			'value' => ''
		],
	]
]);

notEqualTo

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'notEqualTo',
			'value' => ''
		],
	]
]);

lessThan

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'lessThan',
			'value' => ''
		],
	]
]);

greaterThan

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'greaterThan',
			'value' => ''
		],
	]
]);

lessThanOrEqualTo

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'lessThanOrEqualTo',
			'value' => ''
		],
	]
]);

greaterThanOrEqualTo

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'greaterThanOrEqualTo',
			'value' => ''
		],
	]
]);

like

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'like',
			'value' => ''
		],
	]
]);

notLike

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'notLike',
			'value' => ''
		],
	]
]);

expression

$params = http_build_query([
	'where' => [
		[
			'field' => 'FIND_IN_SET(?,FIELD) > 0',
			'where' => 'and',
			'type' => 'expression',
			'value' => ''
		],
	]
]);

isNull

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'isNull'
		],
	]
]);

isNotNull

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'isNotNull'
		],
	]
]);

in

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'in',
			'values' => ['','']
		],
	]
]);

notIn

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'notIn',
			'values' => ['','']
		],
	]
]);

between

$params = http_build_query([
	'where' => [
		[
			'field' => 'example',
			'where' => 'and',
			'type' => 'between',
			'from' => 'null',
			'to' => 'null',
		],
	]
]);

Nested Where Clause

New version of the QueryBuilder supports nested where clauses.

$params = http_build_query([
 'where' => [
  [
   'nest' => 'open',
   'field' => 'resource',
   'where' => 'and',
   'type' => 'equalTo',
   'value' => 'order_purchase'
  ],
  [
   'nest' => 'close',
   'field' => 'resource_id',
   'where' => 'or',
   'type' => 'equalTo',
   'value' => '805 '
  ],
  [
   'nest' => 'open',
   'field' => 'resource',
   'where' => 'and',
   'type' => 'equalTo',
   'value' => 'order'
  ],
  [
   'nest' => 'close',
   'field' => 'resource_id',
   'where' => 'and',
   'type' => 'in',
   'values' => [947, 948]
  ],
 ]
]);

Nest opening is done via: "nest": "open"

And the closing via: "nest: "close"

SELECT `audit_logger`.* FROM `audit_logger` WHERE ((`resource` = ‘order_purchase’ AND `resource_id` = ’805 ’) OR (`resource` = ‘order’ AND `resource_id` IN (‘947’, ‘948’))) AND `user_id` = ‘1078’ AND `audit_logger`.`trash` = ‘0’ORDER BY `id` DESC

{: .warning} Note: The user_id and trash where clause are automatically added by the security layer of the system. The query builder is controlling the where in the main brackets.

Order

The order keyword is used to sort the result-set by one or more columns.

$params = http_build_query([
	'order' => [
		[
			'field' => 'amount',
                        'type' => 'greaterThan',
                        'value' => '0',
                        'sort' => 'DESC|ASC'
		],
                [
			'field' => 'amount',
                        'sort' => 'DESC|ASC'
		],
	]
]);

Group

$params = http_build_query(['group' => ['username']]);