Grails GORM (Hibernate) query

Question

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