CriteriaBuilder, NOT and OR

Communities ColdBox HMVC
1

Hi

[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’)
.isNull(‘ACCR_TYPE’)
.list();

…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?

Thanks,

2

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 = c.in("name","luis,fred,joe")
     .OR( c.restrictions.isNull("age"), c.restrictions.eq("age",20) )
     .list();

http://wiki.coldbox.org/wiki/Extras:CriteriaBuilder.cfm#Restrictions

Thanks!

~Brad

ColdBox Platform Evangelist
Ortus Solutions, Corp

E-mail: brad@coldbox.org
ColdBox Platform: http://www.coldbox.org
Blog: http://www.codersrevolution.com

3

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??

4

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

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

ColdBox Platform: http://www.coldbox.org

ContentBox Platform: http://www.gocontentbox.org
Linked In: http://www.linkedin.com/pub/3/731/483

Social: twitter.com/ortussolutions | twitter.com/coldbox | twitter.com/lmajano | twitter.com/gocontentbox

5

Bingo!

Thanks Luis, $or does indeed solve it.

~~Michael