# 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()