Trying to get Hibernate to generate a subquery instead of inner joins (using Grails)

This question is sort of a follow-up to the question I posted here: Problem with duplicates using Grails' withCriteria function with a many-to-many relationship

I'm using the same 3 domain classes defined there, with the addition of the following class:

class Genre {
    int id
    String name
}

and the added genre property on the Question class:

class Question {
    int id
    int text
    Genre genre

    static hasMany = [questionAnswers : QuestionAnswer]
}

I've tried 2 queries:

def criteria = Answer.createCriteria()
def listing = criteria.listDistinct() {
    cache false
    order "id", "asc"
    maxResults(10)

    questionAnswers {
        question {
            genre {
                eq("id", myGenreID)
            }
        }
    }
}

and

def criteria2 = Answer.createCriteria()
def listing2 = criteria2.listDistinct() {
    join "questionAnswers"
    join "questionAnswers.question"
    join "questionAnswers.question.genre"
    createAlias "questionAnswers", "qa"
    createAlias "qa.question", "q"
    createAlias "q.genre", "g"
    cache false
    order "id", "asc"
    maxResults(10)

    eq("g.id", myGenreID)
}

Both queries generate essentially the same SQL, except the first generates left outer joins, and the second generates inner joins. Here is the 2nd query's output (simplified with "select *"):

select *
from answer a
inner join question_answer qa on a.id = qa.answer_id 
inner join question q on qa.question_id = q.id 
inner join genre g on q.genre_id = g.id 
where g.id = 1 
order by a.id asc
limit 10;

T开发者_如何学Che SQL above has the potential to return duplicate Answer rows, which I don't want, so I use the listDistinct function on the criteriaQuery (an alternative to the resultTransformer solution I accepted on the referenced question).

Here's the problem: the maxResults function is applied BEFORE the listDistinct function, so if there are any duplicate answer rows I always end up with less than 10 results.

I really need the generated SQL to look more like this:

select *
from answer a
where a.id in (select distinct qa.answer_id
               from question_answer qa
               inner join question q on qa.question_id = q.id 
               inner join genre g on q.genre_id = g.id 
               where g.id = 1)
order by a.id asc
limit 10;

Is there any way to turn the question_answer lookup into a subquery like in the above SQL?

(My apologies if the solution is very simple. If it wasn't painfully obvious from my questions, I'm not terribly amazing at hibernate.)

Any help/suggestions are much appreciated.

Thanks,

B.J.

I don't know grails, but in Hibernate I think what you're looking for falls under the purview of fetching strategies. One way of setting this is, for example, with the @OneToMany.fetch attribute. You can also set a fetching strategy in queries as shown in this example from that same chapter of the reference guide:

User user = (User) session.createCriteria(User.class)
    .setFetchMode("permissions", FetchMode.JOIN)
    .add( Restrictions.idEq(userId) )
    .uniqueResult();

I hope that helps point you in the right direction.