CODEWITHSUNDEEP
mysqlhibernate
MunkMurray
MunkMurray

Reputation: 28

hibernate order by expression doesn't accept '>'

I have the following query:

SELECT ga FROM Keyword ga ORDER by results>0 asc,lastSearch asc

this query runs and works perfectly when executed in mysql workbench but when put in the @query attribute in a crudrepository class it doesn't compile at all.

when i remove the '>' sign from the query and just order by the column values it works fine.

any suggestions besides getting an unsorted result and sort it in my code?

Upvotes: 0

Views: 52

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123871

Not fully sure what should ORDER BY results > 0 mean, if this is condition, we can convert that into this:

SELECT ga 
FROM Keyword ga 
WHERE ga.results > 0
ORDER BY ga.results asc, ga.lastSearch asc

In case we need a conversion to boolean, we can do it like this:

SELECT ga 
FROM Keyword ga 
ORDER BY ga.results asc
ORDER BY CASE WHEN ga.results > 0 THEN 0 ELSE 1 END asc
 , ga.lastSearch asc

HQL simply does not fit 100% to underlying DB engine SQL syntax

Upvotes: 2

Related Questions