## 5.4.2 Criteria

Criteria is a type safe, advanced way to query that uses a Groovy builder to construct potentially complex queries. It is a much better alternative to using StringBuffer.

Criteria can be used either via the createCriteria or withCriteria methods. The builder uses Hibernate's Criteria API, the nodes on this builder map the static methods found in the Restrictions class of the Hibernate Criteria API. Example Usage:

    def c = Account.createCriteria()
    def results = c {
        between("balance", 500, 1000)
        eq("branch", "London")
        or {
            like("holderFirstName", "Fred%")
            like("holderFirstName", "Barney%")
        }
        maxResults(10)
        order("holderLastName", "desc")
    }

This criteria will select up to 10 `Account` objects matching the following criteria:

- `balance` is between 500 and 1000
- `branch` is 'London'
- `holderFirstName` starts with 'Fred' or 'Barney'

The results will be sorted in descending ordery by `holderLastName`.

#### Conjunctions and Disjunctions

As demonstrated in the previous example you can group criteria in a logical OR using a `or { }` block:

    or {
    	between("balance", 500, 1000)
    	eq("branch", "London")
    }

This also works with logical AND:

    and {
    	between("balance", 500, 1000)
    	eq("branch", "London")
    }

And you can also negate using logical NOT:

    not {
    	between("balance", 500, 1000)
    	eq("branch", "London")
    }

All top level conditions are implied to be AND'd together.

#### Querying Associations

Associations can be queried by having a node that matches the property name. For example say the `Account` class had many `Transaction` objects:

    class Account {
        …
        def hasMany = [transactions:Transaction]
        Set transactions
        …
    }

We can query this association by using the property name `transaction` as a builder node:

    def c = Account.createCriteria()
    def now = new Date()
    def results = c.list {
           transactions {
                between('date',now-10, now)
           }
    }

The above code will find all the `Account` instances that have performed `transactions` within the last 10 days. You can also nest such association queries within logical blocks:

    def c = Account.createCriteria()
    def now = new Date()
    def results = c.list {
         or {
            between('created',now-10,now)
            transactions {
                 between('date',now-10, now)
            }
         }
    }

Here we find all accounts that have either performed transactions in the last 10 days OR have been recently created in the last 10 days.

#### Querying with Projections

Projections may be used to customise the results. To use projections you need to define a "projections" node within the criteria builder tree. There are equivalent methods within the projections node to the methods found in the Hibernate Projections class:

    def c = Account.createCriteria()def numberOfBranches = c.get {
    	projections {
    		countDistinct('branch')
    	}
    }

#### Using SQL Restrictions

You can access Hibernate's SQL Restrictions capabilities.

    def c = Person.createCriteria()def peopleWithShortFirstNames = c.list {
        sqlRestriction "char_length( first_name ) <= 4"
    }

> Note that the parameter there is SQL. The `first_name` attribute referenced in the example relates to the persistence model, not the object model. The `Person` class may have a property named `firstName` which is mapped to a column in the database named `first_name`.

> Also note that the SQL used here is not necessarily portable across databases.

#### Using Scrollable Results

You can use Hibernate's ScrollableResults feature by calling the scroll method:

    def results = crit.scroll {
          maxResults(10)
    }
    def f = results.first()
    def l = results.last()
    def n = results.next()
    def p = results.previous()def future = results.scroll(10)
    def accountNumber = results.getLong('number')

To quote the documentation of Hibernate ScrollableResults:

> A result iterator that allows moving around within the results by arbitrary increments. The Query / ScrollableResults pattern is very similar to the JDBC PreparedStatement/ ResultSet pattern and the semantics of methods of this interface are similar to the similarly named methods on ResultSet.

Contrary to JDBC, columns of results are numbered from zero.

#### Setting properties in the Criteria instance

If a node within the builder tree doesn't match a particular criterion it will attempt to set a property on the Criteria object itself. Thus allowing full access to all the properties in this class. The below example calls `setMaxResults` and `setFirstResult` on the Criteria instance:

    import org.hibernate.FetchMode as FM
    	…
    	def results = c.list {
    		maxResults(10)
    		firstResult(50)
    		fetchMode("aRelationship", FM.EAGER)
    	}

#### Querying with Eager Fetching

In the section on Eager and Lazy Fetching we discussed how to declaratively specify fetching to avoid the N+1 SELECT problem. However, this can also be achieved using a criteria query:

    def criteria = Task.createCriteria()
    def tasks = criteria.list{
         eq "assignee.id", task.assignee.id
         join 'assignee'
         join 'project'
         order 'priority', 'asc'
    }

Notice the usage of the `join` method. This method indicates the criteria API that a `JOIN` query should be used to obtain the results.

#### Method Reference

If you invoke the builder with no method name such as:

    c { … }

The build defaults to listing all the results and hence the above is equivalent to:

    c.list { … }

Method | Description
--- | ---
list | This is the default method. It returns all matching rows.
get | Returns a unique result set, i.e. just one row. The criteria has to be formed that way, that it only queries one row. This method is not to be confused with a limit to just the first row.
scroll | Returns a scrollable result set.
listDistinct | If subqueries or associations are used, one may end up with the same row multiple times in the result set, this allows listing only distinct entities and is equivalent to `DISTINCT_ROOT_ENTITY` of the CriteriaSpecification class.
count | Returns the number of matching rows.