Fluent BQL– Increase the Readability & Maintainability of your Queries

In this post, we will provide a basic introduction to our Fluent Business Query Language which is a complementary technology to BQL. But before I do, let’s have a bit of a review of our Business Query Language (BQL) providing some context for those of you reading this post who may be new to the Acumatica platform.

To query and manipulate data from the Acumatica database, a developer will use BQL — part of the data access layer of the Acumatica Framework. BQL statements represent a specific set of Acumatica queries that are translated into the appropriate SQL required by the back-end database by the framework.  This shields the developer from the language nuances inherent to various database providers (allowing Acumatica to be database agnostic), and validates these queries at compilation time.

BQL solves two discrete and rather important problems developers often face.  One, a simple way to create queries from a set of predefined primitives, and two, declaring queries on an attribute level in C#. Both problems are solved through the usage of generic types.

Sequences of same-level elements in BQL are implemented very similarly to a linked list structure, where each next element must be passed as a generic parameter of the previous one. This allows a developer to build a query by combining primitive elements together without many limits.  Yet, often for this flexibility to be manifested, a developer will sacrifice readability, and hence maintainability.

GIST:

https://gist.github.com/lekker-solutions/61240fcb5b8d2595cb2e3655329e4827

It’s no wonder that developers prefer to rewrite most of their BQL queries from scratch, instead of trying to analyze and modify older ones. If we are honest, we can’t really blame them! Unlike a computer, a person cannot easily analyze heavily nested structures, especially when there is no specific need of a nested structure.

There are other issues with BQL such as “numbered” overloading of base query components, multiple select class families with overlapping structures, as well as the problem of balancing angle brackets of generic classes, correlating rather poorly with parentheses of native SQL queries.

All these problems point to the fact that BQL provides us with a way to produce an endless variety of queries.  For developers, however, it is very difficult to read and maintain complex BQL queries, due mostly to the structure of the BQL-language itself.

To address these problems, one of our engineering teams at Acumatica has come up with a solution by creating Fluent BQL.

What is Fluent BQL

Unlike BQL, which uses function-like generic class declarations, Fluent BQL uses fluent generic class declarations, based on the nesting of generic classes. This naturally splits and organizes the top-most components of a query such as joins and aggregates, and completely removes the ambiguity of command names. The query structure becomes more like the SQL structure, where each section does not depend on others and can appear only in the places assigned to it. Nesting of classes in a command definition helps to reduce the nesting of components in a command declaration.

By using FBQL, a developer doesn’t have to pick up a suitable command overload class.  Rather, just start typing a command and IntelliSense will offer continuations that are relevant for the current query state. Also, it should be noted that sections of a query are not separated by commas, which is a good thing since they are not equal in a certain sense, and their count can vary only within very discrete limits.

Now let’s take a look at some code to get a peek at FBQL.   With fluent comparisons, a developer has to use PX.Data.BQL.Bql[Type].Field<TSelf> as a base class for the field as illustrated below. This is already the default all across Acumatica today, however, you might find legacy code written by third parties that still have the IbqlField interface instead.

GIST: https://gist.github.com/lekker-solutions/0379133b6b0432e1717fc5e70a0dc005

Note that all Acumatica core constants and fields of all the DAC’s are already compatible with fluent comparisons.

Fluent Conditions

The Fluent conditions approach uses dot-separated chaining of Tunary conditions. All IbqlUnary classes, including fluent comparisons, of the Acumatica core, can be used with fluent conditions. To append a condition to another one, just use .And<Tunary> or .Or<Tunary> nested classes of the first condition expression.

GIST: https://gist.github.com/lekker-solutions/731f0b2462c5927b97851f4bd1ea61ce

BQL doesn’t have explicit brackets for conditions, but brackets could be appended by wrapping a part of a condition in an additional Where-clause, which is no trivial task. Together with chaining patterns, the absence of explicit brackets leads to a non-obvious, counterintuitive as well as difficult to maintain a representation of conditions.

FBQL brings a brackets class, which represents explicit brackets.  Further, what is pretty exciting is that now parentheses are represented by angle brackets of the new fluent .And<> and .Or<> classes. Because of this, there is only one case when you may need to use a brackets class explicitly ‒‒ it is when you start your condition with something enclosed in parentheses. If .And<> or .Or<> contains a single comparison, it does not become wrapped in parentheses.

GIST: https://gist.github.com/lekker-solutions/f931b92916d7c8fecc3870b75d065dd2

FBQL also has some very helpful more complex conditions not available (or made easier to use).

GIST: https://gist.github.com/lekker-solutions/36524f61e4dc38635c1e17425be25d8a

Some Comparisons between FBQL & BQL

* Joins are not actually a section of a query, in comparison to other its sections such as Where<>, Aggregate<>, and OrderBy<>, that are containers for some query elements.

** All views that contain aggregation are read-only by default.

GIST: https://gist.github.com/lekker-solutions/cde0f434d8e111e3a1b18eeb655d6e01

Foreign Keys in FBQL

Another very handy feature when designing a query with a lot of joins is the new foreign key definitions present on DACs. This will speed up the programming of a large query that queries a lot of tables. Let’s first take a look at how they are defined in the DAC:

GIST: https://gist.github.com/lekker-solutions/e37513feb79b7b9b5b5f2db1c449373a

Inside of the FK class defines another class, InventoryItemFK, which specifies that PAModelGenXRef.inventoryID is a foreign key reference to the Primary Key (PK) of the InventoryItem table. Let’s take a look at how that is defined there:

GIST: https://gist.github.com/lekker-solutions/73d00938acf89275f6e454f05f796ba3

We can use these definitions in FBQL.

GIST: https://gist.github.com/lekker-solutions/e17021994eed8e0fbd356bc857fecfbd

This might not be earth-shattering here, where the Foreign/Primary key is only a single field, but in instances where it is a composite key made up of 2 or 3 fields, it can reduce the size of the query significantly.

FBQL is Derived from BQL

The following three simple guidelines FBQL implements help to greatly increase the readability and the maintainability of FBQL-queries compared to classic BQL-queries.

1. Use a fluent generic class declaration pattern, instead of a classic function-like generic class declaration pattern.
2. Use containers (or arrays) of components, rather than chains (or linked lists) of components.
3. Use of one family of Selects and single Search and Views that are based on the Select-family, instead of three similar families.

Yet it is important to keep in mind that FBQL doesn’t replace BQL – FBQL complements it!  They can be used together in the same file or class, or even query (you can use FBQL . Where<> in a Bql Where statement, etc, without any naming conflicts. Moreover, FBQL is based on BQL, since the FBQL-command delegates all the querying work to a corresponding BQL-command. All that FBQL tries to achieve is a much higher level of convenience for developers.

Summary

We have spent time in this post introducing you to one of our developer technology offerings, the Fluent Business Query Language – FBQL, and hopefully articulated its value to developers building applications and integrations with Acumatica’s robust Cloud-based ERP platform.  FBQL will help developers maintain a library of queries by making them much more easily readable. This is accomplished fundamentally by changing the inherent structure of the BQL language itself.  It doesn’t replace BQL, per se, but offers a complimentary query language that can be used together, in a convenient fashion, enabling developers to increase their efficiency in writing and maintaining an array of complex queries.

Here’s a summary of the key features:

• Use of a fluent generic class declaration pattern, instead of a classic functional-like generic class declaration pattern;
• Intuitive segregation of query sections;
• Improved, simplified, and intuitive condition building;
• Strongly typed fields/constants/functions and static type-checking in conditions;
• Use of options/hints instead of numbered overloads of query components;
• Infix binary operations;
• The natural balance of angle brackets; and
• Native support of IntelliSense.

We hope this has been helpful – especially now that this post has been updated since we originally posted back in 2018.

Happy Coding!