Criteria query on associated objects

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)

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

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

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

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

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

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

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

Luis, can the BaseORMService handle group by?

I don’t think so.

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

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

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.

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

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