Is there a 'not in' equivalent in GORM?

Question

Is this possible to convert in createCriteria()?

SELECT * FROM node WHERE (node.type = 'act' AND nid NOT IN (SELECT nid FROM snbr_act_community)) LIMIT 10

I know there's a 'in' operator and here's what I have so far:

def c = VolunteerOpportunity.createCriteria()
def matchingActs = c.list {
    node {
        eq('type', 'act')
    }
    maxResults(10)
}

Just want to see if this is possible. Otherwise, I guess this is possible in HQL right?

Thanks.

Answer 1

not tried it myself but looking at the Grails doc and hibernate api you create nodes on this builder map with the static methods found in the Restrictions class of the Hibernate Criteria API 1. So something like

 def c = VolunteerOpportunity.createCriteria()
def matchingActs = c.list {
    node {
        not(in('propertyName', ['val1','val2']))
    }
    maxResults(10)
}

Since you chain the in method (that returns a Criterion) with the not method (that takes a Criterion as argument and returns a negated version)

Answer 2

thanks Sammyrulez for the code. got an idea from that. tested it but it didn't work. i fixed it and here's the final working code:

def ids = [14400 as long, 14401 as long]

def c = VolunteerOpportunity.createCriteria()
def matchingActs = c.list {
    node {
        eq('type', 'act')
        not { 'in'(ids) }
    }
    maxResults(10)
}

now i know how to use 'not' operator. thanks a lot!