I have the following problem:
Entity Alpha has a @OneToMany reference to a Set<Beta>. The Beta entity has an attribute 'name', which is a String.
In the application we have a search UI to find entities of type Alpha. The result table show a colum 'current Beta' which presents the name of the latest/actual Beta in the Set.
Now, the result table should be sortable by this column.
This leads to the requirement of sorting Alphas by the Name of the Beta with this highest id in the database.
I agree with Hibernate. It appears to be doing exactly what you told it to do.
So the question is - did you tell it to do the right thing?
You didn't mention what order the alpha records should be returned by other than in order of Beta.name. And in cases where there is more than one Alpha referencing the same Beta name, the order of alphas has not been indicated, so you've basically said you're OK with random alpha order.
I suspect that what you really want is something more like "ORDER BY alpha.id ASC, beta.name ASC".
When it comes to destroying a civilization, gas chambers cannot hold a candle to echo chambers.
My first impulse would be to minimize coding of SQL on the Java side at all, especially the more complex the joins, filter conditions and order by clauses become. Instead:
• Create a view on your database that encapsulates all of the SQL you need to write, and work out any relational/SQL problems such as the one you describe there, independent of Java, JPA or Hibernate.
• Call the view from Hibernate as simply as you would a single table with no joins, filters or ordering required, other than bind variables for filter condition/WHERE clause values that are not hard-coded into the view, such as a primary key ID.