Start page
Start page
Online Documentation

Start page / Template development / Template syntax / Database queries

Database queries (tag QUERY)

Queries can be created to limit the number of data records for the output or to output them in with a certain sorting.

Syntax

A query is started with a QUERY tag. The QUERY tag is simultaneously the root element of the query.

All tags which are given between the opening and closing QUERY tag are taken into account in the query.

The entityType parameter is used to give the symbolic table name in the database schema so that the query can be applied to the data records of the corresponding database table.

The limit parameter can be used for limiting the result of the query to a certain number of data sets.

Example:

<QUERY entityType="tabelle" limit="10">
    <.../>
</QUERY>

The syntax which is presented here can be used for example in queries (“Template store” / “Database schemes” / “Query”, disabled “Wizard mode”), with the function contentSelect or for the input component CMS_INPUT_CONTENTLIST (QUERY tag).

Important Templates for a query can be created for a table of the database schema with the help of a query (“Template store” / “Database schemes” / “Query”) in the so-called “Wizard mode”. If this mode is quit (disable checkbox) after the query has been completed, you will get the source code of the query.
Some functions, for example sorting, can not be reproduced by the “Wizard mode”, the syntax for these functions must be added manually. Syntax which can not be represented in the “Wizard mode” will get lost if the “Wizard mode” will be enabled again.

DTD representation

The DTD display of the syntax for the QUERY tag (query part):

<!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

These tags can be used within an opening and a closing QUERY tag.

Name tag

Meaning

FILTERPARAM

Use of any parameter given in the definition part (see above) in the query - with definition of a fallback value.

FETCHMODE

Specification whether all the data of a data record is to be loaded immediately or on access.

ORDERCRITERIA

Sorting of the data records of the query result.

AND / OR

Linking of several queries by means of AND or OR.

EQ

The given comparable value matches the value in the database column (check for equality).

NEQ

The given comparable value does not match the value in the database column (check for inequality).

GT

The value in the database column is greater than the given comparable value (Greater than check).

GTE

The value in the database column is greater than or equal to the given comparable value (Greater than or equal check).

LT

The value in the database column is less than the given comparable value (Less than check).

LTE

The value in the database column is less than or equal to the given comparable value (Less than or equal check).

IS_NULL

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

NOTNULL

Value set for the database column (Not-null check).

LIKE

The given comparable value is similar to the value in the database column (similarity check).

  

FILTERPARAM tag

The FILTERPARAM tag enables "any parameters required" defined in the Definition part to be used in the query too.

As the value of the parameters defined in the definition part is frequently the value of a variable (e.g. from an input component, from the Site Store, or similar), the tag is the connection point or junction between the database and FirstSpirit.

The mandatory parameter parameter must be given as the value of the identifier of the parameter in the definition part.

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <CMS_VALUE_PARAM name="from" value="st_startDate" />
  <QUERY entityType="tableName">
    <FILTERPARAM parameter="from" ... />
    ...
  </QUERY>
</CMS_FUNCTION>

The datatype parameter is used to specify the data type of the value. The type should be the same as the data type of the column in the schema so that errors do not occur when the query is executed.

Common data types are:

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

In addition, there are special data types for date columns:

  • de.espirit.common.Now for the starting date/time of the deployment
  • de.espirit.common.Today for today.

<CMS_FUNCTION name="contentSelect" resultname="name">
  <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 is a default (preassigned) value. This parameter should always be defined. It is required if no parameter was defined with the identifier given in the parameter in the definition part.

<CMS_FUNCTION name="contentSelect" resultname="name">
  <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

As a data record in the query result potentially contains large or lots of data - especially for foreign keys - it can be useful not to load the data of the foreign key (the so-called data record attribute) until access or until further processing.

The loading behaviour of such attributes is changed using the tag: FETCHMODE.

The tag has two parameters:

  1. attribute
  2. lazy

For attribute the name of the column in the schema for which the loading behaviour is to be changed is given.

The lazy parameter is used to specify how the data of the column is to be loaded. lazy=1 means that the data can be loaded as and when required (= default setting). lazy=0 means that the data is loaded immediately.

ORDERCRITERIA tag

The ORDERCRITERIA tag is used to sort the result of a query. Each sort sort criteria must be specified in one tag.

The mandatory parameter attribute is used to specify the table column to be used as the basis of the sorting.

The parameter can contain the wildcard %lang% which is replaced with the abbreviation of the current language in a preview/generation (e.g. DE, EN etc.)

Example:

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

Further, it is also possible to use the descending parameter to change the sort direction. If the parameter is not given the result set of the query is sorted in ascending (descending="0") order. If descending="1" is defined the result set is sorted in descending order.

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

AND / OR tag

The AND-/OR tag can be used to link further query tags, e.g. EQ , LIKE , IS_NULL with each other.

Tags which are given in an AND tag are linked by an AND logic relation or logic operation and in an OR tag are linked by an OR logic relation or logic operation.

The nesting of AND and OR tags is only possible alternately.

Example of non allowable nesting:

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

Example of allowable nesting:

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

In the following example all data for which query 1 and (query 2 or query 3) apply are output as the result set of the query:

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

EQ tag (Equal)

The EQ tag is used to define queries which return all data records whose contents in the checked column match the comparable value ( equality check)

An EQ tag can be defined with the help of a variable (FILTERPARAM tag) or directly.

Direct definition:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <QUERY entityType="tableName">
    <EQ attribute="columnName" datatype="java.lang.Integer" value="value" />
    ...
  </QUERY>
</CMS_FUNCTION>

In the direct definition the parameters attribute, datatype and value are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The datatype is used to specify the data type for the comparison (cf. also the FILTERPARAM tag).

The parameter value is used to specify a comparable value.

Using a variable:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <CMS_VALUE_PARAM name="of" value="st_startDate" />
  <QUERY entityType="tableName">
    <FILTERPARAM parameter="of" datatype="java.util.Date" value="1143758280000" />
    <EQ attribute="columnName" parameter="of" />
    ...
  </QUERY>
</CMS_FUNCTION>

When using variables made available to the query with CMS_VALUE_PARAM and FILTERPARAM the parameters attribute and parameter are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The parameter parameter is used to specify which variable defined with the FILTERPARAM tag is to be used as the comparable value.

NEQ tag (Not Equal)

The NEQ tag is used to define queries which return all data records whose contents in the checked column do not match the comparable value (inequality check).

An NEQ tag can be defined with the help of a variable (FILTERPARAM tag) or directly.

Direct definition:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <QUERY entityType="tableName">
    <NEQ attribute="columnName" datatype="java.lang.Integer" value="value" />
    ...
  </QUERY>
</CMS_FUNCTION>

In the direct definition the parameters attribute, datatype and value are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The datatype is used to specify the data type for the comparison (cf. also the FILTERPARAM tag).

The parameter value is used to specify a comparable value.

Using a variable:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <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" />
    <NEQ attribute="columnName" parameter="from" />
    ...
  </QUERY>
</CMS_FUNCTION>

When using variables made available to the query with CMS_VALUE_PARAM and FILTERPARAM the parameters attribute and parameter are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The parameter parameter is used to specify which variable defined with the FILTERPARAM tag is to be used as the comparable value.

GT tag (greater than)

The GT tag is used to define queries which return all data records whose values in the checked column are greater than the given comparable value (Greater than check).

A GT tag can be defined with the help of a variable (FILTERPARAM tag) or directly.

Direct definition:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <QUERY entityType="tableName">
    <GT attribute="columnName" datatype="java.lang.Integer" value="value" />
    ...
  </QUERY>
</CMS_FUNCTION>

In the direct definition the parameters attribute, datatype and value are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The datatype is used to specify the data type for the comparison (cf. also the FILTERPARAM tag).

The parameter value is used to specify a comparable value.

Using a variable:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <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" />
    <GT attribute="columnName" parameter="from" />
    ...
  </QUERY>
</CMS_FUNCTION>

When using variables made available to the query with CMS_VALUE_PARAM and FILTERPARAM the parameters attribute and parameter is to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The parameter parameter is used to specify which variable defined with the FILTERPARAM tag is to be used as the comparable value.

GTE tag (greater than or equal)

The GTE tag is used to define queries which return all data records whose values in the checked column are greater than or equal to the given comparable value (Greater than /equal to check).

A GTE tag can be defined with the help of a variable (FILTERPARAM tag) or directly.

Direct definition:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <QUERY entityType="tableName">
    <GTE attribute="columnName" datatype="java.lang.Integer" value="value" />
    ...
  </QUERY>
</CMS_FUNCTION>

In the direct definition the parameters attribute, datatype and value are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The datatype is used to specify the data type for the comparison (cf. also the FILTERPARAM tag).

The parameter value is used to specify a comparable value.

Using a variable:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <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" />
    <GTE attribute="columnName" parameter="from" />
    ...
  </QUERY>
</CMS_FUNCTION>

When using variables made available to the query with CMS_VALUE_PARAM and FILTERPARAM the parameters attribute and parameter are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The parameter parameter is used to specify which variable defined with the FILTERPARAM tag is to be used as the comparable value.

LT tag (less than)

The LT tag is used to define queries which return all data records whose values in the checked column are less than the given comparable value (Less than check).

An LT tag can be defined with the help of a variable (FILTERPARAM tag) or directly.

Direct definition:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <QUERY entityType="tableName">
    <LT attribute="columnName" datatype="java.lang.Integer" value="value" />
    ...
  </QUERY>
</CMS_FUNCTION>

In the direct definition the parameters attribute, datatype and value are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The datatype is used to specify the data type for the comparison (cf. also the FILTERPARAM tag).

The parameter value is used to specify a comparable value.

Using a variable:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <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" />
    <LT attribute="columnName" parameter="from" />
    ...
  </QUERY>
</CMS_FUNCTION>

When using variables made available to the query with CMS_VALUE_PARAM and FILTERPARAM the parameters attribute and parameter are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The parameter parameter is used to specify which variable defined with the FILTERPARAM tag is to be used as the comparable value.

LTE tag (less than or equal)

The LTE tag is used to define queries which return all data records whose values in the checked column are less than or equal to the given comparable value (Less than /equal to check).

An LTE tag can be defined with the help of a variable (FILTERPARAM tag) or directly.

Direct definition:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <QUERY entityType="tableName">
    <LTE attribute="columnName" datatype="java.lang.Integer" value="value" />
    ...
  </QUERY>
</CMS_FUNCTION>

In the direct definition the parameters attribute, datatype and value are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The datatype is used to specify the data type for the comparison (cf. also the FILTERPARAM ).

The parameter value is used to specify a comparable value.

Using a variable:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <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" />
    <LTE attribute="columnName" parameter="from" />
    ...
  </QUERY>
</CMS_FUNCTION>

When using variables made available to the query with CMS_VALUE_PARAM and FILTERPARAM the parameters attribute and parameter are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The parameter parameter is used to specify which variable defined with the FILTERPARAM tag is to be used as the comparable value.

IS_NULL tag

The IS_NULL tag is used to define queries which return all data records which do not have any value in the checked column (Null check).

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <QUERY entityType="tableName">
    <IS_NULL attribute="columnName" />
    ...
  </QUERY>
</CMS_FUNCTION>

The definition must contain the attribute parameter.

For attribute the name of the column in the schema which is to be used for the comparison is given.

NOTNULL tag

The NOTNULL tag is used to define queries which return all data records which have a value in the checked column (Not-null check).

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <QUERY entityType="tableName">
    <NOTNULL attribute="columnName" />
    ...
  </QUERY>
</CMS_FUNCTION>

The definition must contain the attribute parameter.

For attribute the name of the column in the schema which is to be used for the comparison is given.

LIKE tag

The LIKE tag is used to define queries which return all data records whose values in the checked column are similar to the given comparable value (Similarity check).

The comparable value can be specified using the wildcard % which stands for any character required.

Specifying %ouse therefore returns all terms which end with aus , e.g. Mouse, House , etc.

A LIKE tag can be defined with the help of a variable ( FILTERPARAM tag) or directly.

Direct definition:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <CMS_PARAM name="schema" value="databaseSchemaName" />
  <QUERY entityType="tableName">
    <LIKE attribute="columnName" datatype="java.lang.Integer" value="value" />
    ...
  </QUERY>
</CMS_FUNCTION>

In the direct definition the parameters attribute, datatype and value are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The datatype is used to specify the data type for the comparison (cf. also the FILTERPARAM tag).

The parameter value is used to specify a comparable value.

Using a variable:

<CMS_FUNCTION name="contentSelect" resultname="name">
  <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" />
    <LIKE attribute="columnName" parameter="from" />
    ...
  </QUERY>
</CMS_FUNCTION>

When using variables made available to the query with CMS_VALUE_PARAM and FILTERPARAM the parameters attribute and parameter are to be given.

For attribute the name of the column in the schema which is to be used for the comparison is given.

The parameter parameter is used to specify which variable defined with the FILTERPARAM tag is to be used as the comparable value.

Print version | Page start

© 2005 - 2014 e-Spirit AG | All rights reserved. | Last change: 2013-01-15