Subqueries with ColdBox CriteriaBuilder

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 ) {
    // new criteria object
    var c =  newCriteria();
    ... build out criteria via arguments ...
    var results = {
        count = c.count(),
        data = c.list()
    };
    return results;
}

So not much to it. I simply build out my criteria object, and then return a structure (which will eventually be converted to JSON) which contains both the count and the array of data. LIterally until about 2 days ago, this worked perfectly for me. But then, I hit a wall.

What was the wall? I came across a scenario where I wanted…no, NEEDED to run a subquery. For example, consider that I have 3 entities:

1. Car
2. Driver
3. Insured

Nothing fancy here: Car and Driver describe, shockingly, cars and drivers, while Insured is a link table that tells which drivers are insured for which cars.

Attempt #1

Now, back to the subquery. Let’s assume that I want to get a list of cars, but only those which have insured drivers that are under the age of 26. My first attempt looked something like this:

public struct function getCarsInsuredForYoungDrivers_Join( required Numeric Age ) {
    var c = newCriteria();
    c.createAlias( "InsuredDrivers", "id" ).isLt( "id.Age", javaCast( "int", arguments.Age ) );
    var results = {
        count = c.count(),
        data = c.list( asquery=true )
    };
    return results;
}

Because I know my data, I expect that I should get back 2 records; one for a Ford Focus, and one for a Saturn Aura. However, I actually get this:

And here’s the log from Hibernate:

Count():

select count(*) as y0_ 
from  Car this_ 
inner join Insured insureddri3_ on this_.CarID=insureddri3_.CarID 
inner join Driver id1_ on insureddri3_.DriverID=id1_.DriverID 
where id1_.Age<;26

List():

select this_.CarID as CarID47_1_, this_.Make as Make47_1_, 
       this_.Model as Model47_1_, this_.`Year` as Year4_47_1_,
       insureddri3_.CarID as CarID47_, id1_.DriverID as DriverID,
       id1_.DriverID as DriverID45_0_, id1_.Name as Name45_0_, 
       id1_.Age as Age45_0_ 
 from  Car this_ 
 inner join Insured insureddri3_ on this_.CarID=insureddri3_.CarID 
 inner join Driver id1_  on insureddri3_.DriverID=id1_.DriverID 
 where id1_.Age

This *technically* gives me back the correct results. However, because a JOIN is ultimately created because of the createAlias(), I get back the fully joined result set; in other words, I get back an record for each of the joined results…in this scenario, I get back 3 results, even though there are only 2 distinct cars that fit the criteria.

Attempt #2

“But what about resultTransformer()“, you say? Yes, I tried that as well. Here’s a test method:

public struct function getCarsInsuredForYoungDrivers_Join_Distinct( required Numeric Age ) {
    var c = newCriteria();
    c.createAlias( "InsuredDrivers", "id" )
       .isLt( "id.Age", javaCast( "int", arguments.Age ) )
       .resultTransformer( c.DISTINCT_ROOT_ENTITY );
    var results = {
        data = c.list( asquery=true ),
        count = c.count()
    };
    return results;
}

And here’s the result:

This one’s a bit more interesting. The log from Hibernate is, of course, the same as the first query. However, because of the resultTransformer, the duplicative result is removed. The count() method, however, does not take into account the resultTransformer (and really it shouldn’t I don’t think), so I still get back an ultimately bad result. Correct data, incorrect count. Bummer.

Attempt #3

Of course, at the end of your rope, CriteriaBuilder always has the handy sqlRestriction(), which allows you send through arbitrary SQL that wil be added to any other criteria that you’ve specified. Here’s an example:

public struct function getCarsInsuredForYoungDrivers_SQL( required Numeric Age ) {
    var c = newCriteria();
    c.sqlRestriction( "CarID in ( select CarID from Insured i join Driver d on i.DriverID=d.DriverID where d.Age

Here’s the result:

And the Hibernate log:

List()

select  this_.CarID as CarID47_0_, this_.Make as Make47_0_, 
        this_.Model as Model47_0_, this_.`Year` as Year4_47_0_ 
from    Car this_ 
where   CarID in (
    select CarID 
    from   Insured i
    join   Driver d  on i.DriverID=d.DriverID 
    where  d.Age

Count()

select count(*) as y0_ 
from Car this_ 
where CarID in (
    select CarID 
    from   Insured i 
    join   Driver d  on i.DriverID=d.DriverID 
    where  d.Age

So guess what? That works. Not only do I get back the desired result set (without having to use a result transformer), but I also get back the correct count. All is well.

Or is it? Sure this works, but philosophically I have a problem with it. It’s one thing if I’m running a one-off criteria query and need to use this approach. However, if I’m actually in a real application that is, more than likely, dynamically adding criteria, guess where I’m back to: string concatenation. Yuk.

Toward a Solution

To be perfectly forthcoming, I *nearly* stopped here. I had spent more time frustratingly exploring this issue than I really wanted to, and, of course, when presented with something-approaching-a-workable-solution, it is always incredibly tempting to call good enough good enough.

However, I didn’t stop. Once I discovered the subquery solution, it changed my Google-foo. Instead of trying to find ways to work around the count/transformer/whatever issues, I started searching for solutions to hibernate-specific questions involving subqueries.

DetachedCriteria and Subqueries

It turns out that the Hibernate criteria query API has a DetachedCriteria class that, according to the docs:

“…allows you to create a query outside the scope of a session and then execute it using an arbitrary Session.”

Hmm, ok. As it turns out, however, it’s also the gateway for implementing subqueries within a Hibernate criteria query. In other words, we have a *really* good way of programmatically adding subqueries right in front of us.

Here’s my final method:

public struct function getCarsInsuredForYoungDrivers_SubQuery( required Numeric Age ) {
    var c = newCriteria();
    var s = createObject( "java", "org.hibernate.criterion.Subqueries" );
    var d = createObject( "java", "org.hibernate.criterion.DetachedCriteria" );
    var dc =  d.forEntityName( "Insured", "InsuredDrivers" )
        .setProjection( c.projections.property( "CarID" ) )
        .createAlias( "DriverID", "d" )
        .add( c.restrictions.isLT( "d.Age", javaCast( "int", arguments.Age ) ));
    c.add( s.propertyIn( "CarID", dc ) );
    var results = {
         data = c.list( asquery=true ),
        count = c.count()
    };
    return results;
}

So let’s walk through this. Obviously the first couple of lines are simply creating instances of the Subqueries and DetachedCriteria. But let’s start with the DetachedCriteria. Basically, you want to do at least two things:

1. Use the forEntityName() method to define which entity you’d like to use in your subquery.
2. Set a projection for the property(ies) you want to key on in the subquery. So for example, if you’d like to have the results of the subquery tied to the ID of the CriteriaBuilder’s entity (in this case, Car->;CarID), you’ll want to use that.

Now, notice what comes next. Since I’m within the DetachedCriteria (e.g., the soon-to-be subquery), I can now create the joins that I needed in my first several queries in order to get at the “Age” property. And since this is now in a subquery, my result counts aren’t going to get messed up because of this necessary join

Once I’m done defining the DetachedCriteria, all that’s left is to add it as a proper subquery. This is done via the Subqueries class. Very simply, all you have to do is to add a Subqueries instance to your CriteriaBuilder object. Subqueries come in a variety of flavors (see the docs for all possible methods). In this case, I want my subquery to return all insured cars whose drivers are under the age of 26. To do this, I use the propertyIn() method, which takes two arguments:

1. Property name
2. A DetachedCriteria (handy that we already built one!)

And that’s it. When we run this method, we get the following:

And the Hibernate log:

List():

select this_.CarID as CarID47_0_, this_.Make as Make47_0_,
       this_.Model as Model47_0_, this_.`Year` as Year4_47_0_ 
from   Car this_ 
where this_.CarID in (
    select InsuredDrivers_.CarID as y0_ 
    from   Insured InsuredDrivers_ 
    inner  join Driver d1_ on InsuredDrivers_.DriverID=d1_.DriverID 
    where  d1_.Age

Count():

select count(*) as y0_ 
from   Car this_ 
where  this_.CarID in (
    select InsuredDrivers_.CarID as y0_ 
    from   Insured InsuredDrivers_ 
    inner  join Driver d1_ on InsuredDrivers_.DriverID=d1_.DriverID
    where  d1_.Age

Rock. That works. I get back the correct results, the count is right, and I have a programmatic way to build not only regular criteria queries, but also those that include subqueries.

Wrapping Up

As we saw, not only is it possible to use subqueries in Hibernate criteria queries, but there is actually a built in way for developing a programmatic query/subquery builder. Obviously, we could go a few extra steps and actually implement this into ColdBox’s CriteriaBuilder (I’ve started, but haven’t had time to think through all parts, or to test beyond my own limited examples), but this is, I think, something that’s at least workable–if nothing else, it’s better (to me) than using sqlRestriction and building out concatenated SQL strings. 🙂

So, what do you think? If there are better ways of approaching/solving this issue, I’m very willing to hear them. In everything I do, I definitely appreciate any constructive feedback that others can provide, so I eagerly await words from the masters!