# createCriteria

## Purpose

Creates an 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")
    }

## 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. 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"

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