CODEWITHSUNDEEP
jpajpqlquerydsl
skwisgaar
skwisgaar

Reputation: 977

QueryDSL coalesce in order by

I'm trying to join two tables and output them and sort them alphabetically by two fields like order by coalesce(tableA.name, tableB.name) (NOT order by tableA.name, tableB.name), so result should be something like:

tableA.name    tableB.name
A              null
B              null
null           C
D              null
null           E

In plain SQL it works fine but when I try to do it with QueryDSL it adds additional column to generated select statement and sorts only by first specified column:

//java code
query.orderBy(qTableA.name.coalesce(qTableB.name).asc());

//generated sql code
SELECT ...
       COALESCE(tablea_.NAME, tableb_.NAME) AS col_9_0_
FROM   ...
WHERE  ...
ORDER  BY tablea1_.NAME ASC

Can somebody tell why it does it like that and whether it is possible to make it work as I expect?

Upvotes: 2

Views: 4494

Answers (1)

Jaumzera
Jaumzera

Reputation: 2359

Try this:

final Coalesce<String> coalesce =
    new Coalesce<>(String.class).add(optionalA).add(optionalB);

Use the coalesce in your select fields and so in your order by clause:

.orderBy(coalesce.asc()) // or desc()

Upvotes: 5

Related Questions