I wanted to share this hard-earned discovery but couldn't find a suitable place to (or maybe didn't want to try that much)
Yes, the great and awesome Rand McRanderson has a day job and in such day job I do program and that programming involves the Hibernate ORM system.
One annoyance has been that Hibernate does not allow simple expressions in the sort by clause, that is I cannot do (inside a query annotation or by functionally using the entity manager to create a query)
SELECT * FROM Books b WHERE b.title LIKE CONCAT('%' , :param , '%') ORDER BY b.title = :param DESC, b.publishDate
For the unfamiliar, what this does is it looks through a database's book table for titles (the b.title field of the object) that contain the value of :param (which could be user input, for our example let's say the value is "Bob"), the LIKE and '%', '%', mean that the text "Bob" could have any text before or after it in the title, ie, "Bob's adventure", "Adventures with Bob", "The Bob of Bobbington" are all matches. The select clause is set to '*' which means to fetch all the fields (in this case that would be the entire Book entity).
In this scenario, sorting the results by b.publishDate is reasonable, however then you get cases where, a book called "Bob", published in 2022, is ranked after a book called "Long list of names including Alan, Joe, Michael, also Bob" published in 2024. Some people are fine with this, I find it a little annoying.
So what I'm trying to do is to first sort by whether or not there is an exact match for the title "Bob" using the expression b.title = :param - this will evaluate to a boolean value, either TRUE or FALSE, and since TRUE (which means there is an exact match) = 1 and FALSE = 0 in Hibernate (as in most of the computer world), I sort this descending, after this sort criteria, I still sort by publishDate so I end up with exact matches first sorted by date and then the other matches sorted by date afterwards.
That's the idea at least, but Hibernate doesn't like this. Try to run this code and you get an exception like:
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: = near line 1, column ...
(On a side note, I wish there was a better way to check this expressions at compile time, I know I could use the Criteria API for this with the static metamodel approach, but that ends up being very verbose with a significant learning curve that I cannot spare at the moment)
This issue is that Hibernate does not like that raw '=' expression in an ORDER BY clause (it is legal in SQL I believe, which raises questions about why I'm using Hibernate, which I'm not going to go into now)
Anyways, I went through a lot of variations trying to figure out how to get around this behavior (once I understood what the issue was, which took a little while), I seem not to be the only one to get this error:
(Although given that this post is from 10 years ago I might be the only one in recent times)
What I finally discovered is while you cannot use simple expressions like X = Y in order by clauses for Hibernate, however you can use a CASE expression, like CASE WHEN A THEN B ELSE C END, where A is a boolean expression which can be... X = Y. So I can rewrite this thing as:
SELECT * FROM Books b WHERE b.title LIKE CONCAT('%' , :param , '%') ORDER BY (CASE WHEN b.title = :param THEN 1 ELSE 0 END) DESC, b.publishDate
(Not sure if the parentheses are necessary, but I think it looks pretty ugly without them, and it is my blog so...)
And this works, and it gives me the ordering I want, so I'm happy with it. Now the trick is to remember this into the future where I might encounter this type of situation again, whether that will happen or not, well, that is a true mystery of life.
Anywho, take it to your head, take it to your heart, and remember Rand rocks!
Goodnight Folks!
And God Bless!
