createCriteria

Purpose

Creates an returns an instance of Grails' api:grails.orm.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()