1

I need a way to sort based on date, with all rows of a certain type to be grouped last (but also sorted by date).

For instance, suppose I have a DB column where values can take on ["A", "B", "C"]. In the same table, there is also a dateCreated column.

I want all rows with "A" to be last and all rows (including those within group A) to be sorted by date created (ascending for simplicity sake)

For instance, if I had the rows:

ID enum dateCreated
1 A 2020-01-01
2 A 2020-01-03
3 A 2020-01-02
4 B 2020-02-03
5 B 2020-02-01
6 C 2020-02-02

The resulting order should be:

5,6,4,1,3,2

To explain, all of the non-A rows come first, ordered by date. Then all of the A rows, also ordered by date.

I see the SO post QueryDsl orderBy specific string values which almost provides what I need, but it is unfortunately not nuanced enough for my use case.

Azianese
  • 554
  • 7
  • 21
  • Sounds unlikely - how would you communicate that A is special, but I don't know. If not possible, as an alternative is it worth wrapping your query interface in another class that sorts the list that comes back based on your nuanced rules? – Chris Aug 08 '22 at 20:56
  • Oh I see - my bad, you can implement OrderSpecifier, and so in theory you can do whatever you need there, but I guess you're wondering how to do that for your case, I haven't looked in detail at that interface but I'd expect it to be pretty flexible in terms of how complex your ordering can be – Chris Aug 08 '22 at 21:05

2 Answers2

1

I ended up finding a working solution with something like:

    private OrderSpecifier<Integer> orderByEnum() {
        NumberExpression<Integer> cases = new CaseBuilder()
                .when(QTable.table.enum.eq("A"))
                        .then(2)
                .otherwise(1);
        return new OrderSpecifier<>(Order.ASC, cases);
    }

I added that to a order specifier array like so:

    OrderSpecifier<?>[] order = new OrderSpecifier[] {
            orderByEnum(),
            QTable.table.dateCreated.asc()
    };

And then I passed the order specifier array to the query with

    queryFactory.select(...)
            .from(...)
            .where(...)
            .orderBy(order);

It looks like this got me what I needed. Just had to break the problem down into individual components and add that to an order specifier array.

Azianese
  • 554
  • 7
  • 21
0

You can order by a boolean expresion. Booleans order false to true naturally, so you can just sort ascending.

.orderBy(enum.eq("A").asc(), created.asc())
Jan-Willem Gmelig Meyling
  • 3,685
  • 2
  • 21
  • 43