Search code examples
postgresqlgrailsgrails-orm

How to query all the owned objects in one query MTM?

I have a list of the owning side of a many-to-many relationship. How do I query all the owned objects in one query using Grails GORM? In SQL I would used the join table and the owned table and the ids for the owning table with an in clause.

Example domain classes:

class Book {
  static belongsTo = Author
  static hasMany = [authors:Author]
  String title
}

class Author {
  static hasMany = [books:Book]
  String name
}

So I have a List or Set of Authors and I want to find all their Books in one query.

select b.*
  from book b
  join author_book ab on b.id = ab.book_id
 where ab.author_id in (1, 2, 3);

In Grails I tried the following but it fails.

def books = Book.withCriteria {
  inList('authors', authors)
}
Solution

  • You need to join the author first:

    def books = Book.withCriteria {
    authors {
        inList('id', authors*.id)
    }
}