Search code examples
jpagrails-ormjpqlgrails3grails-3.3

Querying backwards association with JPA QL in a grails 3 Data Service, MissingMethodException executeQuery on Object?

In a database, that gathers authors and their books, we add the books by an Author as Book instances.

However, an Author may have books that have not yet been added to the database. This is why we have an outside sanity counter, telling us how many books an Author actually has published. If the numberOfKnownBooks is equal to the number of Book instances in the database, then we consider that we have an Author's complete bibliography.

Data Model

An SQL query to obtain all the Authors whose bibliography is complete would be written like this in SQL:

select author.id, count(*)
from book inner join author on book.author_id=author.id
group by author.number_Of_Known_Books, author.id
having count(*) = author.number_Of_Known_Books

The difficulty here is that the join points in the opposite direction, and that normal examples usually show how to create a query to obtain Books joined with Authors, not the other way around.

I have found some articles in stackoverflow explaining how to do something like this in plain JPQL (minus the group by ... having complication) here, but doing this in the DSL expected by the @Query annotation in a Data Service in GORM 6.1+, I get an exception, occurring in the dungeons of generated code by an AST, saying that it can't find method executeQuery on java.lang.Object. This is a reasonable complaint, but I have no idea why it is happening. No way to step debug this code unfortunately.

This is my Data Service:

import grails.gorm.services.Query
import grails.gorm.services.Service


@Service
abstract class AuthorService {

    @Query("""
            select a.*, count(a.id)
            from ${Book b} join ${b.author} a
            group by a.numberOfKnownBooks, a.id
            having count(*) = a.numberOfKnownBooks
""")
    abstract List<Author> retrieveCompleteAuthors()
}

UPDATE: removed useless annotations @GrailsCompileStatic and @Transactional as per jeff's instructions

FULL SOLUTION, with the right JPQL query:

@Service(Author)
abstract class AuthorService {
    @Query("""
        select auth from ${Author auth}  where auth.id in (
            select a.id
            from ${Book b} join ${b.author} a
            group by a.numberOfKnownBooks, a.id
            having count(*) = a.numberOfKnownBooks
        ) 
""")
    abstract List<Author> retrieveCompleteAuthors()
}
Solution

  • I expect that isn't really the HQL you want but that is a separate issue. The error you asked about should go away if you do something like this...

    import grails.gorm.services.Query
import grails.gorm.services.Service

@Service(Author)
abstract class AuthorService {

    @Query('''
            select a.*, count(a.id)
            from ${Book b} join ${b.author} a
            group by a.numberOfKnownBooks, a.id
            having count(*) = a.numberOfKnownBooks
    ''')
    abstract List<Author> retrieveCompleteAuthors()
}