Grails namedQuery sort order by multiple columns

Question

Given a namedQuery:

class MyDomainObject {

    String someProperty

    static namedQueries = {

        myNamedQuery {
            // some implementation here
        }

    }
}

I can use it to generate a list, sorted by a single key, like this (documentation for 2.4.3 here):

def resultsList = MyDomainObject.myNamedQuery.list(sort: "someProperty", order: "desc")

How do I order the results by multiple columns? I'd like to be able to define the sort parameters dynamically, not define them in the query.

I see here [here](http://grails.github.io/grails-doc/3.0.x/ref/Domain%20Classes/namedQueries.html) that named queries support the criteria builder syntax. Have a look at [this question](http://stackoverflow.com/questions/326053/how-to-order-by-more-than-one-field-in-grails) and [this question](http://stackoverflow.com/questions/16332369/grails-sort-by-two-fields-in-a-query). — Daddy Pumpkin, Apr 15 '15 at 10:36
@DaddyPumpkin those seem to incorporate the sort parameters into the named query. I want to execute the sorting to be independent of the named query. — John, Apr 15 '15 at 12:01
[This](http://stackoverflow.com/questions/4882992/grails-mapping-sort-on-multiple-fields-groovy-sort-on-multiple-map-entries) is what you need then. — Daddy Pumpkin, Apr 15 '15 at 12:51
I can see what you're trying to do there, but it's not the same thing as what I need. The query is executed within the (remote) database, whereas the list.sort() executes on the Grails server. They're not the same machine. I think I've got the basis of a solution which I'm trying to get working - it involves concatenating queries and then calling list(). — John, Apr 15 '15 at 13:45

score 0 · Answer 1 · answered Apr 15 '15 at 19:29

I'm sure there's a better way, but I ended up creating another named query that I can concatenate onto my chosen one (I could always incorporate into the original query too).

// expects to be passed a List containing a series of Maps
orderByMultipleColumns { List columnsToSortBy ->
    columnsToSortBy.each { Map field ->
        order("${field.fieldName}", field.fieldOrder)
    }
}

// usage:
List orderByList = []
    // ...
    // within some loop that I use:
    orderByList << [fieldName: someValue, fieldOrder: dir] // dir == 'asc' or 'desc'
    // ...

MyDomainObject.myNamedQuery().orderByMultipleColumns(orderList).listDistinct(max: length, offset: start)

Grails namedQuery sort order by multiple columns

1 Answers1