Grails GORM (Hibernate) query

Question

Hello, I'm trying to do the below sql statement in GORM

select * from table1 where table1.x not in
      (select x from table 2 where y='something');

so, I have two tables, and needs to find the entries from table 1 which are not in table 2. In Grails

def xx= table2.findByY('something')
    def c = table1.createCriteria()
    def result= c.list {
      not (
        in('x', xx)
    )
    }

the syntax is wrong, and I'm not sure how to simulate not in sql logic.

As a learning point, if someone can also tell me why minus (-) operator in grails/groovy doesn't work with list. I tried getting x and y seperately, and doing x.minus(y), but it doesn't change the list. I saw an explanation at http://stackoverflow.com/questions/1754576/groovy-on-grails-list-not-working , but I would expect the list defined are local.

thank you so much.

Answer 1

Hmm, I'm just learning GORM myself, but one thing I see is that in is a reserved word in groovy and so needs to be escaped as 'in'

Answer 2

Ok.. got to work.. anyone interested..

Stephen was right, you have to escape in with 'in', as it is a reserved word. the syntax is.

def c = table1.createCriteria()
    def result= c.list {
      not {
        'in'("x", xx)
//xx could be a list, array etc. eg: [1,2,3]
    }
}

Answer 3

Even the above solution doesn't seem to work. It complaints of "No signature of method: static org.hibernate.criterion.Restrictions.not() is applicable for argument types: Closure, possible solution : not(org.hibernate.criterion.Criterion).

Any idea?