Filter Entity View Based on CTE results

[aleksvidak]

To follow up on the question in #1769 (reply in thread) and the answer, I suppose @beikov is right about the left join and where condition making the same impact.
So let's say, I have entity view like:

@EntityView(LimitedPartner.class)
@With({LimitedPartnerView.CommitmentCteProvider.class})
@EntityViewRoot(
    entity = LimitedPartnerCommitmentCte.class,
    name = "com",
    condition = "lpId = VIEW(id)",
    joinType = JoinType.LEFT)
public interface LimitedPartnerView {

  @IdMapping
  Long getId();

  String getName();

  @Mapping("com.committedCapital")
  BigDecimal getCommittedCapital();

  @Mapping("com.numberOfFunds")
  Integer getNumberOfFunds();

  @Mapping("com.firstClosingDate")
  LocalDate getFirstClosingDate();

  @Mapping("com.numberOfCommitments")
  Integer getNumberOfCommitments();

  class CommitmentCteProvider implements CTEProvider {
    @Override
    public void applyCtes(CTEBuilder<?> builder, Map<String, Object> optionalParameters) {
      var funds = (List<Long>) optionalParameters.get("funds");
      var starterBuilder =
          builder
              .with(LimitedPartnerCommitmentCte.class)
              .from(LimitedPartner.class, "lp")
              .innerJoinOn(Commitment.class, "com")
              .setOnExpression("com.limitedPartnerId = lp.id")
              .bind("lpId")
              .select("lp.id")
              .bind("numberOfCommitments")
              .select("COUNT(com.limitedPartnerId)")
              .bind("numberOfFunds")
              .select("COUNT(DISTINCT com.fundId)")
              .bind("committedCapital")
              .select("ROUND(SUM(com.amountFundCurrency), 2)")
              .bind("firstClosingDate")
              .select("MIN(com.closingDate)");

      if (funds != null) {
        starterBuilder.where("com.fundId").inLiterals(funds);
      }
      starterBuilder.end();
    }
  }
}

In the entity view repository we have a method:
List<LimitedPartnerView> findAll(EntityViewSettingProcessor<LimitedPartnerView> processor);

Service method:

  public List<LimitedPartnerView> getAll(List<Long> funds) {
    return limitedPartnerViewRepository
        .findAll(
            setting -> {
              setting.withOptionalParameter("funds", funds);
              return setting;
            });
  }

How should I add the where clause which limits the results based on CTE?

[beikov]

You could add @AttributeFilter to the entity view attributes you want filterable and use that for filtering. Also see https://persistence.blazebit.com/documentation/1.6/entity-view/manual/en_US/#filter-api

[beikov]

Your LimitedPartnerView maps all the CTE attributes, so you can put the attribute filter on one of the attributes. If you want to filter the data to aggregate, your approach with optional parameters looks ok to me.

[aleksvidak]

Going back to your reply #1769 (reply in thread), if in CTE I aggregate results and filter by fund, then CTE data loses info about individual funds and the only way to return Limited Partners with matching aggregated data is to do inner join, right? So I can only apply filter on the CTE, not the main select statement.

[aleksvidak]

I wonder whether approach with CTEs is too complex and if I could approach this problem with multiple Entity Views...

[beikov]

Maybe try to explain your use case and requirements again. I have the feeling that I might be missing something.
You want to aggregate the commitments per partner, but only certain commitments? Did I get that right? Because if that's all, then the solution you have right now with the optional parameter already is sufficient.

[aleksvidak]

Sorry for miscommunication and thank you for wanting to help out.

To sum the desired query: aggregate the commitments per partner, but return only partners that have commitments in certain funds.

[beikov]

but return only partners that have commitments in certain funds

You could just ignore/filter out LimitedPartnerView if getCommittedCapital() <= 0, which would essentially do what you are looking for. If you want to do the filtering on the query level you could do that with an attribute filter on the committedCapital attribute or through a view filter that adds a predicate like e.g. exists (select 1 from Commitment c where c.lpId = VIEW(id) and c.fundId in :funds)

[aleksvidak]

Do you know how to pass :funds parameter for view filter?

[beikov]

You can just use the parameter e.g. where("c.fundId").eqExpression(":funds")

[aleksvidak]

Currently I have a method:

  public List<LimitedPartnerView> getAll(List<Long> funds) {
    var setting = EntityViewSetting.create(LimitedPartnerView.class);
    if (!CollectionUtils.isEmpty(funds)) {
      setting.withOptionalParameter("funds", funds);
    }
    return limitedPartnerViewRepository.findAll(setting);
  }

so I am not sure how to incorporate this where("c.fundId").eqExpression(":funds"). 😕 ... if we are talking about https://staging-persistence.blazebit.com/documentation/1.6/entity-view/manual/en_US/index.html#view-filters, then this seems only to support static filtering

[beikov]

You can define the view filter like this:

    class MyFilterProvider extends ViewFilterProvider {
        @Override
        public <T extends WhereBuilder<T>> T apply(T whereBuilder) {
            return whereBuilder.where("fundId").eqExpression(":funds");
        }
    }

and enable the filter when binding the optional parameter.