Database Queries
Last updated
Last updated
In Zingy, you can define your own Database Queries associated with Database Tables.
Database Queries enable you to access the table's data with specific criteria, allowing your app to retrieve data quickly and efficiently.
To manage the queries for a Database Table, click on the table and select the Queries option from the popup menu.
An example of the Database Query screen is shown in the image below.
In the above image, the Database Query screen lists all the currently saved queries (2) and allows:
Addition of new Queries (1)
Editing Queries (3)
Deleting Queries (3)
The Query Editor allows you to create new queries or edit existing ones. It consists of the following (refer to the image above):
Query Name (1) : Specify the unique name of the query. Please note that the query name cannot be changed after the query is created.
The search criteria consists of a match expression. The expression can be something as simple as FirstName equals Input-1 , which will list records where the FirstName field exactly matches the provided input parameter (Input-1).
More complicated expressions with logical combinations (AND, OR, NOT) can also be constructed (more on this topic later in this page).
If you are adding your first match expression for the query, click on the button with the ➕ icon (1) as shown below. This will bring up the Match Expression editor.
The image below shows the Match Expression editor.
The match expression is created as follows:
Field (1): Choose one of the fields in the Database Table. The ID field is an automatic internal field that is part of every table. it provides an internally unique identifier for each record. The field listing will also show the ID field.
Condition (2): Choose one of the conditions for the match. The conditions shown are based on the type of the field selected.
=
Checks if the field's value is equal (exact match) to the provided input parameter. Works for all field types.
!=
Checks if the field's value is not equal (not an exact match) to the provided input parameter. Works for all field types.
>
Works for Numeric, Numeric (decimals) and Large Numeric field types. Checks if the field's value is greater than the provided input parameter.
<
Works for Numeric, Numeric (decimals) and Large Numeric field types. Checks if the field's value is less than the provided input parameter.
>=
Works for Numeric, Numeric (decimals) and Large Numeric field types. Checks if the field's value is greater than or equal to the provided input parameter.
<=
Works for Numeric, Numeric (decimals) and Large Numeric field types. Checks if the field's value is less than or equal to the provided input parameter.
matches pattern
Works for the Text field type.
Does a regular expression match (case insensitive global search) of the field's value with the provided input parameter.
Input parameter (3) to use for the match. You can either choose an existing parameter or add a new one.
Here are some examples of match expressions:
Price is less than Input-1
The value of the Price field is compared to the Input-1 parameter. If the value is less than the parameter, the record is output.
Email matches pattern specified in Input-1
The Input-1 parameter is used as a regular expression. A case insensitive global search is conducted on the value of the Email field. If the match succeeds, the record is output.
You can logically combine multiple match expressions using the boolean AND , OR and NOT operations.
Clicking on the logical combination button (1) will bring up a popup-menu showing options for the logical combination operations (2).
After selecting the logical combination, the chosen match expression will be displayed as nested inside the selected logical combination group, as illustrated in the above image.
You can now add more match expressions to the logical combination group by clicking in the ➕ icon (1) as shown below. This will bring up the Match Expression editor.
Just like a match expression, a logical combination group can also be grouped into another logical combination group using the the logical combination button (2) shown in the above image.
Examples of logical combination groups are provided below:
In this example, two match expressions, Email matches pattern specified in Input-1 and FirstName matches pattern specified in Input-1 are joined using an OR group.
This will lead to the output of records where either the Email or the FirstName field's values correspond with the regular expression search using the Input-1 parameter.
Here, the two match expressions, Price is greater than Input-1 and Name matches pattern specified in Input-2 are merged using an AND group.
This will lead to the output of records where the Price field's value exceeds the Input-1 parameter and the Name field's value aligns with the regular expression search using the Input-2 parameter.
In this example, an AND group contains an OR group and the match expression PhoneNumber matches pattern specified in Input-2 . The OR group includes two match expressions: FirstName matches pattern specified in Input-1 and LastName matches pattern specified in Input-1 .
This will result in the output of records where the PhoneNumber field's value aligns with the regular expression specified in Input-2, AND either the FirstName or LastName fields correspond with the regular expression search using the Input-1 parameter.
Typically, when reading from the app's database, all the fields of the table are displayed. However, the Field Selection tab enables you to select only the specific fields you want to be outputted by the query.
The image below shows the Field Selection tab with no fields selected. The table's fields are shown with a ➕ icon. The ID field is an automatic internal field that is part of every table. it provides an internally unique identifier for each record. The field listing will also show the ID field.
To select a field, click on the ➕ icon. The image below shows an example where two fields FirstName and PhoneNumber are selected. Selecting a field moves it out of the field listing and shows it in the selected section.
To deselect a field click on the 'x' icon. This will move the field from the selected section to the field listing.
By default, when you read from the app's database, records are listed in an internally determined order which typically is based on the time the record was created.
You can customize this behavior using the Sorting Order tab.
To add a field, click on the ➕ icon and select the type (Ascending or Descending). You can sort using multiple fields, in which case the sorting will take place in the order you added them. If two records have the same values for a field, the next field in the sorting list will be assessed, and so on.
The image below shows an example where two fields FirstName (ascending) and PhoneNumber (descending) are selected. Selecting a field moves it out of the field listing and shows it in the selected section.
In the above example, the records will be sorted by FirstName field in ascending order. Records with the same FirstName, will be sorted by PhoneNumber in descending order.
To deselect a field click on the 'x' icon. This will move the field from the selected section to the field listing.
