Hibernate is difficult for string_agg, or sorting by a nested collection
There are several hacks and almost no use cases when you need them.
What we will cover here:
1. Setup a use case for sorting by a nested collection
2. Present a problem
3. Show how to do that in a native SQL
4. Show how to use HQL as a solution
5. Conclude the story
Use case: Students with assigned courses
Where there is an admin which for any reason wants to sort all students by their courses combined in a column.
And not only the courses themselves have to be sorted in the column, we have to make sure we keep sorting across students based on that sort 😞. Requirements are crazy these days.
The model will be pretty simple for the exercise:
Problem — no out of the box support from hibernate
You can try sorting by hibernate magic, without much success, because it won’t aggregate courses for you
The failure is obvious if you look into generated sql. It is a very basic sorting without taling into account all the other courses
Native SQL approach
First of all we have to make sure the courses themselves are sorted
We can achieve what we are looking for with a native SQL above but that leaves no benefits of Hibernate per se.
Approach with Spring Data (or Hibernate?)
First of all, we need to register custom function for Hibernate to enable aggregations and internal sorting (see the ORDER BY ?3 trick)
Now we can compose a query on almost plain query language with aggregations which gives us exactly what we were looking for:
The usage is straightforward with the method call and default sort calls:
Conclusion — do not use hibernate or any ORMs, stick to a dead simple jdbc. But if are interested in trying out the above code in action, go to tests section in the repo https://github.com/anatolyD/hibernate-aggs
