Matching hasMany children with dynamic Grails finders

In Grails, I'm trying 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 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 would like to do is use a dynamic finder for the author. I'm looking for an exact match of the hasMany relationship 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 worked with dynamic crawlers with hasMany domain class relationships? What is the most "Grails-y" solution for getting the desired behavior?

+3


source to share


3 answers


It is not clear from your domain model if it Book

belongs to the author. If so, you should add this fact to your domain model and query as Tom Metz said.

Let me get this right. You want to find the author who wrote the books with the title (or id), that is, "Book 1" and "Book 2". To complete the test, you must join the book table twice to compare the book title by connecting to Book 1 and the book title to join Book 2.

It can be assumed 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 concatenates the assertions in every book close into a single connection.



This is the result of the 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 cannot be archived using grails api criteria. 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")
}

      

+2


source


I'm not 100% sure that you can actually get this to work (unless I'm missing some docs). But to get what you want, you want to use 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")
  }
}

      

+1


source


You need to add a one-to-many bidirectional relationship to your domain objects. Introduce the book:

static belongsTo = [ author:Author ]

      

Then you can request:

Author a = Book.author

      

0


source







All Articles