Grails eager fetch with scrolling

I am trying to scroll through a result set of a namedQuery. To avoid N=1 problem i set fetch param to eager. I am also interested in distinct set of top level objects. So my controller closure looks like this:

def list = {
        params.max = Math.min(params.max ? params.int('max') : 25, 50)
        params.fetch=[tasks:"eager"]
        def requests =    Request.activeOnly.open.listDistinct(params)
...
}

The problem I see is that the query returns less th开发者_运维百科an 25 Request objects. Instead it returns 25 rows of a joint result set, which is not what I want. How do I restructure it? By the way "select" fetch works great but results in 26 queries. Thanks, Dmitry.

You can't apply max results to a query which performs a join fetch on a collection, because the max is applied to the number of rows returned in the result set, and not to the number of entities.

What I do, in this case, is a first query that only fetches the IDs of the root entity (Request), and then a second query which selects the entities with their fetched collection, using an IN clause:

// first query, with max results applied: returns 25 IDs
select request.id from Request request where ...

// second request:
select request from Request request 
left join fetch request.tasks 
where request.id in (:setOfIdsReturnedByTheFirstQuery)