Support conditional order-by clauses in QueryBuilder
Description
Activity
Patrick Lübbecke November 3, 2022 at 12:02 PMEdited
Just in case somebody runs into the same problem: In the meantime I have found a different approach that works in 5.6. I used selectCase() to achieve the same effect:
List<Order> orderStatements = new ArrayList<>();
orderStatements.add(cb.desc(cb.selectCase().when(cb.equal(root.get("rmgtnav_collection_type"), "PROFILE"), 0).otherwise(1)));
That creates a case/when construct in the order by segment:
order by generatedAlias0.path asc, case when generatedAlias0.collectionType=:param8 then 0 else 1 end asc, generatedAlias0.denotation asc
Patrick Lübbecke October 12, 2022 at 11:38 AMEdited
There is a good chance that we will upgrade to 6.1 once we’re done upgrading from 3.6 to 5.6. Thank you for the hint that this might already be solved. Under these circumstances this ticket can be closed for now.
Christian Beikov October 12, 2022 at 11:35 AM
Did you try Hibernate 6 yet? Pretty sure this is fixed already.
Details
Details
Assignee
Reporter
Patrick LübbeckePriority
Minor
I would like to add conditional order by operators to CriteriaBuilder like that:
order by rmgtnav_path, rmgtnav_collection_type<>'PROFILE', rmgtnav_denotation ascOr as
Orderobjects:What the
rmgtnav_collection_type<> 'Profile'part does is to resolve the statement as a boolean which is true (1) or false (0) and start sorting with collection_type = Profile on top in my case. Unfortunately, I get an error when callingsession.createQuery(cq)method:org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: <> near line 1, column 596 [select generatedAlias0 from com.myapp.core.rmgt.rate.business.object.RmgtBVOImpl as generatedAlias0 where ( generatedAlias0.collectionType<>:param0 ) and ( ( generatedAlias0.collectionSubType<>:param1 ) or ( generatedAlias0.collectionSubType is null ) ) and ( ( lower(generatedAlias0.profileStatus) like :param2 ) and ( ( generatedAlias0.type=:param3 ) or ( generatedAlias0.type=:param4 ) ) ) and ( generatedAlias0.enabled=:param5 ) and ( ( generatedAlias0.owner=:param6 ) or ( generatedAlias0.owner=:param7 ) ) order by generatedAlias0.path asc, generatedAlias0.collectionType<>:param8 asc, generatedAlias0.denotation asc]I think it would be beneficial, If criteriaBuilder would also allow to add these kind of statements.