How to create a temporary column + when + order by with Criteria Builder

Question

here is the sql statement I am trying to translate in jpa :

  select 
        id,
        act_invalidation_id, 
        last_modification_date,
        title,
        case when act_invalidation_id is null then 1 else 0 end as test
    from act order by test, last_modification_date desc

The actual translation

Root<Act> act = query.from(Act.class);

builder.selectCase()
                .when(builder.isNull(actRoot.get("actInvalidation")), 1)
                .otherwise(0).as(Integer.class);
Expression<?> actInvalidationPath = actRoot.get("actInvalidation");
Order byInvalidationOrder = builder.asc(actInvalidationPath);

Path<Date> publicationDate = actRoot.get("metadata").get("publicationDate");
Order byLastModificationDate = builder.desc(publicationDate);

query.select(act).orderBy(byInvalidationOrder, byLastModificationDate);
entityManager.createQuery(query).getResultList();

I try to create a temporary column (named test) of Integer type and orderby this column, then orderby lastmodificationdate. The content of this new column is determined by the value of actInvalidation field.

In short: How to create a temp column with integer values, then order by this temp column in jpa ?

Thank you

Answer 1

I didn't test this but it should work like this:

Root<Act> act = query.from(Act.class);

Expression<?> test = builder.selectCase()
                .when(builder.isNull(actRoot.get("actInvalidation")), 1)
                .otherwise(0).as(Integer.class);
Expression<?> actInvalidationPath = actRoot.get("actInvalidation");
Order byInvalidationOrder = builder.asc(actInvalidationPath);

Path<Date> publicationDate = actRoot.get("metadata").get("publicationDate");
Order byLastModificationDate = builder.desc(publicationDate);

Order byTest = builder.asc(test);

query.select(act).orderBy(byTest, byInvalidationOrder, byLastModificationDate);
entityManager.createQuery(query).getResultList();