search

my2iu / Jinq

LINQ-style queries for Java 8
Other
659 stars 71 forks source link

Problem with Pair sorting #72

Open undefined-user-ctrl opened 6 years ago

undefined-user-ctrl commented 6 years ago

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

undefined-user-ctrl commented 6 years ago

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

undefined-user-ctrl commented 6 years ago

In my case, i was able to use by sorting from column position (Count value).

query.sortedBy(x-> 2);

my2iu commented 6 years ago

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.