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'

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

Since Grails 2.4 , you can write this query as a single query using a subquery via notIn & DetachedCriteria(where):

& we have two syntax :

@java.lang.Override public Criteria notIn(java.lang.String propertyName, QueryableCriteria<?> subquery)

@java.lang.Override public Criteria notIn(java.lang.String propertyName, groovy.lang.Closure<?> subquery)

For your case , following should works:

def c = Tabl1.createCriteria().list {
              notIn 'x',Table2.where { eq('y','something') }.projections {
                     property 'x'
                     }.list()
    }