Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hibernate - Orderding criteria by formula property

Say I have an entity MyEntity, and it has a formula-based property fmlaProp. Now say I create a criteria:

s.createCriteria(MyEntity.class) 
    .setProjection( 
        Projections.distinct( 
            Projections.property("fmlaProp"))) 
    .addOrder(Order.asc("fmlaProp"));

in this case I get the following SQL:

SELECT DISTINCT fmlaProp-sql FROM MY_ENTITY_TABLE ORDER BY fmlaProp-sql

Which gives an error on Oracle saying that order-by expression is non-selected. Then I tried the following criteria:

s.createCriteria(MyEntity.class) 
    .setProjection( 
        Projections.distinct(
            Projections.alias(
                Projections.property("fmlaProp"),
                "alias1")) 
    .addOrder(Order.asc("alias1"));

Which generates "order by alias1" which works fine. But it is kind of ugly -- the code must "know" of those formula properties, which violates "write once" principle. Any thoughts or suggestions on that? Thank you in advance.

like image 713
Andrey Balaguta Avatar asked Nov 24 '11 13:11

Andrey Balaguta


1 Answers

This is expected behavior from Hibernate. It doesn't have to do with the formula property specifically, but that you want to do ordering with a projected value. From the Hibernate Docs:

An alias can be assigned to a projection so that the projected value can be referred to in restrictions or orderings. Here are two different ways to do this...

As far as alternatives, you could try making the formula property a virtual column (in versions of Oracle 11 and above) or wrapping the table in a view with this column computed. That way, Oracle will know fmlaprop directly, which can be used just like a "normal" column.

like image 129
Adam Hawkes Avatar answered Oct 20 '22 21:10

Adam Hawkes