Issue with left join and conditional COALESCE in Blaze-Persistence EntityView (Unable to Use the @MappingCorrelated value in @Mapping Fields).

[ai-sanket]

I have a use case where we need to fetch values from two different tables: entity and entity_case. These tables do not have a foreign key relationship in the database; the relation is purely logical,

The requirement is to:

• Select fields from entity.
• Override the default col values like label and many others with a case-specific label from entity_case if available.
• Apply filters such as soft-delete flags and case code matching.

Example Query:

SELECT ef.name, COALESCE(efc.label, ef.label) .....
FROM entity ef
LEFT JOIN entity_case efc
    ON ef.entity_code = efc.entity_code
    AND efc.case_code = :caseCode
    AND efc.deleted_on IS NULL
    AND efc.deleted_user IS NULL
WHERE (ef.case_code = :caseCode OR ef.case_code IS NULL)
  AND ef.deleted_on IS NULL
  AND ef.deleted_user IS NULL
ORDER BY ef.name
LIMIT :limit

EntityView Implamantation:

@EntityView(value = Entity.class)
public interface EntityView {

    @IdMapping
    String getEntityCode();

    @MappingCorrelated(
            correlationBasis = "entityCode",
            correlator = EntityCaseCorrelator.class,
            fetch = FetchStrategy.JOIN)
    EntityFieldCaseView getCaseOverride();

    @Mapping("COALESCE(caseOverride.label, label)")
//    @Mapping("COALESCE(VIEW(caseOverride).label, label)")
//    @Mapping("COALESCE(VIEW(caseOverride, label), label)")
    String getLabel();

Correlator:

public class EntityCaseCorrelator implements CorrelationProvider {

	public void applyCorrelation(CorrelationBuilder builder, String correlationExpression) {

		final String alias = builder.getCorrelationAlias();
		builder.correlate(EntityFieldCase.class)
				.on(alias + ".entityCode").eqExpression(correlationExpression)
				.on(alias + ".caseCode").eqExpression(":caseCode")
				.end();
	}
}

Problem:
When we are generating/ populating a value using @MappingCorrelator the correlated field (caseOverride.label) cannot be used directly in another column via COALESCE.
I tried multiple variations:

// @Mapping("COALESCE(VIEW(caseOverride).label, label)")
// @Mapping("COALESCE(VIEW(caseOverride, label), label)")

…but none worked as expected.

I tried different methods but still not able to resolve this problem.

Fallback Attempt

As a fallback, I tried mapping directly with correlationResult, but this loses the COALESCE behavior:

@EntityView(value = EntityField.class)
public interface EntityFieldView {

    @IdMapping
    String getEntityFieldCode();

    @MappingCorrelated(
            correlationBasis = "entityCode",
            correlator = EntityCaseCorrelator.class,
            fetch = FetchStrategy.JOIN,
            correlationResult = "label")
    String getLabel();

This will give me the value generated using EntityCaseCorrelator, but I loose the COALESCE as in case the EntityCase do not have a value then I need to return the value of column from the Entity table.

Is there a workaround to use the correlated field inside COALESCE in Blaze-Persistence EntityView?

[beikov]

This is where secondary entity view roots come into play. You can model the root at the top and use the self introduced alias in all expressions.

[ai-sanket]

Hi @beikov thanks for the response, you mean something like?

@EntityViewRoot(name = "caseOverride", correlator = EntityCaseCorrelator.class, joinType = JoinType.LEFT)
@EntityView(value = Entity.class)
public interface EntityView {

    @IdMapping
    String getEntityCode();

    @Mapping("COALESCE(caseOverride.label, label)")
    String getLabel();

public class EntityCaseCorrelator implements CorrelationProvider {

	public void applyCorrelation(CorrelationBuilder builder, String correlationExpression) {

		final String alias = builder.getCorrelationAlias();
		builder.correlate(EntityFieldCase.class)
				.on(alias + ".entityCode").eqExpression(correlationExpression)
				.on(alias + ".caseCode").eqExpression(":caseCode")
				.end();
	}
}

Bur I see the same error: java.lang.IllegalArgumentException: Attribute 'caseOverride' not found on type 'Entity'

[beikov]

I'd need to see a reproducer to understand what is going on exactly. The testsuite contains the example from the documentation actually and there it works fine. See https://github.com/Blazebit/blaze-persistence/blob/main/entity-view/testsuite/src/test/java/com/blazebit/persistence/view/testsuite/viewroot/SecondaryViewRootTest.java

[ai-sanket]

Note sure if this matches my use case. I am using the similar standard methods, everything works but I am getting this error:
: java.lang.IllegalArgumentException: Attribute 'caseOverride' not found on type '<Entity-class-name-here>' for this specific usecase.