Grails named query for NOT IN

Go To StackoverFlow.com

0

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


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.

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