ColdBox

Custom SQL Projections…Part Second

Sep 5th

Posted by existdissolve in ColdBox

No comments

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 >

Custom SQL Projections in CriteriaBuilder

Aug 25th

Posted by existdissolve in ColdBox

No comments

In my last post, I showed how you can pretty easily integrate ColdBox’s awesome CriteriaBuilder with subqueries. As I was using this approach, I came across another (unrelated) challenge that I wanted a solution for.

Simple Grouping/Summing

In one of my queries, I wanted to do some simple grouping/summing. With CriteriaBuilder, you can do this very easily by adding a projection or two.

For example, let’s imagine that we have the same entities as before:

  • Car
  • Driver
  • Insurance

This time, we want to get a sum of the total number of cars, grouped by “make”. Easy:

public struct function getCarsByMake() {
   var c  = newCriteria();
   c.withProjections( count="CarID", groupProperty="Make" );
   var results = {
       data = c.list()
   };
   results.count = arrayLen( results.data );
   return results;
}

And the SQL:

select this_.Make as y0_, count(this_.CarID) as y1_
from   Car this_
group by this_.Make

Pretty simple. By the application of the “count” and “groupProperty” projections, we get back a nice array of the grouped data. Note, of course, that because of the application of projections, our result set is no longer in the domain of discreet entities. Rather, since we asked for aggregate data, that’s what we get! Of course, this means that results.count has to be calculated differently. Instead of running CriteriaBuilder’s count() method More >

, , , ,

Subqueries with ColdBox CriteriaBuilder

Aug 20th

Posted by existdissolve in ColdBox

1 comment

Here is the code on GitHub, if you want it.

Over the last several months, I’ve been working heavily in a brand-new environment (to me): one that is built upon ColdBox and leverages all of the awesome goodies that ColdBox provides in the way of rapidly and easily developing ORM-driven applications. If you spend more than 3 minutes in a ColdBox ORM app, you’ll quickly come to rely upon–and LOVE–ColdBox’s CriteriaBuilder, which lets you easily build intensely complicated HIbernate criteria queries…without the nonsense of string concatenation.

The other day, however, I ran into a bit of a wall. Before I explain the issue, let me give a quick summary of the context.

In my app, I have a good number of concrete services that extend the uber-handy Virtual Entity Services. Since my app is driven largely by an AJAX frontend, there are lots of cases in which I not only want to return a JSON-representation of a result set, but also important meta-data about the result set (e.g., total record count for paging, etc.). Obviously, since I want that bundled into one single response, I have lots of methods that do something like so in one of my concrete services:

function getResults( event, rc, prc More >
, ,
«12