Start page / Template development / Template syntax / Database queries

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_INDEX input component with DatasetDataAccessPlugin
    (QUERY tag - Replacements of the placeholder %lang% cannot be considered in the query)"

Important 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, tab conditions

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, tab parameters

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:

Tag name

Definition

FILTERPARAM

Uses a comparison value in the query – with the definition of a fallback value.

FETCHMODE

Determines whether all data of a dataset are to be loaded immediately or upon access.

ORDERCRITERIA

Sorts the datasets of the query result.

AND / OR

Logically links multiple rules using AND or OR.

SUBSELECT / NOT_SUBSELECT

Restriction of the result set

EQ

The specified comparison value matches the value in the database column (equal check).

NEQ

The specified comparison value does not match the value in the database column (not equal check).

GT

The value in the database column is greater than the specified comparison value (greater than check).

GTE

The value in the database column is greater than or equal to the specified comparison value (greater than or equal check).

LT

The value in the database column is less than the specified comparison value (less than check).

LTE

The value in the database column is less than or equal to the specified comparison value (less than or equal check).

IS_NULL

No value is set for the database column (null check).

NOTNULL

A value is set for the database column (not null check).

LIKE

The specified comparison value is similar to the value in the database column (like check).

  

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>

© 2005 - 2024 Crownpeak Technology GmbH | All rights reserved. | FirstSpirit 2024.4 | Data privacy