Posts tagged Hibernate

Criteria Builder Quickie: Disjunction

Mar 12th

If you use ColdBox’s awesome CriteriaBuilder at all, you know it’s dead simple to create complex AND dynamic criteria queries. If you’re like me, a lot of your criteria queries are a bunch of “and”s built together. For example, let’s imagine we’re trying to get all the cars from our database that have a make of Ford and a color of Red. A simple criteria query for this might look like:

// create criteria
var c = newCriteria();
    // add criteria
    c.isEq( "Make", "Ford" )
     .isEq( "Color", "Red" );
// get result
var result = c.list();

Pretty simple, right?

Of course, not all queries are “and“s. Sometimes, you need to do an “or“. Perhaps we want to get all the cars that are Ford OR Red. Again, this is very easy with CriteriaBuilder:

// create criteria
var c = newCriteria();
    // add criteria
    c.or( 
        c.restrictions.isEq( "Make", "Ford" ), 
        c.restrictions.isEq( "Color", "Red" ) 
    );
// get result
var result = c.list();

While this works, it’s not complete. Sure, there are some scenarios where you will know ahead of time what criteria you want to use in a criteria query. However, consider a scenario in which you have a search form in which a user can select from a list More >

CriteriaBuilder, Hibernate, ORM

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 >