Matching hasMany children with Grails dynamic finders

Question

In Grails, I'm attempting to find an instance of a domain class that has exact entries in a one-to-many relationship. Consider this example:

class Author {
    String name
    List<Book> books

    static hasMany = [books:Book]
}

class Book {
    String title

    static belongsTo = Author
}

My database then appears as such:

author                     book
-------------------------    ------------------------
| id | name             |    | id | title           |
|----|------------------|    ------------------------
| 1  | John Steinbeck   |    | 1  | Grapes of Wrath |
| 2  | Michael Crichton |    | 2  | East of Eden    |
-------------------------    | 3  | Timeline        |
                             | 4  | Jurassic Park   |
                             ------------------------

author_book
----------------------------------------
| author_books_id | book_id | book_idx |
----------------------------------------
| 1               | 1       | 0        | // John Steinbeck - Grapes of Wrath
| 1               | 2       | 1        | // John Steinbeck - East of Eden
| 2               | 3       | 0        | // Michael Crichton - Timeline
| 2               | 4       | 1        | // Michael Crichton - Jurassic Park
----------------------------------------

What I'd like to be able to do is use a dynamic finder on author. I'm searching for an exact match on the hasMany relation, to match this behavior:

Author.findByBooks([1]) => null
Author.findByBooks([1, 2]) => author(id:1)
Author.findByBooks([1, 3]) => null
Author.findByBooks([3, 4]) => author(id:2)

Attempting this results in an ugly Hibernate error:

hibernate.util.JDBCExceptionReporter No value specified for parameter 1.

Has anyone had dynamic finders work with hasMany relationships of domain classes? What is the most 'Grails-y' solution to get the desired behavior?

Answer 1

Its not clear from your domain model if Book belongs to author. If so, you should add that fact to your domain model and query like Tom Metz said.

Let me get this right. You want to find the Author[s] that have writen the Books with title (or id) i.e. 'Book 1' and 'Book 2'. To make the check work you have to join the Book table two times in order to compare the book title from join one with 'Book 1' and the book title from join two with 'Book2'.

One could assume that the following test should work:

void setUp() {
    def author = new Author(name: "Ted Jones").save(flush: true)
    def author2 = new Author(name:  "Beth Peters").save(flush: true)
    def author3 = new Author(name:  "Foo Bar").save(flush: true)
    def book1 = new Book(title: 'Book 1').save(flush: true)
    def book2 = new Book(title: 'Book 2').save(flush: true)
    def book3 = new Book(title: 'Book 3').save(flush: true)
    def book4 = new Book(title: 'Book 4').save(flush: true)
    author.addToBooks(book1)
    author.addToBooks(book3)

    author2.addToBooks(book2)
    author2.addToBooks(book4)

    author3.addToBooks(book1)
    author3.addToBooks(book2)
}

void testAuthorCrit() {
    def result = Author.withCriteria() {
        books {
            eq("title", "Book 1")
        }
        books {
            eq("title", "Book 3")
        }
    }
    assert 1 == result.size()
    assertTrue(result.first().name == "Ted Jones")
}

But it turns out, that the result set is empty. Grails merges the statements in each books closure into one join.

This is the resulting query:

 select this_.id as id1_1_, this_.version as version1_1_, this_.name as name1_1_, books3_.author_books_id as author1_1_, books_alia1_.id as book2_, books3_.books_idx as books3_, books_alia1_.id as id0_0_, books_alia1_.version as version0_0_, books_alia1_.title as title0_0_ from author this_ inner join author_book books3_ on this_.id=books3_.author_books_id inner join book books_alia1_ on books3_.book_id=books_alia1_.id where (books_alia1_.title=?) and (books_alia1_.title=?)

ASFAIK this cannot be archieved using the grails criteria api. But you can use hql instead. The following test works:

void testAuthorHql() {
    def result = Author.executeQuery("select a from Author a join a.books bookOne join a.books bookTwo where bookOne.title=? and bookTwo.title=?", ['Book 1', 'Book 3'])
    assert 1 == result.size()
    assertTrue(result.first().name == "Ted Jones")
}

Answer 2

I'm not 100% sure you can actually get that to work (unless I am missing some docs somewhere). But to get what you need, you'd want to use a criteria:

class AuthorIntegrationTests {

  @Before
  void setUp() {
    def author = new Author(name: "Ted Jones").save(flush: true)
    def author2 = new Author(name:  "Beth Peters").save(flush: true)

    def book1 = new Book(title: 'Book 1').save(flush: true)
    def book2 = new Book(title: 'Book 2').save(flush: true)
    def book3 = new Book(title: 'Book 3').save(flush: true)
    def book4 = new Book(title: 'Book 4').save(flush: true)

    author.addToBooks(book1)
    author.addToBooks(book3)

    author2.addToBooks(book2)
    author2.addToBooks(book4)
  }

  @After
  void tearDown() {
  }

  @Test
  void testAuthorCrit() {
    def result = Author.withCriteria(uniqueResult: true) {
      books {
        inList("id", [1.toLong(), 3.toLong()])
      }
    }
    assertTrue(result.name == "Ted Jones")
  }
}

Answer 3

You need to add bidirectional one-to-many relation to your domain objects. Into you Book domain add:

static belongsTo = [ author:Author ]

You can then query:

Author a = Book.author