Re: Criteria Query How-To

I have a variation of this conundrum. Here is my code executing in the console,

groovy> import org.hibernate.criterion.CriteriaSpecification
groovy> def criteria = Sample.createCriteria()
groovy> def dataList = criteria.listDistinct {
groovy>        and {
groovy>              results {
groovy>                ne ('condition', 'suspect')
groovy>             }        
groovy>           }
groovy>             order("id", "asc")
groovy>             maxResults(15)      
groovy>          }
groovy> dataList.size()

Result: 3

The problem I have is that only three records are returned when 83 records match this criteria. If I set maxResults higher you can see the result is that i get all 83 records back,

groovy> import org.hibernate.criterion.CriteriaSpecification
groovy> def criteria = Sample.createCriteria()
groovy> def dataList = criteria.listDistinct {
groovy>        and {
groovy>              results {
groovy>                ne ('condition', 'suspect')
groovy>             }        
groovy>           }
groovy>             order("id", "asc")
groovy>             maxResults(999)      
groovy>          }
groovy> dataList.size()

Result: 83

However, to work with pagination I'd like (expect?) the first code example to return 15 of the 83 Sample records instead of just 3.

Unfortunately this is just the first half of the mess I have created for myself. The second part relates to trying to get back an instance of grails.orm.PagedResultList for my pagination needs. I have two further code examples. In this first one, I get back a paged list, but the distinct projection is ignored and I get back 951 records from a database contained just 83 :(

groovy> import org.hibernate.criterion.CriteriaSpecification
groovy> def criteria = Sample.createCriteria()
groovy> def dataList = criteria.list (max: 999, offset: 0) {
groovy>        and {
groovy>              results {
groovy>                ne ('condition', 'suspect')
groovy>                projections {  
groovy>                  distinct("id")
groovy>                }
groovy>              }            
groovy>           }
groovy>             order("id", "asc")  
groovy>          }
groovy> println dataList.size()
groovy> println dataList.class.name

951
grails.orm.PagedResultList

When I remove (max: 999, offset: 0) the distimct is honored but I cannot figure out how to get back a PagesResultSet,

groovy> import org.hibernate.criterion.CriteriaSpecification
groovy> def criteria = Sample.createCriteria()
groovy> def dataList = criteria.list {
groovy>        and {
groovy>              results {
groovy>                ne ('condition', 'suspect')
groovy>                projections {  
groovy>                  distinct("id")
groovy>                }
groovy>              }            
groovy>           }
groovy>             order("id", "asc")  
groovy>          }
groovy> println dataList.size()
groovy> println dataList.class.name

83
java.util.ArrayList


I've tried so many variants of the Criteria to get this to work my head is spinning (CriteriaSpecification, projections, list, listDistinct, firstResults, maxResults etc etc ) so any insight offered would be most appreciated.