Using the Query Editor

Overview

Queries are very complex, yet powerful tools that help you access your data. The query editor is an intuitive interface that makes creating queries a lot simpler. This article explains how to use the query editor to create an a!queryEntity().

There are six basic steps to creating a query:

  1. Select the data store entity that you want to query.
  2. Create or update rule inputs to pass information into the query.
  3. Create filters to limit the results to the conditions you specify.
  4. Set up the paging and sorting to limit the amount of rows returned and order them.
  5. Select the fields to include in the query.
  6. Test the query to make sure it is returning the desired information.

Open the Query Editor

The query editor can be opened from any new expression rule, or any expression rule that contains a single a!queryEntity() function. See Query Editor to learn more about what types of queries can be used with the query editor.

  • From the expression rule toolbar, click the query editor icon.

Query Editor Icon

If you are editing an existing query, all of the configurable sections of the editor display. If you are creating a new query, only the Source Constant section displays.

Select a Source Constant

The source constant defines which data store entity you want to query. In order to query a data store entity, you must first create a constant of type Data Store Entity that points to it. See Constants for additional information.

  • Start typing the name of the constant in the Source Constant picker and select the constant that points to the desired data store entity.

Once a constant is selected, the Query Results Preview section will display a preview of the data where you can view all of the fields in your data store entity.

Create Rule Inputs

If you want to use rule inputs for any of the settings in the query editor, you can create new rule inputs or edit existing ones directly from the query editor. You can use the query editor to change the test values of your rule inputs to test and troubleshoot your query.

  1. In the Query Results Preview section click CONFIGURE RULE INPUTS.
  2. Click Add New Rule Input.
  3. Configure the Name, Type, and whether or not it is an Array.
  4. Enter an Expression or Value to use for testing the query and then click Save.

Rule Inputs and Test Values

Configure Paging and Sorting

The query editor allows you to select the paging and sorting parameters in Basic mode using configurable fields. You can set limits on the amount of rows you retrieve and determine the order of the rows with paging and sorting.

The default page size of the query is 50 rows per page and the default startIndex parameter is 1.

Configure the Page Size

  1. To change the number of rows to retrieve, enter a different number in Rows Per Page.
  2. To retrieve all data, select All Rows.

Configure How the Data is Sorted

  1. In the Sort By field, select a field from the list.
  2. Select either In Ascending Order or In Descending Order.

    Paging & Sorting Section

  3. You can also change the way the table is sorted in the Query Results Preview section by clicking on the header column to sort by that field. This will apply the sort to the query, not just the preview.

If you need more advanced paging and sorting settings, select Expression to configure additional settings. For additional information on specifying paging and sorting as an expression, see a!pagingInfo().

Create Filters

Filters allow you to select which results to return from your query based on conditions you specify. The query editor allows you to easily set up filters using four criteria: Field, Condition, Value, and Apply Filter.

If you want skip filters that have an empty value, select the Ignore Filters with Empty Values check box. See a!queryLogicalExpression() for more information on ignoring filters with empty values.

  1. Click the Add Filter link to add a new filter row.
  2. From the Field list, select the field that you want to use to filter the data.
  3. From the Condition list, select the operator to apply to the filter.
  4. Set the Value by selecting a rule input from the list, creating a new one, or clicking the expression editor icon Expression Rule Icon.
    • If the value is defined using the expression editor and you want to define it using a rule input instead, click the value to open the expression editor, then click Clear expression (use selection instead). The rule input list and expression editor icon display.
  5. To apply conditional filters, in the Apply Filter column click the condition link (shown as either Always or When). Enter an expression that will result in either true or false and then click OK.

    Apply Filters is useful in situations where you want to apply the filter only if a particular condition is met. For example, if the user belongs to a specific group.

If you have more than one filter, use the up and down arrows to change the order in which the filters are applied. You can also delete a filter by clicking X.

If you need more advanced filters, select Expression to create your filters manually. See Query Recipes for a few examples of advanced filters.

Filters Section

Select Fields

Select Fields allows you to select which fields will be returned from your query. By default, the query editor fetches all fields so designers can see a query preview with all available fields. However, for performance reasons, it is a good practice to only query the fields that you need. If you query all of the fields, it may take longer for your query to run.

To configure the fields of your query:

  • To select or clear a field, click the check box next to it. You can also click ADD ALL FIELDS or CLEAR ALL FIELDS to select or clear all fields.

Preview the Query

You can preview the results of your query by clicking RUN QUERY. The results will display. If Fetch total row count is selected, a parameter will be applied to the query that will retrieve the total number of rows that are returned by the query.

Fetch total row count is applied to the query, not just the preview. If you have a lot of data, it is recommended to turn off Fetch total row count to improve performance.

The query preview only displays 10 rows at a time. This may differ from the number of rows per page in the Paging & Sorting section as it is only intended to be a preview.

If the query filters out all data, Query returned no data displays.

FEEDBACK