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!

score 1 · Answer 1 · answered Jul 31 '14 at 19:45

1

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.

answered Jul 31 '14 at 19:45

Raanan W

1,295
7
19

Thank you. What you have above would only work for an exact string match though. Do you know if there is a way to filter the tags via a like condition instead? – cweston Jul 31 '14 at 20:10
Sorry, can't help with that, My best solution would be to actually create a domain object with a single String, search using Tag.findAllByTagNameLike("whateverYouWannaSearch") and then use this in a query similar to the above. But this is not a single query, I assume it's not exactly what you wanted to do... – Raanan W Aug 02 '14 at 19:29

score 1 · Answer 2 · edited May 23 '17 at 10:28

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

edited May 23 '17 at 10:28

Community

1
1

answered Jul 31 '14 at 22:02

Martin Hauner

1,593
1
9
15

I still get "org.hibernate.MappingException: collection was not an association" when using this in 2.4.2. I have also found that this is a known issue that is still open: [GRAILS-5887](https://jira.grails.org/browse/GRAILS-5887). – cweston Oct 31 '14 at 20:56

Grails - Perform WHERE IN subquery using CreateCriteria

2 Answers2