Monday, April 22, 2013

Grails create criteria on model

def c = Account.createCriteria()
List results = c.list {
    like("name", "%Name Like%")
    and {
        between("amount", 500, 1000)
        eq("address", "London")
    }
    setFirstResult(0)
    setMaxResults(10)
    order("name", "desc")
    order("id", "asc")
    
    projections {
        property("id")
        groupProperty("invoice.id")
    }
}


Below is a node reference for each criterion method:

Node

Description

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

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

Name Description
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)
Indicates if the query should be cached (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 uses a projection to count the number of distinct branch names that exist for each Account:


Name Description
property
Returns the given property in the returned results
property("firstName")
distinct
Returns results using a single or collection of distinct property names
distinct("fn") or distinct(['fn', 'ln'])
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 count of the given property name for distinct rows
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()

XXXXXXXXXXXXXXXX

No comments:

Post a Comment