Grails - Perform WHERE IN subquery using CreateCriteria

Question

I have a domain class like the following:

class Blog {

    String name
    Set tags = []

    static constraints = {}

    static hasMany = [tags: String]
}

I have a search routine, where I would like to be able to filter based on both the name and the tags collection.

    def results = Blog.createCriteria().list(params) {
        def query = params.query?.trim()
        if(query) {
            or {
                like("name", "%${query}%")
                like("tags", "%${query}%") <--- This does not work...
            }
        }
    }

In SQL, I would implement this with a WHERE IN sub query against the tags table, filtering based on the parent. However, I have been unable to find a way to do this in Grails using the CreateCriteria builder.

Can someone provide me insight if this is possible?

I would prefer to not have to implement this in HSQL if possible, but would be open to suggestions on how this may be achieved...

I have considered something along these lines:

from Blog as b
where b.name like '%:query%' or b.id in ( select ... from tags where .... )

But I am not sure what to specify for the tag table name, since it is not a domain class, just a list of strings.

Thanks!

Answer 1

This works for me (using grails 2.4.2):

    def q = Blog.createCriteria ()
    def r = q.list {
        createAlias('tags', 't')
        or {
            like ("name", "Foo%")
            like ("t.elements", "%b%")
        }
    }

from: https://stackoverflow.com/a/24211973/1262371

Answer 2

I believe what you are searching for is the "elements" keyword (in HQL)

The query would then be:

def results = Blog.executeQuery('from Blogas b where :name in elements(tags) or b.name like :name', [name: "myGreatTagOrName"])

Hope it helps.