Criteria query on associated objects

Communities ColdBox HMVC
#1

I started using CB's Criteria query and it works great for semi-simple
restrictions, but it couldn't handle criteria of associated objects
(or if it can, I couldn't find it by looking at the source)

A raw example would be...
var Restrictions =
CreateObject("java","org.hibernate.criterion.Restrictions");
var Criteria = ORMGetSession().createCriteria( 'user' );

Criteria.createCriteria( 'favoritetechniques' ).add( Restrictions.in("technique_id",
JavaCast("java.lang.Integer[]",ListToArray(techniques) ) ) );

var results = Criteria.list();

(http://pastie.org/private/b5nf0lrbgvwkadjjsklwq because formatting
will be off)

-- I just checked the github dev branch, I see the changes to

for associated objects but I'm not seeing how it can be used.

(Really trying to keep using the BaseORMService, instead of breaking
out of it)

#2

I will post a sample later as I don't have one right now. But we do support this now by adding to the criteria array the name of tqhe associated object. Then we will create another critia object based on the associated entity so you can then do association criteria.

Luis Majano
CEO
Ortus Solutions, Corp
Toll free phone/fax: 1-888-557-8057
Mobile: 909-248-3408

www.coldbox.org

#3

That would be great Luis. The hibernate criteria queries are really
powerful

#4

Here is a full sample from blogbox:

/**

  • entry search returns struct with keys [entries,count]
    */
    struct function search(search="",isPublished,category,author,max=0,offset=0){
    var results = {};
    // get Hibernate Restrictions class
    var restrictions = getRestrictions();
    // criteria queries
    var criteria = [];

// isPublished filter
if( structKeyExists(arguments,“isPublished”) AND arguments.isPublished NEQ “any”){
arrayAppend(criteria, restrictions.eq(“isPublished”, javaCast(“boolean”,arguments.isPublished)) );
}
// Author Filter
if( structKeyExists(arguments,“author”) AND arguments.author NEQ “all”){
arrayAppend(criteria, restrictions.eq(“author.authorID”, javaCast(“int”,arguments.author)) );
}
// Search Criteria
if( len(arguments.search) ){
// like disjunctions
var orCriteria = [];
arrayAppend(orCriteria, restrictions.like(“title”,"%#arguments.search#%"));
arrayAppend(orCriteria, restrictions.like(“content”,"%#arguments.search#%"));
// append disjunction to main criteria
arrayAppend( criteria, restrictions.disjunction( orCriteria ) );
}
// Category Filter
if( structKeyExists(arguments,“category”) AND arguments.category NEQ “all”){

// Uncategorized?
if( arguments.category eq “none” ){
arrayAppend(criteria, restrictions.isEmpty(“categories”) );
}
// With categories
else{
// create association criteria, by passing a simple value the method will inflate.
arrayAppend(criteria, “categories”);
// add the association criteria to the main search
arrayAppend(criteria, restrictions.in(“categories.categoryID”,JavaCast(“java.lang.Integer[]”,[arguments.category])));
}
}

// run criteria query and projections count
results.entries = criteriaQuery(criteria=criteria,offset=arguments.offset,max=arguments.max,sortOrder=“publishedDate DESC”,asQuery=false);
results.count = criteriaCount(criteria=criteria);

return results;
}

Luis F. Majano
CEO
Ortus Solutions, Corp
www.ortussolutions.com

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

Social: twitter.com/lmajano facebook.com/lmajano

#5

Thank you Luis, I see now what I was doing wrong.

#6

That seems like a horribly long way to write an otherwise simple
query. Wouldn't it be a lot more simple to use HQL? Perhaps I'm
missing the idea for why you'd create criteria and restriction arrays
instead of just writing HQL. Is it a mere matter of preference or am I
missing something?

Thanks,
Judah

#7

One of the biggest advantages is that youdont have to create conditional strings for hql where you have to manually build the hql to execute. Criterias and restrictions can also give you projections which normal hql can't. You can add, or, and and do lots of more complicated quieries using the programmatical dsl that is way cleaner than hql. Hql has its place and these don't replace them it enhances them

#8

What do you mean by "also give you projections which normal hql
can't"? I'm not familiar with the term projections in that context.

ANDs and ORs are as easy to do in HQL as they are in SQL. Admittedly,
I haven't done a huge amount with HQL and certainly no terribly
complicated queries so perhaps I just haven't run into the
limitations. Do you know of any examples out in web land comparing HQL
and Criterias/Restrictions and showing when each is appropriate?

Thanks Luis.

Judah

#9

Luis, can the BaseORMService handle group by?

#10

I don’t think so.

Current 3.1 release: https://github.com/ColdBox/coldbox-platform/blob/master/system/orm/hibernate/BaseORMService.cfc

#11

Group by for Hql or for criteria queries?

Luis Majano
CEO
Ortus Solutions, Corp
Toll free phone/fax: 1-888-557-8057
Mobile: 909-248-3408

www.coldbox.org

#12

Sorry should have been more specific. I meant for criteria queries.

I spent a couple hours testing to see if I could apply this myself to
CB, but along the way learned how complicated projections can get,
specially when it comes to fetching properties and result
transformers... the whole process seems unnecessarily convoluted.

#13

Yea, definitely feel you, it can get very convoluted but extremely powerful.

Luis F. Majano
CEO
Ortus Solutions, Corp
www.ortussolutions.com

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

Social: twitter.com/lmajano facebook.com/lmajano

#14

Luis, thanks for posting the example for the ContentBox criteria…the bit for the associations saved me a lot of frustration :slight_smile: