Search code examples
grailshqlgrails-orm

GORM: Find by collection contents

I Have the following two classes:

class Person{

 String name

  static hasMany = [
     items: Item
 ]
}

class Item{
 String name
}

Multiple Persons can also have the same Item. I'm trying to get a list of all Persons that have a specific item in their collection. I.E. Both Person A and B have Item A in their list so return them both. Unfortunately their is no findAllByCollectionContains() the closest is is findAllByCollection() which requires an exact set.

I've been trying executeQuery to give me a but more control and haven't come up with anything yet.

Example of what I have tried:

 Person.executeQuery("select name from Person p where ? in p.items",[item])

Any suggestions?

Solution

  • You have to join the items collection, then you can query on it easily

     Person.executeQuery("select name from Person p join p.items as i where i = ?",[item])