In a @Formula, Refer to a Join Dependency Outside this Formula (not a Sub-Select and not this object)

Question

I'm writing a Hibernate @Formula which will return a certain value. The @Formula exists in a certain Domain Object (Plans.java).

However, that value depends on a future JOIN which it currently doesn't know about. That is,

Plans Class

@Formula("CASE" + 
         "    WHEN " + 
         "                     nv.organizationalstat = 'FELLOW' " + 
         "                     AND nv.hi_education_cd IS NOT NULL " +
         "                     ... " + // Some PLANS_T fields here, which are OK, since it's this object
                                       // Independent SUB-Selects are also OK
                                       // But what doesn't work is the Future Join dependency, "nv"
         "    THEN 1 " +
         "    ELSE 0 " + ...

When building the query I do the Join with another table, NVTable,

     Root<NVTable> nvRoot = criteriaQuery.from(NV.class);
     Join<Object,Object> plans = nvRoot.join("plans", JoinType.LEFT);

@Formulas work when I have independent Sub-Selects, like (SELECT .. FROM ..) inside them, or if they're all strictly within this object. But how do I include the field of a Future Join, which is not an independent Sub-Select?

Answer 1

That's not possible, you will have to do a separate join in a subquery.

Having said that, this is a perfect use case for Blaze-Persistence Entity Views.

Blaze-Persistence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. I created Entity Views on top of it to allow easy mapping between JPA models and custom interface defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure the way you like and map attributes(getters) via JPQL expressions to the entity model. Since the attribute name is used as default mapping, you mostly don't need explicit mappings as 80% of the use cases is to have DTOs that are a subset of the entity model.

Assuming you have an entity model like this

@Entity
public class NV {
    @Id
    Integer id;
    String organizationalstat;
    String hiEducationCd;
    @ManyToOne
    Plans plans;
}

@Entity
public class Plan {
    @Id
    Integer id;
}

A DTO mapping for your model could look as simple as the following

@EntityView(Nv.class)
interface NvDto {
    Integer getId();
    @Mapping("CASE WHEN organizationalstat = 'FELLOW' AND hiEducationCd IS NOT NULL AND plans.someField = 'ABC' THEN 1 ELSE 0 END")
    Integer getYourFormulaResult();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

NvDto dto = entityViewManager.find(entityManager, NvDto.class, id);

But the Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

It will only fetch the mappings that you tell it to fetch.