Issue Details (XML | Word | Printable)

Key: GRAILS-3045
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Graeme Rocher
Reporter: Keith Thomas
Votes: 4
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Grails

Database searching with HibernateCriteriaBuilder has pagination and distinct results coded to be mutually exclusive

Created: 02/Jun/08 06:09 PM   Updated: 14/May/09 03:11 AM
Component/s: Persistence
Affects Version/s: 1.0.3
Fix Version/s: 1.1.2

Time Tracking:
Not Specified

Environment: OS X 10.5.3, J2SE 1.5.0_13-b05-237, Oracle 10.g Release 2


 Description  « Hide
The code snippet below means that Criteria queries through the HibernateCriteriaBuilder unable to support requests for distinct records to be returned in the same list() query as requests for pagination. This is at least as much an issue with Criteria as it is with the builder, however this seems like an area where the builder could (should?) be easier to use than directly using Criteria.

Code snippet from HibernateCriteriaBuilder:

HibernateCriteriaBuilder.java
} else if(paginationEnabledList) {
    // Calculate how many results there are in total. This has been
    // moved to before the 'list()' invocation to avoid any "ORDER
    // BY" clause added by 'populateArgumentsForCriteria()', otherwise
    // an exception is thrown for non-string sort fields (GRAILS-2690).
    this.criteria.setFirstResult(0);
    this.criteria.setMaxResults(Integer.MAX_VALUE);
    this.criteria.setProjection(Projections.rowCount());
    int totalCount = ((Integer)this.criteria.uniqueResult()).intValue();

    // Drop the projection, add settings for the pagination parameters,
    // and then execute the query.
    this.criteria.setProjection(null);
    this.criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
    GrailsHibernateUtil.populateArgumentsForCriteria(this.criteria, (Map)args[0]);
    PagedResultList pagedRes = new PagedResultList(this.criteria.list());

    // Updated the paged results with the total number of records
    // calculated previously.
    pagedRes.setTotalCount(totalCount);
    result = pagedRes;
  }

The following link has a suggested solution:

http://floledermann.blogspot.com/2007/10/solving-hibernate-criterias-distinct.html

The following mailing list posting has examples of the problem,

http://www.nabble.com/Criteria-Query-How-To-to17352317.html#a17516093



 All   Comments   Work Log   Change History      Sort Order: Ascending order - Click to sort in descending order
Jeff Brown added a comment - 02/Jun/08 06:31 PM
added code tags to code sample to make it easier to look at