GORM: Query having subquery empty

0
votes

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.
2
mysqlhibernategrailsgrails-orm

2 Answers

0
votes

Something like this should work for you; it may be a little slow due to the sorting, however if your number of books is relatively small, it shouldn't have a huge impact. 

def targetDate = // define your date here.
def lazyAuthors = Author.findAll{
   books.sort{it.requestDate}[books.size()-1] > targetDate
}

**EDIT** It turns out you can't query transients so my original thought would not work, however the technique I used to get the latest book should work.

0
votes

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