Search code examples
mysqlhibernategrailsgrails-orm

GORM: Query having subquery empty

Scenario:

I have two domain beans Book and Author. Author has a one-to-many association with Book.

I want authors who NOT have written books in this year (it isn't the real scenario :) )

This is my MySQL query:

SELECT * FROM author AS a
WHERE 
(SELECT COUNT(b.id)
    FROM book AS b
    WHERE b.author_id = a.id
    AND b.releaseDate > '2014-01-01'
) = 0

How can I convert it to a Hibernate/Grails criteria?

Edit:

  • More explicit: I need the equivalent Grails criteria.
  • I have many book and author records. Gorm query must be high performance.
Solution

  • I resolved, but not using criteria. I used HQL:

    def authors = Author.findAll("\
SELECT * FROM author AS a \
WHERE \
(SELECT COUNT(b.id) \
    FROM a.books AS b \
    WHERE b.releaseDate > '2014-01-01' \
) = 0")