grails Pagination using iterative gorm querys aggregation

Question

I have this controller.method rendering all things associated to a profile with the correct offset and max for the pagination in the gsp

    if (profil) {
        def max = params.max ? params.max as int : 5
        def offset = params.offset ? params.offset as int : 0
        List things = []

        things = Things.findAllByProfil(profil,[max: max, offset: offset, sort: 'dtCreated', order: 'desc'])
        [profil: profil, things: things, thingsCount: things.size()]
    }

but now i´m having a special "aggregating-Profile" wich has some profiles associated and i want to put all things of all associated profiles in a list like:

    if (profil) {
        def max = params.max ? params.max as int : 5
        def offset = params.offset ? params.offset as int : 0
        List things = []

        if(profil.typ == 'Aggregate'){
            List profiles = profil.profiles?.collect { Profil.collection.findOne(_id:it.id) }
            profiles.each() { pr ->
                if(pr) {
                    things+= Things.findAllByProfil(pr as Profil,[max: max,offset: offset,  sort: 'dtCreated', order: 'desc']) 
                }
            } 
            things.sort{a,b-> b.dtCreated<=>a.dtCreated}
        }else{

            things = Things.findAllByProfil(profil,[max: max, offset: offset, sort: 'dtCreated', order: 'desc'])
        }
        [profil: profil, things: things, thingsCount: things.size()]
    }

but this way i´m using the offset and max several times, for each associated profile, so the resulting list is too big.

unfortunetly the result-design should stay the same, so assert params.max == 5 && profil.typ == "Aggregate", the first-page´s result is a list with the 5 most recent things of all profiles ( as i put them all in a list and sort them by the dtCreated) and my problem maybe : how to apply the same slicing logic to the aggregated list(and how to aggregate the things in a performant way)

What would be the best approach to solve this problem ?

for any hint thanks in advance

Answer 1

First I'd like to extend on what rcgeorge23 has stated in that you really are better off letting the gorm/database handle your aggregation. Section 6 of the Grails documentation will take you a long way.

In your particular case, you can simply use a built-in comparator with your `findAllBy to handle your aggregation, here's a concrete example:

if(profil.typ == 'Aggregate'){
  List profiles = //some code to get a list of profiles
  def things = Things.findAllByProfilInList(profiles, [max: max....order: 'desc'])
} else {
  ...
}    

Second, your use of pagination is a little off. thingsCount should technically be a simple "select count" matching your criteria, but you're returning matching criteria limited by "max". So use the following for thingsCount

def thingsCount = Things.countByProfilInList(profiles) //for all things with aProfile in list
 or
def thingsCount = Things.countByProfil(aProfile) //for all things with aProfile