Criteria queries + associations = confusion

Since we've been bitten by this problem twice in the same week now I thought it was worth a post.

Consider these classes:

    class Author {
        String name
        static hasMany = [books: Book]
    }

    class Book {
        String name
        static belongsTo = [author: Author]
    }

Then let's attempt to find all authors who have written a book with a particular name:

    Author wg = new Author(name: 'William Gibson')
    wg.addToBooks new Book(name: 'Pattern Recognition')
    wg.addToBooks new Book(name: 'Spook Country')
    assert wg.save(flush: true)

    sessionFactory.currentSession.clear()

    List authors = Author.withCriteria {
        books {
            eq('name', 'Spook Country')
        }
    }
    assertEquals 1, authors.size()
    assertEquals 2, authors[0].books.size() // FAILS result is 1

When you look at how Hibernate implements this query under the hood it's reasonably clear what's going on. It will be doing something roughly along the lines of:

    select * from author a, book b where a.id = b.author_id and b.name = ?

The problem is it then makes the mistake of thinking that the books collections of the author objects it has found can be initialised based on that result set.

Where this problem has bitten us is when objects fetched by such a query are also used by an unrelated bit of code that doesn't bother reading from the database as it quite reasonably thinks (some of) the objects is needs are in the Hibernate session already. Unfortunately, the view module related to this other bit of code is reliant on the elements of the collection that weren't loaded by our defective query. I'm sure you can imagine the fun to be had debugging that kind of problem.

I've been hacking around with the criteria attempting various permutations such as:

    // DOES NOT WORK
    List authors = Author.withCriteria {
        'in'('books.name', 'Spook Country')
    }

and

    // ALSO DOES NOT WORK
    List authors = Author.withCriteria {
        'in'('books') {
            eq('name', 'Spook Country')
        }
    }

But I've got nowhere yet. The best I've managed to do is:

    List authors = Author.withCriteria {
        books {
            eq('name', 'Spook Country')
        }
    }
    // if you have a weak stomach look away now
    authors*.refresh()

If you're wondering, yes that is going to execute n+1 selects.

Another option is to give up and use an HQL query.

This problem applies equally to one-to-many (and presumably many-to-many) associations using Set, List or Map.