Result Transformers

Our criteria builder also supports the notion of projections (http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-projection). A projection is used to change the nature of the results, much how a result transformer does. However, there are several projection types you can use which are great for doing counts, distinct counts, max values, sums, averages and much more. This is great when you do paging as obviously you do not want to execute two queries: one for the pagination and another for the total reuslts count. Below are the available projections you can use:

Transform	Description	Example
avg	The name of the property to avg or a list or array of property names	withProjections(avg="salary")
count	The name of the property to count or a list or array of property names	withProjections(count="comments")
countDistinct	The name of the property to count distinct or a list or array of property names	withProjections(countDistinct="email")
distinct	The name of the property to do a distinct on, this can be a single property name a list or an array of property names	withProjections(distinct="email")
groupProperty	The name of the property to group by or a list or array of property names	withProjections(groupproperty="lastName")
max	The name of the property to max or a list or array of property names	withProjections(max="lastLogin")
min	The name of the property to min or a list or array of property names	withProjections(min="cid")
property	The name of the property to do a projected value on or a list or array of property names	withProjections(property="firstname")
sum	The name of the property to sum or a list or array of property names	withProjections(sum="balance")
rowCount	Do a row count on the criteria	withProjections(rowcount=1)
id	Return the projected identifier value	withProjections(id=1)
sqlProjection	Return projected value for sql fragment. Can accept a single config {sql,alias,property}, or an array of multiple configs.	withProjections(sqlProjection={sql="SELECT count( ) from blog where Year < 2006 and Author={alias}.Author", alias="BlogPosts", property="Author" })*
sqlGroupProjection	Return projected value for sql fragment with grouping. Can accept a single config( sql,alias,property,group}, or an array of multiple configs.	withProjections(sqlGroupProjection={sql="SELECT count( ) from blog where Year < 2006 and Author={alias}.Author", alias="BlogPosts", property="Author", group="Author" })*
detachedSQLProjection	Creates a sqlProjection() based on Detached Criteria Builder	See Detached Criteria Builder

You will use them by passing them to the withProjections() method as arguments that match the projection type. The value of the argument is one, a list or an array of property names to run the projection on, with the exception of id and rowcount which take a boolean true. Also, you can pass in a string separated with a : to denote an alias on the property when doing the SQL. The alias can then be used with any restriction the criteria builder can use.

Ex: avg="balance", avg="balance:myBalance", avg="balance, total", avg=["balance","total"]

INFO If the :alias is not used, then the alias becomes the property name.

// Using native approach for one projection only
var results = c.like("firstName","Lui%")
     .and( 
          c.restrictions.between( "balance", 200, 300),
          c.restrictions.eq("department", "development")
     )
     .setProjection( c.projections.rowCount() )
     .get();

// Using the withProjections() method, which enables you to do more than 1 projection
var results = c.like("firstName","Lui%")
     .and( 
          c.restrictions.between( "balance", 200, 300),
          c.restrictions.eq("department", "development")
     )
     .withProjections(rowCount=1)
     .get();

var results = c.like("firstName","Lui%")
     .and( 
          c.restrictions.between( "balance", 200, 5000),
          c.restrictions.eq("department", "development")
     )
     .withProjections(avg="balance,total",max="total:maxTotal")
     .gt("maxTotal",500)
     .list();