Projections & Aggregates

Hibernate also supports the ability to work with projections and aggregates. Instead of treating the results as an array of objects or a stream of objects you can return the result as a row with columns or a projection of the data. This is similar to how you use to select data in a cfquery call.

This is great for API driven applications as you DON"T have to retrieve the entire object graphs, you can decide which columns to bring back and return an array of structs with lightening speed.

There are several projection types you can use which are great for doing counts, distinct counts, max values, sums, averages and much more.

withProjections()

The method in the criteria builder that will allow you to add projections is called withProjections(). You will then use it's arguments to tell Hibernate what projection or aggregates to compile into the query. Here is the method signature:

/**
 * Setup projections for this criteria query, you can pass one or as many projection arguments as you like.
 * The majority of the arguments take in the property name to do the projection on, which will also use that as the alias for the column
 * or you can pass an alias after the property name separated by a : Ex: projections(avg="balance:avgBalance")
 * The alias on the projected value can be referred to in restrictions or orderings.
 * Please also note that the resulting array locations are done in alphabetical order of the arguments.
 *
 * @avg The name of the property to avg or a list or array of property names
 * @count The name of the property to count or a list or array of property names
 * @countDistinct The name of the property to count distinct or a list or array of property names
 * @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
 * @groupProperty The name of the property to group by or a list or array of property names
 * @id The projected identifier value
 * @max The name of the property to max or a list or array of property names
 * @min The name of the property to min or a list or array of property names
 * @property The name of the property to do a projected value on or a list or array of property names
 * @rowCount Do a row count on the criteria
 * @sum The name of the property to sum or a list or array of property names
 * @sqlProjection Do a projection based on arbitrary SQL string
 * @sqlGroupProjection Do a projection based on arbitrary SQL string, with grouping
 * @detachedSQLProjection Do a projection based on a DetachedCriteria builder config
 */
any function withProjections(
	string avg,
	string count,
	string countDistinct,
	any distinct,
	string groupProperty,
	boolean id,
	string max,
	string min,
	string property,
	boolean rowCount,
	string sum,
	any sqlProjection,
	any sqlGroupProjection,
	any detachedSQLProjection
){

Arguments

Below are the available projections you can use from this method

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")
id	Return the projected identifier value	withProjections(id=true)
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")
rowCount	Do a row count on the criteria	withProjections(rowcount=true)
sum	The name of the property to sum or a list or array of property names	withProjections(sum="balance")
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

The value of the arguments 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.

Aliases

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

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

Examples

// 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();
     
var results = c
     .withProjections( property="id,name,username" )
     .isTrue( "isActive" )
     .asStruct()
     .list();