# createCriteria

## Purpose

Creates and returns an instance of Grails' HibernateCriteriaBuilder that can be used to construct criteria queries.

## Examples

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

In order to use pagination you would have to make another query to retrieve the total number of matching results. A better way is to pass the necessary pagination values as arguments to the criteria method as shown below:

    def c = Account.createCriteria()
    def results = c.list (max: 10, offset: 10) {
        like("holderFirstName", "Fred%")
        and {
            between("balance", 500, 1000)
            eq("branch", "London")
        }
        order("holderLastName", "desc")
    }

This will return a PagedResultList which has a getTotalCount() method to return the total number of matching records for pagination.

    println "Rendering ${results.size()} Accounts of ${results.totalCount}"

## Description

Criteria queries are 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. Refer to the user guide section on Criteria for usage instructions.

Method reference:

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. In Hibernate one would do a "CriteriaSpecification.DISTINCT_ROOT_ENTITY". In grails one can do it even simpler by just using this method.

If you invoke the builder with no method name like so

    c { … }

the `list()` method will be invoked automatically. In other words, it's the equivalent of

    c.list { … }

Below is a node reference for each criterion method:

Node | Description | Example
--- | --- | ---
between | Where the property value is between to distinct values |
    between("balance", 500, 1000)
eq | Where a property equals a particular value. |
    eq("branch", "London")
eq (case-insensitive) | A version of eq that supports an optional 3rd Map parameter to specify that the query be case-insensitive. |
    eq("branch", "london", [ignoreCase: true])
eqProperty | Where one property must equal another |
    eqProperty("lastTransaction","firstTransaction")
gt | Where a property is greater than a particular value |
    gt("balance",1000)
gtProperty | Where a one property must be greater than another |
    gtProperty("balance","overdraft")
ge | Where a property is greater than or equal to a particular value |
    ge("balance",1000)
geProperty | Where a one property must be greater than or equal to another |
    geProperty("balance","overdraft")
idEq | Where an objects id equals the specified value |
    idEq(1)
ilike | A case-insensitive 'like' expression |
    ilike("holderFirstName","Steph%")
in | Where a one property is contained within the specified list of values note: 'in' is a groovy reserve word, we must escape it by quotes. |
    'in'("holderAge",[18..65])
isEmpty | Where a collection property is empty |
    isEmpty("transactions")
isNotEmpty | Where a collection property is not empty |
    isNotEmpty("transactions")
isNull | Where a property is null |
    isNull("holderGender")
isNotNull | Where a property is not null |
    isNotNull("holderGender")
lt | Where a property is less than a particular value |
    lt("balance",1000)
ltProperty | Where a one property must be less than another |
    ltProperty("balance","overdraft")
le | Where a property is less than or equal to a particular value |
    le("balance",1000)
leProperty | Where a one property must be less than or equal to another |
    leProperty("balance","overdraft")
like | Equivalent to SQL like expression |
    like("holderFirstName","Steph%")
ne | Where a property does not equals a particular value |
    ne("branch", "London")
neProperty | Where one property does not equal another |
    neProperty("lastTransaction","firstTransaction")
order | Order the results by a particular property |
    order("holderLastName", "desc")
rlike | Similar to like, but uses a regex. Only supported on Oracle and MySQL. |
    rlike("holderFirstName",/Steph.+/)
sizeEq | Where a collection property's size equals a particular value |
    sizeEq("transactions", 10)
sizeGt | Where a collection property's size is greater than a particular value |
    sizeGt("transactions", 10)
sizeGe | Where a collection property's size is greater than or equal to a particular value |
    sizeGe("transactions", 10)
sizeLt | Where a collection property's size is less than a particular value |
    sizeLt("transactions", 10)
sizeLe | Where a collection property's size is less than or equal to a particular value |
    sizeLe("transactions", 10)
sizeNe | Where a collection property's size is not equal to a particular value |
    sizeNe("transactions", 10)
sqlRestriction | Use arbitrary SQL to modify the resultset |
    sqlRestriction "char_length( first_name ) = 4"

With dynamic finders, you have access to options such as 'max', 'sort', etc. These are available to criteria queries as well, but they are named differently:

Name | Description | Example
--- | --- | ---
order(string, string) | Specifies both the sort column (the first argument) and the sort order (either 'asc' or 'desc'). |
    order "age", "desc"
firstResult(int) | Specifies the offset for the results. A value of 0 will return all records up to the maximum specified. |
    firstResult 20
maxResults(int) | Specifies the maximum number of records to return. |
    maxResults 10
cache(boolean) | Tells Hibernate whether to cache the query or not (if the query cache is enabled). |
    cache true

Criteria also support the notion of projections. A projection is used to change the nature of the results. For example the following query using a projection to count the number of distinct `branch` names there are for each `Account`:

    def c = Account.createCriteria()
    def branchCount = c.get {
    	projections {
    		countDistinct "branch"
    	}
    }

The following table summarizes the different projections and what they do:

Name | Description | Example
--- | --- | ---
property | Returns the given property in the returned results |
    property("firstName")
distinct | Returns results using a single or collection of distinct property names |
    distinct("lastName") or distinct(['firstName', 'lastName'])
avg | Returns the average value of the given property |
    avg("age")
count | Returns the count of the given property name |
    count("branch")
countDistinct | Returns the distinct count of the given property name |
    countDistinct("branch")
groupProperty | Groups the results by the given property |
    groupProperty("lastName")
max | Returns the maximum value of the given property |
    max("age")
min | Returns the minimum value of the given property |
    min("age")
sum | Returns the sum of the given property |
    sum("balance")
rowCount | Returns count of the number of rows returned |
    rowCount()