Baasic REST endpoints provide an SQL-like query language called Baasic Query Language (BQL) which can be used for searching through available resources.
Baasic is, among other, a modular platform for developing web and mobile applications and some of its modules utilize Baasic Query Language (BQL) for easier and more profound data search. It provides filtering and sorting functionality for built-in and dynamically extended models. Also, it gives users the ability to manipulate data in a more flexible way and is commonly used when built-in filtering is insufficient. If the built-in filters are specified and BQL query is provided, Baasic tries to combine both filter options and return results accordingly.
It is important to mention that BQL is like a trimmed-down version of the SQL, so some of the SQL features, like nested queries, joins and similar, are not supported.
BQL support in Baasic modules
Baasic Query Language feature is supported in the following modules: Dynamic Resources, Articles, and Users, while other modules have this feature on the roadmap. While working with it, you should keep in mind that every Baasic model can be extended on the fly, which means you can add fields to both dynamic and built-in models. Dynamic Resources offer BQL support on dynamic fields, while Users and Articles have integrated fields and BQL support on both dynamically extended and integrated fields.
Some SQL statements are supported, like
ORDER BY, and
Support for these statements is different across modules - Dynamic Resources module supports all four statements, while those with integrated fields can only support
Supported Operators & Functions
Different operations and functions can be used to manipulate data and get results. Operators are predefined characters or keywords that make conditional operations, mainly in the
WHERE clause, and are used for comparisons and arithmetic operations, same as in the SQL. Both operators and functions can be used with parentheses or as a combination of more than one condition.
These are supported operators:
- Arithmetic :
Functions are also used for data manipulation and getting the result, but the format is different.
Built-in functions are:
- Aggregate functions:
- Group functions:
CAST is supported too and is used for converting one value type into another.
The data type determines value type of data which is defined by the schema (dynamic resource) or is passed in the Users module.
There are different sorts of data types, such as:
- Other: UUID in short uuid format (UniqueIdentifier)
We talked about the usage of BQL language in the Dynamic Resources Module article.
Examples shown below are presented within that same module, using a dynamic schema with following properties:
- name -
- dateCreated -
- rating, number, and popularity -
The easiest way to follow these examples is to create your own schema and a few resources to test the queries.
SELECT MAX(CAST("rating" as int)) as "MaxNo" WHERE "number" = '3' AND "popularity" = '2'
This query will find maximum rating where
number value is 3 and
popularity value is 2.
WHERE, LIKE, ORDER BY
WHERE "name" LIKE '%edi%' ORDER BY "dateCreated" DESC
This query will return all items that have value “edi” in any part of the
Percentage characters define which part of the
name value we are searching for (e.g., if we write
WHERE "name" LIKE '%edi', all items ending with “edi” will be displayed - wildcard phrases). Also, items are sorted by the property
dateCreated in descending order (for sorting in ascending order, write
ASC instead of
Parentheses, GROUP BY
WHERE ("name" LIKE 'edi%' OR "rating" = 1) AND "popularity" = 4 GROUP BY "rating"
This example shows the possibility of combining multiple conditions within parentheses - and finds all items which have
popularity value 4, and
name that starts with “edi” or has
rating equal to 1. All together, the resulting items are grouped by the
COUNT("popularity") WHERE "popularity" = 6
COUNT returns the count of the rows for a given condition.
This one sums all the values of the
popularity field in items.
WHERE CAST("dateCreated" AS DATETIME) <= CAST('2018-02-08T20:07:30.482Z' AS DATETIME)
Baasic saves all date values in the UTC time zone and then converts them to ISO format in order to compare dates.
This was a short introduction to the Baasic Query Language with examples on how to use it in Baasic modules. You can read more in the documentation - feel free to comment below and stay tuned for more updates.