criteria

Values

Description

criteria is a column option that allows you to restrict which rows are included in your table. It defines a list of Hibernate criteria that are applied to the query. This option only applies when using serverSide or serverDataLoad.

The data provided via this option is used to create criteria by calling methods on the Hibernate Restrictions class. Each criteria you specify for a column contains a list of values. The first value is the name of the method. With the exception of the sqlRestriction criterion method, the first parameter of the method will be provided by the column name. Thus, not all types of Restrictions methods are supported at present. Currently, the supported restrictions are:

• sqlRestriction
• Restrictions methods that have propertyName as the first argument

Any additonal list items are used as the following arguments to that method. See the examples below for clarification.

You may specify multiple criteria using an outer list of lists, or you may specify one criterion using a single list.

Examples

Single Criterion

This example shows how a criteria option is used to create an ilike criterion that restricts the lastName column to include only names that end with r.

<dt:datatable name="Criteria1" domainClass="datatablestest.Author" serverSide="true">
    <dt:column name="firstName"/>
    <dt:column name="lastName" criteria="${['ilike', '%r']}"/>
    <dt:column name="email" heading="E-Mail"/>
    <dt:column name="dateOfBirth" dataType="date" order="asc"/>
</dt:datatable>

Two Criteria

This second example shows how a criteria option is used to create two criteria for the lastName column. The first criterion restricts the names to those that fall alphabetically before D, and the second is the same criterion as shown in the first example.

<dt:datatable name="Criteria2" domainClass="datatablestest.Author" serverSide="true">
    <dt:column name="firstName"/>
    <dt:column name="lastName" criteria="${[['lt', 'D'],['ilike', '%r']]}"/>
    <dt:column name="email" heading="E-Mail"/>
    <dt:column name="dateOfBirth" dataType="date" order="asc"/>
</dt:datatable>

Excluded Column Criteria

This third example shows how a criteria option is used restrict the items shown in the table while not actually including the lastName column in the table. The include option indicates that the lastName column should not be included in the table.

<dt:datatable name="Criteria3" domainClass="datatablestest.Author" serverSide="true">
    <dt:column name="firstName"/>
    <dt:column name="lastName" criteria="${['ilike', '%r']}" include="none"/>
    <dt:column name="email" heading="E-Mail"/>
    <dt:column name="dateOfBirth" dataType="date" order="asc"/>
</dt:datatable>

Association Column Criteria

This fourth example shows how a criteria option can be used to restrict items based on an association property.

<dt:datatable name="Criteria4" domainClass="datatablestest.Book" serverSide="true">
    <dt:column name="title"/>
    <dt:column name="author.name"/>
    <dt:column name="author.publisher.name" criteria="${['ilike', 'c%']}"/>
</dt:datatable>

Association Object Column Criteria

This fifth example shows how a criteria option can be used to restrict items based on an association object.

<dt:datatable name="Criteria5" domainClass="datatablestest.Book" serverSide="true">
    <dt:column name="title"/>
    <dt:column name="author.name"/>
    <dt:column name="author.publisher.name"/>
    <dt:column name="author.publisher" criteria="${['eq', myPublisher]}" include="none"/>
</dt:datatable>

sqlRestriction Criterion

This sixth example shows how a criteria option can be used to restrict items using a sqlRestriction Criterion, which allows you to use SQL to build your criterion. This has the advantage of allowing a more flexible approach, however you must use the database column names instead of the attribute names. When using a sqlRestriction, the column name is not used to build the criterion.

<dt:datatable name="Criteria6" domainClass="datatablestest.Author" serverSide="true">
    <dt:column name="firstName"/>
    <dt:column name="lastName" criteria="${['sqlRestriction', "lower(last_name) in ('smith', 'jones')"]}"/>
</dt:datatable>

See Also

sqlRestrictionFunction

Back to Options.