Custom SQL Projections…Part Second

Previously, I outlined a way in which you can pretty easily extend ColdBox’s excellent CriteriaBuilder to support Hibernate Criteria SQL projections. While this certainly worked, you may remember I complained–whined, really–about how hard-codey the solution was. That is, in order to really use the SQL projections, you more or less have to build out SQL strings manually each and every time you use it. String concatenation, ugh!

Not content to leave it alone, I continued to explore ways to make this a bit better; I think I’ve struck on a significantly better way.

On the Road to a Solution

What I’ve created is what I call a “Detached SQL Projection”. In essence, it leverages the DetachedCriteria which I wrote about previously. Once the DetachedCriteria is sufficiently built up, the “projection” more or less grabs the SQL that *would be* generated for the DetachedCriteria. However, instead of adding it as a subquery to the main criteria object, this faux-projection uses the pre-generated SQL and creates a real SQL projection.

Here’s some code (from my extension of the withProjections() method):

// detachedSQLProjection
if( structKeyExists( arguments, "detachedSQLProjection" ) ) {
   var propertyTypes = [];
   var sqlargs = arguments.detachedSQLProjection;
   var metaData = orm.getSessionFactory(orm.getEntityDatasource(this.getentityName())).getClassMetaData(this.getentityName());
   // get session
   var More >