Last week I tried to create GORM criteria that shows from which countries the addresses stored in a table were. The SQL for this would look something like this:

select c.code, count(*) from Address a left outer join Country c on a.country=c.id group by c.code

Now I wanted to sort the results with the country the most addresses were from on top. This SQL would look like:

select c.code, count(*) as co from Address a left outer join Country c on a.country=c.id group by c.code order by co desc

I had to add the “as co” alias to the count column to use it in the order command at the end of the SQL. How could this be done in a GORM criteria? The basic criteria with the result of the first SQL looked like:

Address.createCriteria().list {
	country {
		projections {
			groupProperty('code')
			rowCount()
		}
	}
}

The question was how to add the “as co” alias to the row count column? In the Hibernate the Projections allow that by using the as method but how to do that in the GORM criteria DSL? I turned out the following way:

Address.createCriteria().list {
	country {
		projections {
			groupProperty('code')
			rowCount('co')
		}
	}
	order('co', 'desc')
}

You can give the alias to the projection as an additional parameter (or as the only parameter in case of rowCount).