Baasic Query Language

Baasic REST endpoints provide an SQL-like query language called Baasic Query Language (BQL) which can be used for searching through available resources.

Introduction

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.

Supported elements

Some SQL statements are supported, like SELECT, WHERE, ORDER BY, and GROUP BY. Support for these statements is different across modules - Dynamic Resources module supports all four statements, while those with integrated fields can only support WHERE and ORDER BY.

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 : +, -, *, /
• Comparison: =, >, <, >=, <=, <>, LIKE, IN
• Logical: AND, OR

Functions are also used for data manipulation and getting the result, but the format is different.

Built-in functions are:

• Aggregate functions: AVG, MIN, MAX, SUM
• Group functions: COUNT
• Other: LEN, UPPER, LOWER

Expression CAST is supported too and is used for converting one value type into another.

Supported Types

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:

• Numeric: Bit, Integer, SmallInt, BigInt, Float, Real, Decimal, Numeric, Money
• Date: Date, Time, TimeStamp
• String: Text, Binary, Character
• Other: UUID in short uuid format (UniqueIdentifier)

Examples

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 - string type
• dateCreated - date type
• rating, number, and popularity - number type

The easiest way to follow these examples is to create your own schema and a few resources to test the queries.

SELECT, CAST

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 name property.

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 DESC).

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 rating property.

COUNT

COUNT("popularity") WHERE "popularity" = 6

COUNT returns the count of the rows for a given condition.

SUM

SUM("popularity")

This one sums all the values of the popularity field in items.

Date queries

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.