The Query component acts as the central connector marshalling the data stored in the site before it is displayed. It is one of the core components in the system.
The Query provides a graphical interface to describe what will internally become a SQL query on the relational database underlying the site.
Whilst SQL is notoriously difficult to use directly, at no stage do you need to work with or even see the SQL code the query generates. Whilst for SQL experts we refer to some of the SQL join types that the Query generates, no knowledge of SQL is required.
The Query in the data handling flow
The Query takes as its input one or more sources. Each source is recordset – it consists of records, each record containing fields. Typically sources will be simple Table components, but they may also be Data Feeds or lookups feeding Table components.
Regardless of how many input sources it works with, a Query will always output a single recordset (the 'resultset'). Much of the Query configuration is concerned with how several sources can be joined together to form this single resultset.
Once the output recordset has been created, the Query passes it forward to one of a range of View components, where usage-specific configuration can be defined. The View is typically then embedded on a page surface, where criteria are applied, acting to narrow down the recordset to the particular record or records required.
The criteria are not defined in the Query itself, but at the Embed stage. (They are defined there because a single Query is often used in many places, and needs to retain the flexibility to use the criteria appropriate to the page it is used on)
Configuring a Query
The Query configuration interface is split into two tabs: Fields and Views
- The Fields tab is where most configuration is made
- The Views tab provides access to four bundled views: Grid, CSV (Comma separated values), Custom and Query by Example
The Fields tab
This tab is split into two panes:
- The top pane is the 'source area'.
- Below it is the 'fields list'.
Source area
The 'source area' displays the sources used by the query: each one is shown in a block. Each block has the name of the source at the top, followed by an alphabetical listing of its fields.
The source area can be split into several rows. Each row can then have its own set of sources. The Query then appends the resultsets from each row to form the final aggregate resultset. This allows a SQL Union Query to be defined.
Sources in the source area can be joined together in various ways to define SQL Inner Joins and SQL Cartesian Joins.
Learn more:
Fields list
The fields list contains the fields that are to be made available to the Views. The inclusion or otherwise of a field here will not affect whether a particular row in the resultset is included.
The order (lateral positioning) of the fields is used by the Grid and CSV views, and is taken into account when Sorting.
A field may be included several times. This is useful if the sort order precedence and the display order in a Grid view are in conflict (For example, if you have two fields, to be displayed in order as FirstName and LastName, but want to sort by LastName and then FirstName, you would add an extra LastName field to the left of the two fields, set to Sort on that, but set it not to Show)
Each field has a set of properties that can be defined:
Title
If set, the Title can be used:
to provide a heading row in Grid Views
to disambiguate several fields of the same name
Sort
Can be set to sort the resultset by this field, in ascending, Descending, or Random order. If the order is not important (for example, if criteria will later be used to restrict the resultset to a single record) then this should be left blank.
Learn more: Sorting results
Show
Defaults checked. If unchecked will prevent the field from being included in Grid and CSV Views, and will be hidden in other views.
Learn more: Showing and Hiding fields
Format
Provides formatting control over the field. Options are dependent on the datatype.
Learn more: Formatting Text fields, and Formatting Image fields
On-click link
If set will determine the behavior when the field is clicked. This can be used to make the fieldlink to another page, or to act as an Ajax trigger.
Learn more: Defining links in fields, and Ajax transitions
On-mouseover link
The same options as for 'on-click link', except the action is triggered when the user moves the mouse over the field: there is no need for them to click the field.
Total
When checked to enable this row, this can be used to return aggregate functions: Sum, Count, Min, Max, Expression. The default is Group By, which returns a record for each distinct value.
Grand Total
Provides for further aggregate functions: Sum, Min, Max and Count.