CODEWITHSUNDEEP
grails
vivek
vivek

Reputation: 65

Grails Pagination: how to use pagination to restrict the number of rows

I have a query which gives a result of about 100 rows. Here is the code for the controllers

def bandsOneTrack = {
    def bands = Band.executeQuery("Select b from Band as b where size(b.tracks) > (select count(t.id) from Band as ba join ba.tracks as t where ba.id = b.id and t.ourContract is not null) and (select count(t.id) from Band as ba join ba.tracks as t where ba.id = b.id and t.ourContract is not null) >= 1" )
    render(view: 'bands_list' , model: [ bands : bands ])
}

It gives me the result set of about 100 rows but they are appearing inside a same page. Now I want to use pagination so that I can restrict it to only 20 rows per page. What should I do, and also how to use pagination for this.

Upvotes: 2

Views: 10469

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: 0

Alidad
Alidad

Reputation: 5538

On your pagination tag check the total parameter. That should be the total number of records. In your case 100 so that the pagination tag can calculate the total number of pages.

Something like this here:

<g:paginate controller="Book" action="list" total="${bookInstanceTotal}" />

You might need to execute your query once to find the total number of records.

def list() {
    params.max = Math.min(params.max ? params.int('max') : 10, 100)
    def ls = Book.executeQuery("from Book a",[max: params.max, offset: params.offset])
    def totalCount = Book.executeQuery("from Book a").size()
    [bookInstanceList: ls, bookInstanceTotal: totalCount]
}

Upvotes: 7

Aquatoad
Aquatoad

Reputation: 788

If I remember correctly, you add max and offset properties to the model you're passing to your view, it should automatically wire in pagination. At that point, you should be able to use the paginate tag to iterate through your result sets. See the docs here:

http://grails.org/doc/latest/ref/Tags/paginate.html

Upvotes: 0

Related Questions