How to sort by multiple properties in Spring Data (JPA) derived queries?

Question

I'm looking at the examples giving on this page (https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.repositories) about method naming, is it possible to create a complex chain method name such as

findByProgrammeAndDirectorAndProgDateBetweenOrderByProgDateStartTimeAsc

In the example they give, they are only doing an OrderBy on one value. In the example above ProgDate and StartTime would be two separate values.

Have you tried it? It should work. – M. Deinum Aug 19 '14 at 10:46 — M. Deinum, Aug 19 '14 at 10:46

score 209 · Accepted Answer · answered Aug 19 '14 at 16:05

209

The trick is to simply delimit the properties you want to sort by using the direction keywords Asc and Desc. So what you probably want in your query method is something like:

…OrderByProgDateAscStartTimeAsc

Note, how we conclude the first property definition by Asc and keep going with the next property.

Generally speaking, we recommend switching to @Query based queries, once method names exceed a certain length or complexity. The main reason being that it's awkward for clients to call these very long methods. With @Query you rather get the full power of the query language plus a reasonably sized method name that might be of higher level language to express the intent of the query.

answered Aug 19 '14 at 16:05

Oliver Drotbohm

80,157
18
225
211

is there any way to order by `JoinColumn` id? Thanks – Laxman Nov 18 '17 at 14:20
@Laxman check this: https://stackoverflow.com/questions/28084064/spring-jpa-pagerequest-ordering-by-a-join-column – dchang Oct 23 '19 at 11:11
It doesn't work if there are no any conditions. So findByIdOrderByStartAscIdDesc works, but findAllOrderByStartAscIdDesc doesn't. – Selindek Sep 11 '20 at 15:18
Is it possible to combine `OrderBy` method keyword with `Sort` parameter in a `Pageable` object? – Conscript Apr 20 '21 at 04:51

score 12 · Answer 2 · answered Jan 26 '21 at 08:14

12

I am Sharing one other approach code snippet for implementing get operation where performing sort operation ordered by multiple column

        List<Order> orders = new ArrayList<Order>();

        Order StartTimeOrder = new Order(Sort.Direction.DESC, "StartTime");
        orders.add(StartTimeOrder);
        Order progDateOrder = new Order(Sort.Direction.ASC, "ProgDate");
        orders.add(progDateOrder);
        return repository.findAll(Sort.by(orders));

answered Jan 26 '21 at 08:14

abhinav kumar

1,487
1
12
20

2

Thanks, this is what I was looking for. – reiley Feb 21 '21 at 14:33
What import is for Order? – Michal.S Apr 26 '22 at 14:27
1

org.springframework.data.domain.Sort; org.springframework.data.domain.Sort.Order; – abhinav kumar Apr 26 '22 at 15:08

score 8 · Answer 3 · answered Aug 19 '14 at 10:42

8

Yes it's should be possible:

Try this:

findByProgrammeAndDirectorAndProgDateBetweenOrderByProgDateStartTimeAsc(String programme, String director, Date progStart, Date progEnd);

I have not tested the code, but according to things I've already done, it should work.

answered Aug 19 '14 at 10:42

Paulo Fidalgo

21,709
7
99
115

Is that signature correct? I would think the Between covers the progStart and progEnd, but OrderByProgDateStartTimeAsc, would that need an additional two parameters? – PDStat Aug 19 '14 at 10:48
1

@PaulStatham I believe it's right. Between needs two Dates and the Order don't need any argument, since you are instructing to order by a value that is matched by the column, like in SQL ... ORDER BY ProgDateStartTime ASC – Paulo Fidalgo Aug 19 '14 at 13:40

score 2 · Answer 4 · answered Sep 13 '22 at 08:06

A bit more compact :

return repository.findAll(
  Sort.by(List.of(
    new Order(Sort.Direction.DESC, "StartTime"),
    new Order(Sort.Direction.ASC, "ProgDate")
  ))
);

or

return repository.findAll(
  Sort
  .by(Direction.DESC, "StartTime")
    .and(Sort.by(Sort.Direction.ASC, "ProgDate"))
);

How to sort by multiple properties in Spring Data (JPA) derived queries?

4 Answers4

Linked