Grails Paging Results are Inconsistent

Question

I'm doing some paging in my Grails application. Since I'm doing some special sorting, I can't use the convient sort/paging methods. Here is my paging code on the server side:

def criteria = ClientContact.createCriteria().createAlias("client", "c")
criteria.setFirstResult(params.offset?.toInteger())
criteria.setMaxResults(params.max?.toInteger())
def sortField = params.sort
if(params.sort == "clientName") sortField = "c.name"
criteria.addOrder(params.order == "asc" ? Order.asc(sortField) : Order.desc(sortField))
   /*
   if(!StringUtils.isBlank(params.searchField))
   {
    criteria.add(
     Restrictions.or (
      Restrictions.ilike("c.name", "%" + params.searchField + "%"),
      Restrictions.or(
       Restrictions.ilike("firstName", "%" + params.searchField + "%"),
       Restrictions.ilike("lastName", "%" + params.searchField + "%")
      )
     )
    )
   }
       */

def results = criteria.list()

You'll notice the section of code commented-out. Typically, it would be uncommented so I can do my special sorting, but it's commented-out to help reduce the number of variables causing this problem.

When I look at the 4th page of my results, I see a certain list of people. When I reload the 4th page of results, I see a slightly different list -shifted by 8 people.

Does anyone have any idea why I would be receiving inconsistent results? I would expect to get the same 100 people every time I view the 4th page of my results (If you haven't guess, I'm showing people's names). The 'offset,' 'max,' and 'sort' values are identical across both requests.

Any help is appreciated. Thanks,

Andrew

Update....

Here is the more straightforward approach that should work, except I receive an exception. If I remove the 'createAlias' line, I don't receive the exception. Unfortunately, I need the alias to join to an associated table.

   if(params.sort == 'client') params.sort = 'c.name'
   def criteria = ClientContact.createCriteria()
   criteria.createAlias('client', 'c')
   def pagedResults = criteria.list(offset: params.offset, max: params.max, sort: params.sort, order: params.order ?: 'asc' ) {
     if(!StringUtils.isBlank(params.searchField)) {
       or {
           ilike "c.name", "%$params.searchField%"
           ilike "firstName", "%$params.searchField%"
           ilike "lastName", "%$params.searchField%"
       }
     }
   }

Exception:

Caused by: java.lang.IllegalArgumentException: call to [list] not supported here

at grails.orm.HibernateCriteriaBuilder.invokeMethod(HibernateCriteriaBuilder.java:847)

at ClientContactController$_closure21_closure39_closure44.doCall(ClientContactController:494)

Answer 1

I don't have an idea why the sorting is inconsistent across different request, but I'd like to show you the "default" grails approach for your code:

if(params.sort == 'clientName') params.sort = 'c.name'
def criteria = ClientContact.createCriteria()
criteria.createAlias('client', 'c')
def results = criteria.list( sort: params.sort, order: params.order ?: 'asc' ) {
    if(!StringUtils.isBlank(params.searchField)) {
     or {
      ilike "c.name", "%$params.searchField%"
      ilike "firstName", "%$params.searchField%"
      ilike "lastName", "%$params.searchField%"
     }
    }
}

the results variable is then an instance of PagedResultList, which is basically a normal list, but has a "totalCount" property to ease the paging.