Grails named query for NOT IN

Go To


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?


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:
2012-04-03 23:12
by dbrin


Maybe sombething like this:

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

or using a criteria

Book.withCriteria {
    not {
        'in'('author', ['Borges', 'Sabato', 'Cortazar'])
} - the docs for 'in' criterion method has a chained 'not' method also.

2012-04-03 23:18
by pablomolnar
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 2012-04-03 23:23
Criteria dsl added - pablomolnar 2012-04-03 23:41
Thanks for your time. Corrected the syntax. inList is reserved for constraints, but quoted 'in' works - dbrin 2012-04-03 23:57