How to order by more than one field in Grails?

Question

Is there a way to get a list ordered by two fields, say last and first names?

I know .listOrderByLastAndFirst and .list(sort:'last, first') won't work.

score 83 · Answer 1 · edited Oct 16 '17 at 13:48

83

Hates_ criteria answer didn't seem to work for me; putting "last,first" in order will only cause exceptions saying, "Property 'last,first' not found". To order on two fields, you can do the following:

def c = MyDomain.createCriteria()
def results = c.list {
    and{
       order('last','desc')
       order('first','desc')
    }
}

edited Oct 16 '17 at 13:48

Stefan van den Akker

6,661
7
48
63

answered Oct 27 '09 at 21:12

mattlary

999
6
8

1

is the 'and' block necessary here? – Akusete Sep 12 '11 at 22:44
1

@Matt Lachman: Interesting, I thought there was an implicit `and` around a criteria. Can you explain what happens without the `and`? – Akusete Oct 28 '11 at 00:35
Perhaps I was a bit too hasty. I retract my comment: http://grails.1312388.n4.nabble.com/Criteria-Restrictions-question-AND-or-OR-td1351000.html – Matt Lachman Nov 02 '11 at 17:32
2

It works the same without the outer `and` block in Grails 2.3.7 – James McMahon Jun 02 '14 at 00:29

score 11 · Answer 2 · answered Sep 02 '11 at 00:45

11

This is quite old but helped me in finding a suitable solution. A "cleaner" code example by using withCriteria shortcut:

def c = MyDomain.withCriteria {
    and {
        order('last', 'desc')
        order('first', 'desc')
    }
}

answered Sep 02 '11 at 00:45

Arnar B

111
1
2

So far, this is definitely the cleanest solution. I don't expect we can do any better. – Jarek Przygódzki Dec 31 '11 at 18:23

score 8 · Accepted Answer · edited Jul 12 '18 at 04:55

8

This old solution no longer works. Please see mattlary's answer below

You may have to write a custom finder in HQL or use the Criteria Builder.

MyDomain.find("from Domain as d order by last,first desc")

Or

def c = MyDomain.createCriteria()
def results = c.list {
       order("last,first", "desc")
}

edited Jul 12 '18 at 04:55

doelleri

19,232
5
61
65

answered Nov 28 '08 at 16:17

Hates_

66,613
6
32
37

Not sure why it has been marked negative but the first approach Mydomain.find definitely works. probably he has some typo in the above but that is no reason for minus. +1 from me. – saurabh Dec 17 '12 at 12:39
9

Worked 5 years ago ;) Shame I can't delete it. – Hates_ Jan 04 '13 at 23:11
5

Oh how I dislike people downvoting something that used to work. – miek Jan 22 '13 at 13:41
org.hibernate.QueryException: could not resolve property: last,first of: MyDomain at grails.orm.HibernateCriteriaBuilder.invokeMethod(HibernateCriteriaBuilder.java:1616) – Abdennour TOUMI Oct 09 '13 at 08:50

score 6 · Answer 4 · answered May 04 '18 at 11:39

6

MyDomain.findAll(sort: ['first': 'desc','last':'desc'])

works with grails-datastore-gorm:6.0.3

answered May 04 '18 at 11:39

gerrit-hntschl

61
1
1

This solution worked for me with Grails 4.0.3 and GORM 7.0.4. You can also do something like `Person.findByUsername(username, [sort: ['lastName': 'asc', 'firstName': 'asc']])` – jnunderwood Dec 31 '20 at 19:31

score 5 · Answer 5 · answered Sep 12 '12 at 12:37

5

More complicated ordering criteria, (tested in Grails 2.1.0)

def c = MyDomain.withCriteria {
    property {
        order('last', 'desc')
    }
    order('first', 'desc')
}

sorts first by MyDomain.property.last then by MyDomain.first

answered Sep 12 '12 at 12:37

chim

8,407
3
52
60

score 5 · Answer 6 · edited May 29 '14 at 05:38

5

you can do this

def results=MyDomain.findAll([sort:"last",order:'desc'],[sort:"first",order:'desc']);

this line of code will first sort results from domain class "MyDomain" first by last name and then by first name of the person .

edited May 29 '14 at 05:38

Afzaal Ahmad Zeeshan

15,669
12
55
103

answered May 29 '14 at 04:45

maq

51
1
2

score 4 · Answer 7 · edited Sep 03 '11 at 00:37

4

This query is working on the basis of first field. When the first field is blank then it is shorted by the second field.

order('last','desc')
order('first','desc')

edited Sep 03 '11 at 00:37

mcabral

3,524
1
25
42

answered Nov 04 '09 at 05:06

Nakul

41
1

score 4 · Answer 8 · answered Nov 30 '08 at 13:33

4

I think a criteria is the best bet, but you did the right thing by attempting a finder first. When retrieving domain objects from GORM, the right order to make the attempt is: dynamic finder, criteria, HQL.

answered Nov 30 '08 at 13:33

Matthew Taylor

3,911
4
29
33

score 0 · Answer 9 · answered Nov 28 '08 at 16:17

If you were sorting lists on the contents of their items, you would need to implement a comparator which would have some smarts to enable to you decide the sort order based on multiple properties.

Some examples of Groovy-style comparators are shown here

However if the list you are sorting is being returned from a database query, you would be better off sorting it using a CrteriaQuery and sorts on that

score 0 · Answer 10 · answered Jul 05 '18 at 15:13

I has the same problem. Since my list is not so big, I use groovy sort, as I want to sort on fields of linked domain: CalendarData -> Attraction

def listCalendar (Calendar calendar) {
    respond CalendarData.where {
        calendar == calendar
    }.list().sort{ "$it.attraction.type?:' '$it.attraction.name" }
}

How to order by more than one field in Grails?

10 Answers10

Linked