5

I have a requirement which is somewhat similar to this. My API supports a filter where there is more than 8 filter parameter. So, I want to create a query dynamically based on the filter parameter passed. I am using CriteriaBuilder to create a dynamic query.

Now, I am able to create dynamic queries successfully but the issue comes when the user wants to sort on an aggregate functions. In my query, I have 4 aggregate (count) function. So to support sorting on these columns, I just use the expression of this aggregate function but what I want is to use the alias of this expression

Repeating the expression in select and order by doesn't seem right to me. So, is there a solution/workaround to the problem. I want to declare the expression alias once and use it in both select and order by clause and if required in my group by clause in future

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ashishkumar Singh
  • 3,580
  • 1
  • 23
  • 41
  • AFAIK you cannot. In SQL you also have to duplicate this and cannot use the alias of the column in the select clause (at least it always errored out on me). – M. Deinum Dec 16 '19 at 10:14
  • In native sql, we can alias the function and use in the order by/group by clause – Ashishkumar Singh Dec 16 '19 at 10:15
  • Then I might have to work on my SQL foo (it for some reason never worked for me). But I don't thing the `Criteria` API can do this. You can reuse the created `Expression` afaik, so from a code perspective it isn't that hard. – M. Deinum Dec 16 '19 at 10:38
  • I am using the expression currently in both places and it's working as expected. But instead of expression, I want to use alias – Ashishkumar Singh Dec 16 '19 at 10:41
  • There isn't even a method on the `CriteriaBuilder` to group-by/order by an alias. So as stated, the only option is to reuse the `Expression`, adding an alias, might result in reusing it in the generated SQL though. – M. Deinum Dec 16 '19 at 10:43

1 Answers1

0

You probably need to build a custom projection class, you can find a decent and easy one, SQLProjectionWithAliasSupport in https://hibernate.atlassian.net/browse/HHH-2952 (from Sergey Pulyaev)

If then one wants to support also sub criteria, things get more complicate

Testo Testini
  • 2,200
  • 18
  • 29