How do I check if a given list is a subset of a field value in GORM?

Question

I'm working on some sort of advanced search feature with variable fields. Some of the search fields are lists of some primitive(ish) objects (String, enums, etc.). I want to be able to retrieve records whose values are a subset of some given list.

To illustrate, say I have a Book class (assume the model is appropriate this way):

class Book {
    ...
    List authors = []
    ...
}

In addition say we have the following book records:

Book(title: 'Great Expectations of Tom Sawyer', authors: ['cdickens', 'mtwain'])
Book(title: 'Huckleberry Potter in Bleak House', authors: ['cdickens', 'mtwain', 'jrowling'])
Book(title: 'A Christmas Carol', authors: ['cdickens'])

Then, I'm given a list of author (names) authorFilter = ['cdickens', 'mtwain'] to search for any collaborative works of cdickens and mtwain. How do I express this using GORM's where construct? Is it even possible to cover this using it?

Basically, what I want to do is:

Book.where {
    authorFilter.every { it in authors }
}

Answer 1

This question has come up before. Unfortunately, where nor criteria queries have an every() equivalent. But there's a hack that may work for you. But first, I'll expand on your domain model.

Domain model

class Book {
    String title

    static hasMany = [authors: Author]
    static belongsTo = Author
}

class Author {
    String name

    static hasMany = [books: Book]
}

HQL query

Using the domain model described above, you can use the following HQL query.

def hql = """
SELECT b FROM Book AS b
INNER JOIN b.authors AS a
WHERE a.name in :authors
GROUP BY b
HAVING COUNT(b) = :count
"""

def books = Book.executeQuery(hql, [authors: authorFilter, count: authorFilter.size()])

How it works.

You can read about how this query works in the other question I mentioned.

Answer 2

I don't think this is any better than @EmmanuelRosa's answer but I have another approach using HQL and the executeQuery method.

Using the same domain model he's given in his answer, I use the MEMBER OF expression to restrict the results.

def authorFilter = [Author.get(1)]

def authorMemberOfRestriction = ""
def namedParameters = [:]

authorFilter.eachWithIndex{ aut, ind ->
    authorMemberOfRestriction += ":author${ind} MEMBER OF b.authors AND "
    namedParameters.put("author" + ind, aut)
}

namedParameters.put('count', authorFilter.size())

def hql = """
FROM Book b 
WHERE 
(
${authorMemberOfRestriction}
size(b.authors) = :count
)
"""

def books = Book.executeQuery(hql, namedParameters)

Mine is somewhat different in that the authorFilter is a collection of Author domain class instances; I found it to work much easier for the MEMBER OF expression and truthfully is more of a depiction of how the real data would be modeled.

You can see that I build the multiple MEMBER OF expressions with the eachWithIndex, using the index on both sides of the named parameters. It's not exactly pretty but I don't believe there is a way around this and still use this approach.

I think @EmmanuelRosa's approach is probably the 'cleaner' option but the MEMBER OF approach makes more sense in my head as far as the logic is concerned.

Answer 3

There doesn't seem to be a simpler way of doing this other than doing HQL query. Taking hints from this answer to a very similar question, I figured out a solution to my problem.

To be clear, the Book should already have relation to many String using the hasMany construct:

class Book {
    ...
    static hasMany = [authors: String]
    ...
}

To fetch results:

def results = Product.executeQuery("select p from Products p join p.tags t where t in :tags", [tags: givenTags])