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

Question

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)
}

Possible duplicate of [node-postgres: how to execute "WHERE col IN ()" query?](https://stackoverflow.com/questions/10720420/node-postgres-how-to-execute-where-col-in-dynamic-value-list-query) — Lukasz Szozda, Sep 16 '17 at 06:21
It's rather a duplicate of: [grails grom create criteria with many-to-many mapping](https://stackoverflow.com/questions/43959938/grails-grom-create-criteria-with-many-to-many-mapping) — gregorr, Sep 18 '17 at 07:22

score 0 · Answer 1 · answered Sep 16 '17 at 18:27

0

Is this what you're looking for?

Book.findAllByAuthorInList(authors)

answered Sep 16 '17 at 18:27

pawels

1,070
1
10
16

score 0 · Accepted Answer · answered Sep 17 '17 at 08:16

0

You need to join the author first:

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

answered Sep 17 '17 at 08:16

gregorr

373
4
13

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

2 Answers2