Database queries (QUERY tag)

Queries can be created to limit the number of datasets of a data source in the output or to output them sorted.

Syntax

A query starts with a QUERY tag. The QUERY tag also functions as the root element of the query.

Within the opening and closing QUERY tags additional tags can be specified which can be used to define the filter and sorting rules. All of these tags are included in the query.

The entityType parameter is used to specify the symbolic table name in the database schema so that the query can be applied to the datasets of the associated database table.

The syntax shown here can be used, for instance,

• in the queries themselves (in the Template Store under "Database schemata", with “Wizard Mode” disabled),
• with the contentSelect function, or
• for the FS_LIST, Database type input component (QUERY tag)

Templates for a query can be created for a database schema table with the help of a query (in the Template Store under “Database schemata”) in what is called “Wizard Mode”. The query result can be checked directly using the “Result” or “Result (release)” tab. If the user exits this mode after finishing the query (checkbox unchecked), the user will end up with the source code of the query. Some functions, such as sorting, cannot be set up using “Wizard Mode”. The syntax for these functions must be added manually.

Comparison values

A comparison value is required for some query types (such as when using EQ / NEQ, GT / LT, GTE / LTE, LIKE, see Table). This can be done using the FILTERPARAM tag or directly in the query tags EQ / NEQ, GT / LT, GTE / LTE and LIKE.

Comparison values in the contentSelect function

In a contentSelect function comparison values are specified in what is called the definition part, where

<CMS_VALUE_PARAM  name="IDENTIFIER" value="EXPRESSION" />

is used for variable content

or

<CMS_PARAM name="IDENTIFIER" value="CONSTANT"/>

is used for constants.

In this case, the FILTERPARAM tag has to be used in what is called the query part; for example:

<QUERY entityType="TABLENAME">
    <FILTERPARAM parameter="Salutation" datatype="java.lang.String" value="female"/>
        <EQ attribute="Salutation_EN" parameter="Salutation"/>
</QUERY>

Alternatively, comparison values can also be defined directly in the query part. In this case, no definition is required using <CMS_VALUE_PARAM ...> or <CMS_PARAM .../>. The comparison value is then specified using one of these tags: EQ, NEQ, GT, GTE, LT, LTE or LIKE; for example:

<QUERY entityType="TABLENAME">
    <EQ attribute="Salutation_EN" datatype="java.lang.String" value="female"/>
</QUERY>

Comparison values in queries

Query – entering comparison values

Two options for defining comparison values are also available in queries (in the Template Store under “Database schemata”): directly on the “Conditions” tab or by preassignment on the “Parameters” tab.

On the “Conditions” tab, comparison values can be specified or selected directly in the field of a condition column (see figure). The comparison value is then included directly in the query result.

The associated syntax is

<QUERY entityType="TABLENAME">
    <EQ attribute="Salutation_EN" datatype="java.lang.String" value="female"/>
</QUERY>

Here, the attribute, datatype and value parameters must be specified:
The name of the column in the schema to be included for the comparison is specified for the attribute.
The data type for the comparison is specified using the datatype parameter (see also the FILTERPARAM tag).
The value parameter is used to specify the comparison value.

Query – "Parameters" tab

If, on the other hand, the [new parameter] entry is selected from the drop-down list in a condition column, a comparison value can be created that will appear on the “Parameters” tab (see figure). The identifier is defined on the “Conditions” tab and the desired value is assigned on the “Parameters” tab.

This parameter provides, for instance, access to the “Data” tab of page references in which datasets from the affected table are included (“content projection”, see also example for Restricting the output). The value can be changed there directly in order to modify the query result.

The associated syntax is

<QUERY entityType=TABLENAME">
    <FILTERPARAM parameter="Salutation" datatype="java.lang.String" value="female"/>
        <EQ attribute="Salutation_EN" parameter="Salutation"/>
</QUERY>

In this case, the FILTERPARAM tag is required.

In the EQ tag, the name of the column in the schema to be included for the comparison is also specified for attribute. The parameter parameter is used to specify the identifier of the comparison value defined using FILTERPARAM.

DTD display

DTD display of the syntax for the QUERY tag:

<!DOCTYPE QUERY [
  <!-- "and" compare ("&&") -->
  <!ELEMENT AND (EQ|GT|GTE|IS_NULL|LIKE|LT|LTE|NEQ|NOTNULL|OR)+>

  <!-- equals ("==") -->
  <!ELEMENT EQ (#PCDATA)>
  <!ATTLIST EQ
    attribute CDATA #REQUIRED
    parameter CDATA #IMPLIED
    datatype CDATA #IMPLIED
    value CDATA #IMPLIED>

  <!ELEMENT FETCHMODE (#PCDATA)>
  <!ATTLIST FETCHMODE
    attribute CDATA #REQUIRED
    lazy (0|1) #REQUIRED>

  <!ELEMENT FILTERPARAM (#PCDATA)>
  <!ATTLIST FILTERPARAM
    parameter CDATA #REQUIRED
    datatype CDATA #REQUIRED
    value CDATA #REQUIRED>

  <!-- greater than (">") -->
  <!ELEMENT GT (#PCDATA)>
  <!ATTLIST GT
    attribute CDATA #REQUIRED
    parameter CDATA #IMPLIED
    datatype CDATA #IMPLIED
    value CDATA #IMPLIED>

  <!-- greater than or equals (">=") -->
  <!ELEMENT GTE (#PCDATA)>
  <!ATTLIST GTE
    attribute CDATA #REQUIRED
    parameter CDATA #IMPLIED
    datatype CDATA #IMPLIED
    value CDATA #IMPLIED>

  <!-- is null ("== null") -->
  <!ELEMENT IS_NULL (#PCDATA)>
  <!ATTLIST IS_NULL
    attribute CDATA #REQUIRED>

  <!-- "like"  compare ("%") -->
  <!ELEMENT LIKE (#PCDATA)>
  <!ATTLIST LIKE
    attribute CDATA #REQUIRED
    parameter CDATA #IMPLIED
    datatype CDATA #IMPLIED
    value CDATA #IMPLIED>

  <!-- lesser than ("<") -->
  <!ELEMENT LT (#PCDATA)>
  <!ATTLIST LT
    attribute CDATA #REQUIRED
    parameter CDATA #IMPLIED
    datatype CDATA #IMPLIED
    value CDATA #IMPLIED>

  <!-- lesser than or equals ("<=") -->
  <!ELEMENT LTE (#PCDATA)>
  <!ATTLIST LTE
    attribute CDATA #REQUIRED
    parameter CDATA #IMPLIED
    datatype CDATA #IMPLIED
    value CDATA #IMPLIED>

  <!-- not equals ("!=") -->
  <!ELEMENT NEQ (#PCDATA)>
  <!ATTLIST NEQ
    attribute CDATA #REQUIRED
    parameter CDATA #IMPLIED
    datatype CDATA #IMPLIED
    value CDATA #IMPLIED>

  <!-- not null ("!= null") -->
  <!ELEMENT NOTNULL (#PCDATA)>
  <!ATTLIST NOTNULL
    attribute CDATA #REQUIRED>

  <!-- "or" compare ("||") -->
  <!ELEMENT OR (AND|EQ|GT|IS_NULL|LIKE|LT|LTE|NEQ|NOTNULL)+>

  <!-- sorting order criteria -->
  <!ELEMENT ORDERCRITERIA (#PCDATA)>
  <!ATTLIST ORDERCRITERIA
    attribute CDATA #REQUIRED
    descending (0|1) #IMPLIED>

  <!-- root tag -->
  <!ELEMENT QUERY (AND|EQ|FETCHMODE|FILTERPARAM|GT|GTE|IS_NULL|LIKE|LT|LTE|NEQ|NOTNULL|OR|ORDERCRITERIA)*>
  <!ATTLIST QUERY
    entityType CDATA #REQUIRED>
]>

QUERY tags

The following tags can be used within an opening and closing QUERYtag:

FILTERPARAM tag

The FILTERPARAM tag enables the use of comparison values in the query.

Since these values are frequently values from a variable (e.g. from an input component, from the Site Store, etc.), the tag is the connection point between the database and FirstSpirit.

<FILTERPARAM parameter="IDENTIFIER" datatype="DATATYP" value="COMPARISONVALUE"/>

The parameter parameter in contentSelect functions

For the parameter parameter, the identifier of the parameter has to be specified as the value when used in a contentSelect function just like it is defined in the definition part (using the name attribute).

(definition part)
 <CMS_FUNCTION name="contentSelect" resultname="IDENTIFIER">
   <CMS_PARAM  name="schema" value="DATABASESCHEMANAME" />
   <CMS_VALUE_PARAM  name="from" value="st_startDate" />

(query part)
   <QUERY entityType="TABLENAME">
     <FILTERPARAM parameter="from" ... />
     ...
   </QUERY>
 </CMS_FUNCTION>

The parameter parameter in queries

When used in queries (in the Template Store under “Database schemata”), the identifier of the parameter on the Parameters tab (see figure) corresponds to the identifier defined using the parameter parameter.

datatype parameter

The datatype parameter is used to specify the data type of the value. The type should match the data type of the column in the schema so that an error does not occur when executing the query.

The following are typical data types:

• java.util.Date for date columns
• java.lang.String for string columns
• java.lang.Boolean for Boolean type columns
• java.lang.Double for double type columns
• java.lang.Integer for integer type columns
• java.lang.Long for long type columns

Special data types also exist for date columns:

• de.espirit.common.Now for the start time of deployment
• de.espirit.common.Today for Today.

Example of use in a contentSelect function:

<CMS_FUNCTION name="contentSelect" resultname="IDENTIFIER">
  <CMS_PARAM  name="schema" value="DATABASESCHEMANAME" />
  <CMS_VALUE_PARAM  name="from" value="st_startDate" />
  <QUERY entityType="TABLENAME">
    <FILTERPARAM parameter="from" datatype="java.util.Date" ... />
    ...
  </QUERY>
</CMS_FUNCTION>

The value parameter in queries

In queries (in the Template Store under “Database schemata”), the value parameter is mandatory. While the identifier of the parameter is specified using parameter, value specifies the (comparison) value.

The value parameter in contentSelect functions

In contentSelect functions, value is a preassigned value. This parameter should always be defined. It is required when no parameter has been defined with the identifier specified in parameter in the definition part.

Example:

<CMS_FUNCTION name="contentSelect" resultname="IDENTIFIER">
  <CMS_PARAM  name="schema" value="DATABASESCHEMANAME" />
  <CMS_VALUE_PARAM  name="from" value="st_startDate" />
  <QUERY entityType="TABLENAME">
    <FILTERPARAM parameter="from" datatype="java.util.Date" value="1143758280000" />
    ...
  </QUERY>
</CMS_FUNCTION>

FETCHMODE tag

Since a dataset in the query result may contain large files or a large volume of data – particularly in the case of foreign keys – it may make sense to load the foreign key data (known as the “dataset attribute”) only upon access or upon further processing of the data.

The FETCHMODE tag is available for changing the loading behavior of these types of attributes.

The tag has two parameters:

1. attribute
2. lazy

For attribute, the name of the column in the schema is specified for which the loading behavior is to be changed.

The lazy parameter specifies how the column data are to be loaded. lazy=1 means that the data are loaded as needed (= default setting). lazy=0 means that the data are loaded immediately.

ORDERCRITERIA tag

The ORDERCRITERIA tag is used to sort the results of a query. A sort criterion can be specified for this in each tag.

The mandatory parameter attribute species the table column that will serve as the basis for sorting.

Within the parameter, the %lang% placeholder can be used which is replaced by the abbreviation of the current language during a preview/generation process (e.g. DE, EN, etc.).

Example:

  <ORDERCRITERIA attribute="date"... />
  <ORDERCRITERIA attribute="text_%lang%"... />
  ...

In addition, it is possible to change the sort order using the descending parameter. If this parameter is not specified, the query results will be sorted in ascending order (descending="0"). If descending="1" is defined, the results will be sorted in descending order.

  <ORDERCRITERIA attribute="date" descending="0" />
  <ORDERCRITERIA attribute="text_%lang%" />
  ...

AND/OR tag

The AND/OR tag makes it possible to logically link together multiple query tags/rules, such as EQ , LIKE , and IS_NULL .

Tags that are specified in an AND tag are linked logically with AND; tags specified in an OR tag are linked logically with OR.

The nesting of AND and OR tags is possible only if they are alternated.

Example of nesting that is not permitted:

...
<AND>
  <AND>
     <EQ attribute="abbreviation" value="etc" />
  </AND>
</AND>
...

Example of nesting that is permitted:

...
<AND>
  <OR>
    <AND>
      <EQ attribute="abbreviation" value="etc" />
    </AND>
  </OR>
</AND>
...

In the following examples, all data for which Query1 and (Query2 or Query3) apply are output as these query results:

<AND>
  <QUERY1... />
  <OR>
    <QUERY2... />
    <QUERY3... />
  </OR>
</AND>

Tag SUBSELECT / NOT_SUBSELECT

Use the tag SUBSELECT to model an SQL query of the form

SELECT * FROM a WHERE attribute_a IN (SELECT attribute_b FROM b WHERE ...)

=>

<QUERY entityType="a" limit="10">
   <SUBSELECT attribute="attribute_a" subattribute="attribute_b">
    <QUERY entityType="b">
       ...
    </QUERY>
   </SUBSELECT>
 </QUERY>

Use the tag NOT_SUBSELECT to model an SQL query of the form

SELECT * FROM a WHERE attribute_a NOT IN (SELECT attribute_b FROM b WHERE ...)

=>

<QUERY entityType="a" limit="10"> 
   <NOT_SUBSELECT attribute="attribute_a" subattribute="attribute_b">
    <QUERY entityType="b"> 
       ...
    </QUERY>
   </NOT_SUBSELECT>
 </QUERY>

EQ (equal) tag

The EQ tag is used to define queries that return all datasets with content in the checked column that matches the comparison value (equal check).

The EQ tag can be defined directly or using a variable (see the section on comparison values).

Defined directly:

<QUERY entityType="TABLENAME">
    <EQ attribute="COLUMNNAME" datatype="DATATYPE" value="COMPARISONVALUE" />
    ...
</QUERY>

Using a variable:

<QUERY entityType="TABLENAME">
    <FILTERPARAM parameter="from" datatype="java.util.Date" value="1143758280000" />
    <EQ attribute="COLUMNNAME" parameter="from" />
    ...
</QUERY>

NEQ (not equal) tag

The NEQ tag is used to define queries that return all datasets with content in the checked column that does not match the comparison value (not equal check).

The NEQ tag can be defined directly or using a variable (see the section on comparison values).

Defined directly:

<QUERY entityType="TABLENAME">
    <NEQ attribute="COLUMNNAME" datatype="DATATYPE" value="COMPARISONVALUE" />
    ...
</QUERY>

Using a variable:

<QUERY entityType="TABLENAME">
  <FILTERPARAM parameter="from" datatype="java.util.Date" value="1143758280000" />
  <NEQ attribute="COLUMNNAME" parameter="from" />
    ...
</QUERY>

GT (greater than) tag

The GT tag is used to define queries that return all datasets with values in the checked column that are greater than the specified comparison value (greater than check).

The GT tag can be defined directly or using a variable (see the section on comparison values).

Defined directly:

<QUERY entityType="TABLENAME">
    <GT attribute="COLUMNNAME" datatype="DATATYPE" value="COMPARISONVALUE" />
    ...
  </QUERY>

Using a variable:

<QUERY entityType="TABLENAME">
  <FILTERPARAM parameter="from" datatype="java.util.Date" value="1143758280000" />
  <GT attribute="COLUMNNAME" parameter="from" />
  ...
</QUERY>

GTE (greater than or equal) tag

The GTE tag is used to define queries that return all datasets with values in the checked column that are greater than or equal to the specified comparison value (greater than or equal check).

The GTE tag can be defined directly or using a variable (see the section on comparison values).

Defined directly:

<QUERY entityType="TABLENAME">
  <GTE attribute="COLUMNNAME" datatype="DATATYPE" value="COMPARISONVALUE" />
  ...
</QUERY>

Using a variable:

<QUERY entityType="TABLENAME">
  <FILTERPARAM parameter="from" datatype="java.util.Date" value="1143758280000" />
  <GTE attribute="COLUMNNAME" parameter="from" />
    ...
</QUERY>

LT (less than) tag

The LT tag is used to define queries that return all datasets with values in the checked column that are less than the specified comparison value (less than check).

The LT tag can be defined directly or using a variable (see the section on comparison values).

Defined directly:

<QUERY entityType="TABLENAME">
  <LT attribute="COLUMNNAME" datatype="DATATYPE" value="COMPARISONVALUE" />
  ...
</QUERY>

Using a variable:

<QUERY entityType="TABLENAME">
  <FILTERPARAM parameter="from" datatype="java.util.Date" value="1143758280000" />
  <LT attribute="COLUMNNAME" parameter="from" />
  ...
</QUERY>

LTE (less than or equal) tag

The LTE tag is used to define queries that return all datasets with values in the checked column that are less than or equal to the specified comparison value (less than or equal check).

The LTE tag can be defined directly or using a variable (see the section on comparison values).

Defined directly:

<QUERY entityType="TABLENAME">
  <LTE attribute="COLUMNNAME" datatype="DATATYPE" value="COMPARISONVALUE" />
  ...
</QUERY>

Using a variable:

<QUERY entityType="TABLENAME">
  <FILTERPARAM parameter="from" datatype="java.util.Date" value="1143758280000" />
  <LTE attribute="COLUMNNAME" parameter="from" />
  ...
</QUERY>

IS_NULL tag

The IS_NULL tag is used to define queries that return all datasets that have no value in the checked column (null check).

<QUERY entityType="TABLENAME">
  <IS_NULL attribute="COLUMNNAME" />
  ...
</QUERY>

The attribute parameter must be specified to define the tag.

The name of the schema column to be used for the comparison is specified for attribute.

NOTNULL tag

The NOTNULL tag is used to define queries that return all datasets that have a value in the checked column (not null check).

<QUERY entityType="TABLENAME">
  <NOTNULL attribute="COLUMNNAME" />
  ...
</QUERY>

The attribute parameter must be specified to define the tag.

The name of the schema column to be used for the comparison is specified for attribute.

LIKE tag

The LIKE tag is used to define queries that return all datasets with values in the checked column that are similar to the specified comparison value (like check).

When specifying the comparison value, the % placeholder, which stands for any character desired, can be used.

Specifying %ouse therefore returns all words that end with ouse, such as mouse, house, etc.

The LIKE tag can be defined directly or using a variable (see the section on comparison values).

Defined directly:

<QUERY entityType="TABLENAME">
  <LIKE attribute="COLUMNNAME" datatype="DATATYPE" value="COMPARISONVALUE" />
  ...
</QUERY>

Using a variable:

<QUERY entityType="TABLENAME">
  <FILTERPARAM parameter="from" datatype="java.util.Date" value="1143758280000" />
  <LIKE attribute="COLUMNNAME" parameter="from" />
  ...
</QUERY>