I have a nice Blaze-Persistence query with a WITH CTE, used with multiple other queries, tied together with unions. The query has the correct result (as of sql).

public List<FilterResult> getFilters(HourSearchDto hourSearchDto) {
  ...
  FinalSetOperationCriteriaBuilder<FilterResult> cb = cbf.create(entityManager, FilterResult.class)
    // Start with the WITH()
    .with(HourCTE.class, false)
      .from(Hour.class, "hour")
      .bind("id").select("id")
      .bind("employeeId").select("employee.id")
      .bind("taskId").select("task.id")
      .where("UPPER(hour.description)").like().expression("'%"   hourSearchDto.getDescription().toUpperCase()   "%'").noEscape()
    .end()
    // First select, for reference Employee. Use the selectNew(FilterResult.class) to map the result to FilterResult.
    .selectNew(FilterResult.class)
      .with("employee.id", "id")
      .with("'EMPLOYEE'", "referenceName")
      .with("COUNT(hour.employeeId)", "count")
      .with("FORMAT('%1$s (%2$s)', employee.firstName, COUNT(hour.employeeId))", "displayValue")
    .end()
    .from(Employee.class, "employee")
    .leftJoinOn(HourCTE.class, "hour")
      .on("hour.employeeId").eqExpression("employee.id")
    .end()
    // UNION to add next select.
    .union()
    // Next select, for reference Task. Simple select as first select above maps the result to FilterResult already.
    .select("task.id", "id")
    .select("'TASK'", "referenceName")
    .select("COUNT(hour.taskId)", "count")
    .select("FORMAT('%1$s (%2$s)', task.name, COUNT(hour.taskId))", "displayValue")
    .from(Task.class, "task")
    .leftJoinOn(HourCTE.class, "hour")
      .on("hour.taskId").eqExpression("task.id")
    .end()
    .endSet()
    .orderByAsc("referenceName")
    .orderByAsc("displayValue");

    return cb.getQuery().getResultList();
}

Problem is, the result is sent back as an ArrayList<Object[4]> instead of the expected ArrayList<FilterResult>. When I remove the union and its query, the .selectNew(FilterResult.class) correctly constructs an ArrayList<FilterResult>.

How do I make sure the complete query, including the union, returns an ArrayList<FilterResult> as well?

For sake of completeness:

public class FilterResult {

    @Id
    Long id;

    String referenceName;

    Long count;

    String displayValue;

    public FilterResult() {
    }

    public FilterResult(Long id, String referenceName, Long count, String displayValue) {
        this.id = id;
        this.referenceName = referenceName;
        this.count = count;
        this.displayValue = displayValue;
    }

    // Getters
}

@CTE
@Entity
public class HourCTE {

    @Id
    private Long id;

    private Long employeeId;

    private Long taskId;
}

CodePudding user response：

This is a limitation of the current API. You can track https://github.com/Blazebit/blaze-persistence/issues/565 for progress on this matter. A possible workaround is to wrap the union part into another CTE and then use the selectNew part only once in the query selecting from that CTE.