CriteriaBuilder, NOT and OR


[Previous post sent incomplete…]

I’m getting re-acquainted with ColdBox after very many years and I haven’t used its ORM or Hibernate before.

I’m using CriteriaBuilder to do this:

prc.domainACSTTemplate = c
.createAlias(‘ACCR_DOMA_KY’, ‘d’, c.LEFT_JOIN)
.withProjections(property = ‘ACMA_KY,d.SHORT_DESCRIPTION_LB’)

…which works, but isNull(‘ACCR_TYPE’) isn’t what I want. I actually want is:

“(where ACCR_TYPE is null or ACCR_TYPE <> 2)”

That is, null, 0 or 1 would be acceptable.

I have tried many options but all seem to cause syntax errors, among them are:

.isNot(‘ACCR_TYPE’, JavaCast(“int”, 2))
.or(‘ACCR_TYPE is null’, ‘ACCR_TYPE = JavaCast(“int”, 1)’, ‘ACCR_TYPE = JavaCast(“int”, 2)’)
.or(isNull(‘ACCR_TYPE’), isEq(‘ACCR_TYPE’, JavaCast(“int”, 0)), isEq(‘ACCR_TYPE’, JavaCast(“int”, 1)))

I just need to get my head around the right syntax, but I can’t get it, and I don’t find enough examples of NOT and OR in the tutorials/documentation.

Can anyone help?


Right off, remember each method is part of the criteria builder object.

.or(isNull(‘ACCR_TYPE’), isEq(‘ACCR_TYPE’, JavaCast(“int”, 0)), isEq(‘ACCR_TYPE’, JavaCast(“int”, 1)))

That line of code is trying to call native, built in CFML methods isNull() and isEq(). I think those need to be c.restrictsions.isnull() and c.restrictions.isEq so your actually calling the method on your object.

I’m not 100% on criteria queries, but hopefully that gets you in the right direction. There’s some examples here in the docs.

// more complex var results ="name","luis,fred,joe")
     .OR( c.restrictions.isNull("age"), c.restrictions.eq("age",20) )



ColdBox Platform Evangelist
Ortus Solutions, Corp

ColdBox Platform:

Hi Brad

Thanks for the info. Sadly, that example also gives me an error. Thus:

#237 prc.domainACSTTemplate = c
#238 .createAlias(‘ACCR_DOMA_KY’, ‘d’, c.LEFT_JOIN)
#239 .withProjections(property = ‘ACMA_KY,d.SHORT_DESCRIPTION_LB’)
#240 .OR( c.restrictions.isNull(‘ACCR_TYPE’), c.restrictions.eq(‘ACCR_TYPE’,1) )
#241 .list();

Gives me:

Invalid construct??

Yes this is a stupid compiler bug. Use $or instead

Luis Majano
Ortus Solutions, Corp
P/F: 1-888-557-8057
Direct: (909) 248-3408

ColdBox Platform:

ContentBox Platform:
Linked In:

Social: | | |


Thanks Luis, $or does indeed solve it.