Grails named query for NOT IN

Question

Trying to come up with a succinct way to create a named query with NOT IN clause. Restrictions class does not seem to have a "not in" construct. Anyone know a good way to do this?

Solution:

static namedQueries = {
        activeOnly {
            eq 'active', true
        }
        open {
            ne 'state', this.STATE_COMPLETED
        }
        my { user ->
            or {
                eq 'createdBy', user
                eq 'reviewer', user
                eq 'assignee', user
                eq 'requestedFor', user
                ilike 'notifyList', "%$user%"
            }
        }
        topLevel {
            not {'in'('type', [RequestType.Part])}
        }
    }

Note: the word 'in'must be quoted because its reserved.

These named queries can be used like this:

Request.activeOnly.open.topLevel.list(params)

score 1 · Accepted Answer · edited Apr 03 '12 at 23:55

1

Maybe sombething like this:

Book.findAll("from Book b where b.author not in (?)", ['Borges', 'Sabato', 'Cortazar'])

or using a criteria

Book.withCriteria {
    not {
        'in'('author', ['Borges', 'Sabato', 'Cortazar'])
    }
}

http://grails.org/doc/1.3.x/ref/Domain%20Classes/createCriteria.html - the docs for 'in' criterion method has a chained 'not' method also.

edited Apr 03 '12 at 23:55

dbrin

15,525
4
56
83

answered Apr 03 '12 at 23:18

pablomolnar

528
6
14

Thank you for the reply. That might work but is not a named query. Named queries use criteria api to create and cache prepared statements. – dbrin Apr 03 '12 at 23:23
Thanks for your time. Corrected the syntax. inList is reserved for constraints, but quoted 'in' works. – dbrin Apr 03 '12 at 23:57

Grails named query for NOT IN

1 Answers1