my2iu
Problem with Pair sorting
When you have a Pair<Entity,Long>, and you try to sort by .sorted(c -> c.getTwo());
The query generated is very close to expected, but has an ")" in the wrong position:
ORDER BY (SELECT COUNT(A) FROM Entity A WHERE B.id = A.entity.id ASC)
Maybe if an extra parentheses is placed around the column name, without the 'ASC', it may solve the problem, without much effort:
ORDER BY ((SELECT COUNT(A) FROM Entity A WHERE B.id = A.entity.id) ASC)
Or, as checking in SelectFromWhere class, it should be reversed:
queryState.queryString += (sortParams.isAscending ? " ASC" : " DESC"); if (sortParams.expr instanceof SubqueryExpression) // Special handling of subquery parantheses queryState.queryString += ")";
Don't know if it fits another scenarios. Thanks
By the way, in my scenario its a Count. If in the query the expression recieve an alias, it can be used in sort:
Original:
SELECT B, (SELECT COUNT(A) FROM entity A WHERE B.id = A.entity.id) as c FROM anotherEntity B ORDER BY (SELECT COUNT(A) FROM Entity A WHERE B.id = A.entity.id DESC)
Final: SELECT B, (SELECT COUNT(A) FROM entity A WHERE B.id = A.entity.id) as c FROM anotherEntity B ORDER BY c DESC
In my case, i was able to use by sorting from column position (Count value).
query.sortedBy(x-> 2);
I'm a bit busy at the moment, so I'll take a closer look next week if I can find the time. In the past, I've had all sorts of problems with sorting by subqueries using different JPA providers.
Would you be able to just use standard groups to get the same effect? Those operations are much more reliable and much faster in JPA and SQL than using subqueries.
