CODEWITHSUNDEEP
grailspagination
genonymous
genonymous

Reputation: 1750

In Grails, how to get the total result count when we are passing max and offset values to the list method?

If I understand correctly, we can retrieve the desired number of specific results for pagination by passing the max and offset values to the list method. But, in many cases, we also want to show the total number of results returned by the SQL query. As per my understanding, with the offset and max values passed to the list method, the SQL query still returns all the results internally but the results are trimmed according to the max and offset values. Is there any way to get the total number of results returned before trimming occurs?

Upvotes: 2

Views: 3103

Answers (3)

WATCHARIN
WATCHARIN

Reputation: 11

Grails Criteria Query and pagination params

params.max = params?.max as Integer ?: 10
params.offset = params?.offset as Integer ?: 0
params.sort = params?.sort ?: "email"
params.order = params?.order ?: "asc"
params.filter = params?.filter ?: ""
params.packet = params?.packet ?: ""

def members = Member.createCriteria().list(params)
  {
    or
    {
      if(params.filter != ""){
        ilike("firstName", "%" + params.filter + "%")
        ilike("lastName", "%" + params.filter + "%")
        ilike("email", "%" + params.filter + "%")
        try {
          params.filter as Long
          eq("citizenId" , params.filter.toLong())
        }catch (e) {

        }
        ilike("mobile", "%" + params.filter + "%")
      }
    }
  }

def dataMembers = [:]
dataMembers.data = members
dataMembers.totalRecord = members.totalCount
render dataMembers as JSON

Output

{
"data": [
    {
      "id":1,
      "firstName":name
    },
    {
      "id":2,
      "firstName":name
    }
  ],
"totalRecord":5
}

Upvotes: 1

Burt Beckwith
Burt Beckwith

Reputation: 75671

You do not understand correctly :)

It would be crazy to retrieve all of the records to count them and filter out the ones that shouldn't have been returned at the client. It wouldn't be that bad with a few rows, but consider the performance implications of an approach like that with tables that have millions or billions of rows.

Instead, you have to execute two queries. One to retrieve the rows that you want, doing the filtering in the database, and another with the same where clause restrictions but without the max and offset values, and using select count ....

As @sudhir points out in his answer, Grails will return a custom List implementation (PagedResultList) which includes the results for the current page and has a getTotalCount method to return the result from the count query.

The implementation of the list method is here and you can see in the PagedResultList source that the count query is lazily run only when you call getTotalCount.

Upvotes: 5

Sudhir N
Sudhir N

Reputation: 4096

When max is specified, list() method returns PagedResultList which has a totalCount property

def list = DomainClass.list(params) { }
log.debug "Total records: $list.totalCount"    
render view:'list', model: [list:list, totalRecords:list.totalCount]

Upvotes: 4

Related Questions