GORM criteria, pagination headache

Discussion:

tinny

2010-03-08 03:26:03 UTC

Im stuck in a recursive loop on this one and I just need a gentle push in the
right direction please.

I have a list type page that's collection of elements to be displayed is
built using GORM's criteria builder.

E.g.

def criteria = Connection.createCriteria()
def connectionTypes = [ConnectionType.ELECTRICITY, ConnectionType.GAS]
...

def connectionInstanceList = criteria {
and {
if(params.referenceNumber && params.referenceNumber != '') {
like("referenceNumber", params.referenceNumber)
}
if(params.customerFirstName && params.customerFirstName != '') {
like("customerFirstName", params.customerFirstName)
}
if(params.customerSurname && params.customerSurname != '') {
like("customerSurname", params.customerSurname)
}
if(params.siteAddress && params.siteAddress != '') {
like("streetAddress", params.siteAddress)
}
if(params.initiator && params.initiator != '') {
initiator {
like("businessName", params.initiator)
}
}
'in'("type", connectionTypes)
firstResult(params.offset)
maxResults(params.max)
}

}

I would obviously like to use the g:paginate tag to page through all of the
possible results. The only thing I cant figure out right now is how to get
the total count for this query (not limited with "firstResult" and
"maxResults") in a clean way. I mean I could cut and paste 90% of the above
query and insert a rowCount projection or something like that, but it just
feels wrong to have to perform these two slightly different queries twice
just to get a total count.

What do you think? Any feedback welcome.

Thanks

--
View this message in context: http://n4.nabble.com/GORM-criteria-pagination-headache-tp1584073p1584073.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

Jeff Brown

2010-03-08 03:57:12 UTC

Permalink

Post by tinny
Im stuck in a recursive loop on this one and I just need a gentle push in the
right direction please.
I have a list type page that's collection of elements to be displayed is
built using GORM's criteria builder.
E.g.
def criteria = Connection.createCriteria()
def connectionTypes = [ConnectionType.ELECTRICITY, ConnectionType.GAS]
...
def connectionInstanceList = criteria {
and {
if(params.referenceNumber && params.referenceNumber != '') {
like("referenceNumber", params.referenceNumber)
}
if(params.customerFirstName && params.customerFirstName != '') {
like("customerFirstName", params.customerFirstName)
}
if(params.customerSurname && params.customerSurname != '') {
like("customerSurname", params.customerSurname)
}
if(params.siteAddress && params.siteAddress != '') {
like("streetAddress", params.siteAddress)
}
if(params.initiator && params.initiator != '') {
initiator {
like("businessName", params.initiator)
}
}
'in'("type", connectionTypes)
firstResult(params.offset)
maxResults(params.max)
}
}
I would obviously like to use the g:paginate tag to page through all of the
possible results. The only thing I cant figure out right now is how to get
the total count for this query (not limited with "firstResult" and
"maxResults") in a clean way. I mean I could cut and paste 90% of the above
query and insert a rowCount projection or something like that, but it just
feels wrong to have to perform these two slightly different queries twice
just to get a total count.
What do you think? Any feedback welcome.
Thanks

Does something like this get you what you want?...

def search = {
params.max = Math.min(params.max ? params.int('max') : 10, 100)

def propNames = ['referenceNumber', 'customerFirstName',
'siteAddress', 'customerSurname']
def connectionTypes = [ConnectionTypes.ELECTRICITY, ConnectionTypes.GAS]

def connections = Connection.createCriteria().list(max:
params.max, offset: params.offset ?: 0) {
propNames.each { propName ->
if(params[propName]) {
like propName, "%${params[propName]}%"
}
}
inList 'type', connectionTypes
}
def cnt = connections.totalCount

render view: 'list', model: [connectionInstanceList: connections,
connectionInstanceTotal: cnt]
}

jb
--
Jeff Brown
SpringSource
http://www.springsource.com/

Autism Strikes 1 in 166
Find The Cause ~ Find The Cure
http://www.autismspeaks.org/

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

freak182

2011-02-10 02:57:08 UTC

Permalink

Hi,

I have similar problem:

def purchaseBatchSearch(params,max,offset) {
params['max'] = max
params['offset'] = offset
def purchaseBatch = PurchaseBatch.createCriteria()
def results = commonQuery(purchaseBatch,params,true)
return constractDisplay(results)
}

def countPurchaseBatchSearch(params) {
def purchaseBatch = PurchaseBatch.createCriteria()
return commonQuery(purchaseBatch,params,false)
}

def commonQuery(purchaseBatch,params,page)
{
return purchaseBatch {
loans {

// search query here
}

if(page) {
firstResult(params.offset)
maxResults(params.max)
} else {
projections {
rowCount()
}
}

}

when executing the count part was fine:

select
count(*) as y0_
from
nlp_purchase_batch this_
left outer join
............

but it give an exception:

ERROR errors.GrailsExceptionResolver - No signature of method:
java.util.ArrayList.div() is applicable for argument types:
(java.lang.Integer) values: [10]
Possible solutions: min(), get(int), get(int), min(groovy.lang.Closure),
is(java.lang.Object), min(java.util.Comparator)
groovy.lang.MissingMethodException: No signature of method:
java.util.ArrayList.div() is applicable for argument types:
(java.lang.Integer) values: [10]

...i should expecting an integer return from the count query, but return as
a List. what could go wrong?

grails 1.3.4

Cheers.
-Eman

--
View this message in context: http://grails.1312388.n4.nabble.com/GORM-criteria-pagination-headache-tp1584073p3298507.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

Eyck Jentzsch

2010-03-08 05:57:55 UTC

Permalink

You need to use a second criteria using the count projection and
omitting the firstrResult/maxResult:

def connectionInstanceList = criteria {
and {
if(params.referenceNumber && params.referenceNumber != '') {
like("referenceNumber", params.referenceNumber)
}
if(params.customerFirstName && params.customerFirstName != '') {
like("customerFirstName", params.customerFirstName)
}
if(params.customerSurname && params.customerSurname != '') {
like("customerSurname", params.customerSurname)
}
if(params.siteAddress && params.siteAddress != '') {
like("streetAddress", params.siteAddress)
}
if(params.initiator && params.initiator != '') {
initiator {
like("businessName", params.initiator)
}
}
'in'("type", connectionTypes)
}
projections {
rowCount()
}
}

this gives you the number of row which you need to pass then into your
view to be used with the paginate tag.
-Eyck

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

Niels Stargardt

2010-03-08 08:15:19 UTC

Permalink

I think
connectionInstanceList.totalCount
is your friend.

Niels

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

Bhagwat Kumar

2010-03-08 09:47:45 UTC

Permalink

Hi,

createCriteria.list{ //.................//} returns result of type
ArrayList.

However passing pagination params to list e.g.
createCriteria.list(max:10, offset:0){//.............//
}
returns result of type PagedResultSet which has methods like getTotalCount()
and returns the actual number of results returned irrespective of the
pagination params. Also the resultSet returned is paginated.

Have a look here for further clarification :

http://www.pubbs.net/grails/200912/2269/
http://www.grails.org/doc/1.0.x/api/grails/orm/PagedResultList.html
http://jira.codehaus.org/browse/GRAILS-2672

Post by Niels Stargardt

Post by tinny
Im stuck in a recursive loop on this one and I just need a gentle push in the
right direction please.
I have a list type page that's collection of elements to be displayed is
built using GORM's criteria builder.
E.g.
def criteria = Connection.createCriteria()
def connectionTypes = [ConnectionType.ELECTRICITY,
ConnectionType.GAS]
...
def connectionInstanceList = criteria {
and {
if(params.referenceNumber &&
params.referenceNumber != '') {
like("referenceNumber",
params.referenceNumber)
}
if(params.customerFirstName &&
params.customerFirstName != '') {
like("customerFirstName",
params.customerFirstName)
}
if(params.customerSurname &&
params.customerSurname != '') {
like("customerSurname",
params.customerSurname)
}
if(params.siteAddress &&
params.siteAddress != '') {
like("streetAddress", params.siteAddress)
}
if(params.initiator &&
params.initiator != '') {
initiator {
like("businessName", params.initiator)
}
}
'in'("type", connectionTypes)
firstResult(params.offset)
maxResults(params.max)
}
}
I would obviously like to use the g:paginate tag to page through all of the
possible results. The only thing I cant figure out right now is how to get
the total count for this query (not limited with "firstResult" and
"maxResults") in a clean way. I mean I could cut and paste 90% of the above
query and insert a rowCount projection or something like that, but it just
feels wrong to have to perform these two slightly different queries twice
just to get a total count.
What do you think? Any feedback welcome.
Thanks
I think

connectionInstanceList.totalCount
is your friend.
Niels
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

--
With Regards,
Bhagwat Kumar
Intelligrape Software Pvt. Ltd.

http://www.IntelliGrape.com
http://www.linkedin.com/in/bhagwatkumar

tinny

2010-03-09 01:55:00 UTC

Permalink

Thanks for the replies everyone.

Passing the pagination params to the list function is the way to go.

e.g.

.list(max:params.max, offset:params.offset ?: 0)

then result.totalCount

This is the approach I was looking for, I new it had to be simple!!

Thanks!

--
View this message in context: http://n4.nabble.com/GORM-criteria-pagination-headache-tp1584073p1585464.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email