Database Queries
Last updated
Last updated
In Zingy, you can define your own Database Queries associated with .
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).
The image below shows the Match Expression editor.
The match expression is created as follows:
=
!=
>
<
>=
<=
matches pattern
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
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.
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.
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.
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.
(2)
(3)
(4)
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.
Field (1): Choose one of the fields in the . 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 of the field selected.
Checks if the field's value is equal (exact match) to the provided input parameter. Works for all field .
Checks if the field's value is not equal (not an exact match) to the provided input parameter. Works for all field .
Works for Numeric, Numeric (decimals) and Large Numeric field . Checks if the field's value is greater than the provided input parameter.
Works for Numeric, Numeric (decimals) and Large Numeric field . Checks if the field's value is less than the provided input parameter.
Works for Numeric, Numeric (decimals) and Large Numeric field . 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 . Checks if the field's value is less than or equal to the provided input parameter.
Works for the Text field .
Does a match (case insensitive global search) of the field's value with the provided input parameter.
The Input-1 parameter is used as a . A case insensitive global search is conducted on the value of the Email field. If the match succeeds, the record is output.
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 .
The image below shows the Field Selection tab with no fields selected. The table's fields are shown with a icon. The 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 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 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.
