Projections & Aggregates

Search…
Hibernate also supports the ability to work with sql projections and sql 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 of data.
1
var statusReport = c
2
    .withProjections( count: "isActive:authors", groupProperty: "isActive" )
3
    .asStruct()
4
    .list();
Copied!
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 by just using the asStruct() modifier.
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:
1
/**
2
 * Setup projections for this criteria query, you can pass one or as many projection arguments as you like.
3
 * 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
4
 * or you can pass an alias after the property name separated by a : Ex: projections(avg="balance:avgBalance")
5
 * The alias on the projected value can be referred to in restrictions or orderings.
6
 * Please also note that the resulting array locations are done in alphabetical order of the arguments.
7
 *
8
 * @avg The name of the property to avg or a list or array of property names
9
 * @count The name of the property to count or a list or array of property names
10
 * @countDistinct The name of the property to count distinct or a list or array of property names
11
 * @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
12
 * @groupProperty The name of the property to group by or a list or array of property names
13
 * @id The projected identifier value
14
 * @max The name of the property to max or a list or array of property names
15
 * @min The name of the property to min or a list or array of property names
16
 * @property The name of the property to do a projected value on or a list or array of property names
17
 * @rowCount Do a row count on the criteria
18
 * @sum The name of the property to sum or a list or array of property names
19
 * @sqlProjection Do a projection based on arbitrary SQL string
20
 * @sqlGroupProjection Do a projection based on arbitrary SQL string, with grouping
21
 * @detachedSQLProjection Do a projection based on a DetachedCriteria builder config
22
 */
23
any function withProjections(
24
	string avg,
25
	string count,
26
	string countDistinct,
27
	any distinct,
28
	string groupProperty,
29
	boolean id,
30
	string max,
31
	string min,
32
	string property,
33
	boolean rowCount,
34
	string sum,
35
	any sqlProjection,
36
	any sqlGroupProjection,
37
	any detachedSQLProjection
38
){
Copied!
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.
1
Ex: 
2
avg="balance",
3
avg="balance:myBalance", // balance as myBalance
4
avg="balance, total", 
5
avg=["balance","total"]
Copied!
If the :alias is not used, then the alias becomes the property name.
Examples
1
// Using native approach for one projection only
2
var results = c.like("firstName","Lui%")
3
     .and( 
4
          c.restrictions.between( "balance", 200, 300),
5
          c.restrictions.eq("department", "development")
6
     )
7
     .setProjection( c.projections.rowCount() )
8
     .get();
9
​
10
// Using the withProjections() method, which enables you to do more than 1 projection
11
var results = c.like("firstName","Lui%")
12
     .and( 
13
          c.restrictions.between( "balance", 200, 300),
14
          c.restrictions.eq("department", "development")
15
     )
16
     .withProjections(rowCount=1)
17
     .get();
18
​
19
var results = c.like("firstName","Lui%")
20
     .and( 
21
          c.restrictions.between( "balance", 200, 5000),
22
          c.restrictions.eq("department", "development")
23
     )
24
     .withProjections(avg="balance,total",max="total:maxTotal")
25
     .gt("maxTotal",500)
26
     .list();
27
     
28
var results = c
29
     .withProjections( property="id,name,username" )
30
     .isTrue( "isActive" )
31
     .asStruct()
32
     .list();
Copied!
Projections
Here is a detail overview of each projection type.
avg
The name of the property to average or a list or array of property names to average on
1
// projection
2
withProjections( avg = "salary" )
3
// Produces
4
select avg( salary ) from User
5
​
6
// projection with alias
7
withProjections( avg = "salary:totalSalary" )
8
// Produces
9
select avg( salary ) as totalSalary from User
10
​
11
// Multiple projections
12
withProjections( avg = "salary,balance" )
13
// Produces
14
select avg( salary ), avg( balance ) from User
Copied!
count
The name of the property to count on or a list or array of property names to count on
1
// projection
2
withProjections( count = "id" )
3
// Produces
4
select count( id ) from User
5
​
6
// projection with alias
7
isTrue( "isActive" )
8
    .withProjections( count = "id:TotalUsers" )
9
// Produces
10
select count( id ) as totalUsers from User
11
where isActive = true
Copied!
countDistinct
Get a distinct count on a property or list of properties
1
// projection
2
withProjections( distinctCount = "city:cities" )
3
// Produces
4
SELECT COUNT( DISTINCT city ) as cities
5
FROM User;
Copied!
distinct
Distinct is used to return only distinct (different) values from a property or list of properties
1
// projection
2
withProjections( distinct = "city" )
3
// Produces
4
SELECT DISTINCT city
5
FROM User;
Copied!
groupProperty
Which properties to group in the SQL statement.
1
// project users by country
2
withProjections( property : "country", count : "id" , groupProperty : "country" )
3
​
4
// Produces
5
SELECT COUNT(id), Country
6
FROM User
7
GROUP BY Country;
Copied!
id
Return an array of IDs of the current projected entity. Let's say we want all the user id's that have never logged in to the system. So we can use that to send them mails.
1
isNull( "lastLogin" )
2
    .withProjections( id : true )
3
    .list();
4
​
5
// Produces
6
SELECT id
7
FROM User
8
WHERE lastLogin is null
Copied!
max
Return a max() sql projection on a a property or list of properties
1
// projection
2
withProjections( max = "salary" )
3
// Produces
4
select max( salary ) from User
Copied!
min
Return a min() sql projection on a a property or list of properties
1
// projection
2
withProjections( min = "salary" )
3
// Produces
4
select min( salary ) from User
Copied!
property
Return or a list of properties in the projection. Basically pick and choose in the select statement. The cool thing is that this can be a property or a full relationship, which can give you an array of properties and objects.
You can navigate relationships using the dot notation.
1
// projection
2
withProjections( property = "id,fname,lname,email" )
3
// Produces
4
select id, fname, lname, email from User
5
​
6
// Get all site core settings in the database
7
newCriteria()
8
.isFalse( "isDeleted" )
9
.isTrue( "isCore" )
10
.joinTo( "site", "site" )
11
.withProjections( property: "name,site.slug:siteSlug" )
12
.asStruct()
13
.list( sortOrder = "site.slug,name" );
14
​
15
// Produces
16
SELECT name, site.slug as siteSlug
17
FROM setting, site
18
WHERE setting.isDelete = false AND
19
setting.isCore = true AND
20
site.siteId = setting.FK_siteID
21
order by site.slug, setting.name
Copied!
rowcount
Run a count( * ) on the projection alongside other projections.
1
r = criteria
2
.withProjections(
3
	min      = "lastLogin",
4
	rowCount = true,
5
	max      = "lastLogin"
6
)
7
.peek( function( c ){
8
	debug( c.getSql( true, true ) );
9
} )
10
.list();
11
​
12
// Produces
13
select
14
    max(this.lastLogin) as y0_,
15
    min(this.lastLogin) as y1_,
16
    count(*) as y2_ // this is due to the rowcount
17
from
18
    users this_
Copied!
sum
Run a sql sum() on a one or many properties
1
// projection
2
withProjections( sum = "salary:payroll" )
3
// Produces
4
select sum( salary ) as payroll from User
Copied!
sqlProjection/sqlGroupProjection
Do a projection based on arbitrary SQL and SQL grouping strings. The value can be a single struct or an array of structs with the following pattern:
sql - The raw sql to execute
alias - The aliases to apply
property - The property projected on
group - What to group on if needed
1
.withProjections(
2
	groupProperty = "catid",
3
	sqlProjection = [
4
		{
5
			sql      : "count( category_id )",
6
			alias    : "count",
7
			property : "catid"
8
		}
9
	],
10
	sqlGroupProjection = [
11
		{
12
			sql      : "year( modifydate )",
13
			group    : "year( modifydate )",
14
			alias    : "modifiedDate",
15
			property : "id"
16
		},
17
		{
18
			sql      : "dateDiff('2021-12-31 23:59:59','2021-12-30')",
19
			group    : "dateDiff('2021-12-31 23:59:59','2021-12-30')",
20
			alias    : "someDateDiff",
21
			property : "id"
22
		}
23
	]
24
)
25
.asStruct()
26
.peek( function( c ){
27
	debug( c.getSql( true, true ) );
28
} )
29
.list();
Copied!
This will produce the following SQL
1
select
2
    this.category_id as y0_,
3
    count(category_id) as count,
4
    year(modifydate) as modifiedDate,
5
    dateDiff('2021-12-31 23:59:59', '2021-12-30') as someDateDiff
6
from
7
    categories this_
8
group by
9
    this.category_id,
10
    year(modifydate),
11
    dateDiff('2021-12-31 23:59:59', '2021-12-30')
Copied!