Grails named queries not working with "in" statement

Question

I have a Grails application (2.2.4). Where in domain class I have looks like this

class Author implements Serializable {
    ....
  static hasMany = [
    book : Book 
  ]
  static namedQueries = {
      hasGenre {genreNameList -> 
            book{
               genres {  
                   'title' in genreNameList 
           } 
       }
   }
  }
}

class Book implements Serializable{

    Author author
    Genres genres
    static belongsTo = [author: Author , genre: Genres ]
       static mapping = {
       .....
        author lazy: false
       }
}

class Genres implements Serializable{

    String title

 }

If I run the query as below, all the values are retrieved, and not only the Authors with atleast one book with genere in the genereNameList

String comaSeperatedGenereName = "genere1,genere2"
def genereNameList = comaSeperatedGenereName.split(",")
Author.hasGenre(genereNameList)

But If I change the namedQuery like the following,

      hasGenre {genreName -> 
            book{
               genres {  
                   eq 'title' , genreName 
           } 
       }

And If I pass a String like following

Author.hasGenre('genere1')

This works as expected. Is there something i'm missing?

Thanks in advance

Answer 1

There is a groovy in operator and I suspect that instead of the criteria in you are getting the groovy in operator.

Try changing your code to

  static namedQueries = {
      hasGenre {genreNameList -> 
            book{
               genres {  
              'in'  'title', genreNameList 
           } 
       }
   }
  }

Answer 2

A nuance between criteria queries and native queries such as SQL is that a criteria query is not an actual query. It's a query builder.

In other words, the criteria query doesn't run in the same sense as a SQL query runs in a SQL database. Instead, a criteria query is executed to generate a database query. With that in mind it's easier to see what went wrong.

hasGenre {genreNameList -> 
    book{
       genres {  
           'title' in genreNameList 
       }
   } 
}

The expression 'title' in genreNameList returns a Boolean. It has no effect on the criteria query because none of the criteria query's builder methods are called. So in the end the query is constructed to return everything.

The equivalent of Groovy's in keyword in a criteria query is the in() method.

hasGenre {genreNameList -> 
    book{
       genres {  
           'in'('title', genreNameList)
       }
   } 
}

This constructs the query you're expecting. However, because in is a keyword in Groovy, in order to execute the method its name must be quoted. I think a more aesthetically-pleasing way to accomplish the same thing is the inList() method.

hasGenre {genreNameList -> 
    book{
       genres {  
           inList('title', genreNameList)
       }
   } 
}

Finally, to better illustrate the builder concept, here's a more verbose way of accomplishing the same thing.

hasGenre {genreNameList -> 
    book{
       genres {  
           or {
               genreNameList.each {
                   eq('title', it)
               }
           }
       }
   } 
}

This query is built by calling eq() for each genre title. The end result is a query with multiple or conjunctions (ex. title = 'foo' or title = 'bar'...).