Queries

A Query fetches information from app data and can perform some simple yet powerful operations.

Click > +New Query a new page opens

Query Type

Query can be selected on two types:

1. Standard
2. Advanced (ASQL)

Standard

Standard queries are built using the Appivo Query Builder. Queries can optionally use Parameters, which can be defined via user input.

Select records of

The model is essentially the starting point from which we can select attributes either directly from the model or by traversing relationships and selecting attributes of related records.

Selected attributes

When selecting attributes, no matter where in the query builder, this will by default select all attributes present in the Model and if we click on All Attribute Selected (Expand), we can see the entire list of all selected attributes. Here we can directly choose from its attributes but we can also traverse its relationships (listed in bold) and pick attributes of its related models.

Clicking on the plus icon lets you add another attribute in the selected attribute, where you can use functions like plus, minus, sum etc to get results from those multiple attributes.

Functions

Standard functions can be applied to selected attributes. Applying functions in a query is an efficient way to quickly manipulate data, as opposed to doing it in front end code.

Functions can also be combined using simple operators of +, -, /, and *. In addition to using attributes, functions can also use static values and parameters. This table contains a list of common function parameters.

Description of fields:

Pivots

Pivots provides the ability to apply functions to the query model and/or related models.  In addition to standard function abilities, pivots also support joins. Multiple pivot attributes can be added and query results will be grouped by the pivot attributes selected.

Description of fields:

Example: In an ecommerce app we may want to find the total amount invoiced to each customer. If the invoice has a relation to a Customer model it will have an attribute called “Customer_id” (the customer that owns the invoice). We can pivot our result on the Customer_id attribute to get a result that includes one resulting record each for every customer with the total invoiced amount for each customer.

Filter Criteria

Filters can be used to further narrow query results. When applying filters, attributes of the selected and related models can be used, as well as values and parameters.

Multiple filter criteria can be added and the result will work based on AND or OR conditions for the filter. AND will only fetch results which satisfies all the filtered criteria and OR will give us results if either of the filter criteria is met and not all.

If the model has a relationship to the User model, it will have a User_id attribute. We can filter on that saying that we want to get only records that have a User_id equalling a certain value. Now this value can be derived in a number of ways:

1. By providing a hardcoded value or by entering the query builder e.g. “1234567899”.
2. Referring to one of the predefined variables (such as “Current user” – which holds the id of the logged on user).
3. A custom defined variable is passed as an input to the query when executed. These parameters are defined on the parameters tab. Each parameter has a name, a data type and possibly a default value (the value that is assigned if no input for it is provided at the time of query execution).

When an attribute is selected we can choose to apply a function to its value. Functions which work on aggregates – i.e. a series of values of a selected attribute. 

Some aggregate functions are sum, maximum, minimum, average or the count of values (SUM, MAX, MIN, AVG, COUNT) etc.

Example: So if the stored date is 2022-04-5 we can select 2022 by declaring a selected YEAR function applied to our date attribute. Let’s say that our model is an invoice and it has an attribute for storing the total invoice amount. We could then apply the SUM function to that attribute to get the total of several invoices. Using an aggregation.

Example: assume we are selecting from a model that holds cars that are associated with a manufacturer which in turn is related to a country that has a name attribute). We can traverse the many relationships like this: Car -> Manufacturer -> Country -> Name. So here, for each Car we would get its country of origin.

Order By

Order by applies an order to the query results, based on one or more selected attributes, and by specifying ascending or descending order.  When using attributes from different models, a join type must also be selected.

We can have multiple Order By attributes and the result will be sorted using all those attributes. Here, the result of the query will be first sorted by id in descending order and then that sorted order will be sorted based on Name in ascending order.

Advanced

When Advanced (ASQL) is selected a new field appears, where a valid ASQL statement can be entered.

Parameters

Parameters are variables that can be used within queries. Each parameter has a name, a data type and possibly a default value (the value that is assigned if no input for it is provided at the time of query execution).

Description of fields: