CTE Mapping within EntityView

[aleksvidak]

Is the example given in https://stackoverflow.com/questions/67302342/how-to-avoid-n1-problem-with-native-sql-query-in-springboot-with-hibernate/67336967#67336967 the only way of mapping cte fields into the entity view? Seems like in this case a subquery is generated instead of CTE.

[beikov]

It depends what you want to achieve or what the CTE does. If you want to e.g. limit the result of a collection or entity result, you could also create a @Mapping with @Limit. If you want to use a recursive CTE, you will have to do what the StackOverflow post describes.

Seems like in this case a subquery is generated instead of CTE.

Depending on the database that you are using, inlining of the CTE might be the only way to implement this.

[aleksvidak]

In our particular case, we initially used plain SQL view which has quite some joins and complexity to it (using SQL Server btw). CTEs allow us to create a named temporary result sets that we can reference within main select statement. So the whole view is more manageable and easier to understand. I guess having a subquery or a CTE wouldn't make a major difference in this case, since SQL Server would probably optimize the execution of the whole query. I was just surprised that using @With and @EntityViewRoot actually creates subquery and not CTE.

[aleksvidak]

@beikov when you say inlining of the CTE, do you mean building a query manually, using CriteriaBuilderFactory?

[beikov]

Blaze-Persistence might inline a CTE as subquery into the from clause of the query that is using a CTE instead of rendering a with clause, but whether that happens depends on the database and version. I think you can override this by setting this config property: https://persistence.blazebit.com/documentation/1.6/core/manual/en_US/#INLINE_CTES

[aleksvidak]

I am sorry I am asking here, since you basically answered my specific initial question, but I just started using blaze persistence and the requirement I want to implement is somewhat related.
We have an entity LimitedPartner which is connected to an entity Fund through entity Commitment (in venture capital limited partner commits a certain amount of money to a fund through a commitment)
What we initially had is a view, something like:

CREATE VIEW vLimitedPartner
AS
WITH CommitmentsCTE
       AS
       (SELECT lp.Id
             , ROUND(SUM(Amount), 2)                                        AS CommittedCapital
             , COUNT(LimitedPartnerId)                                      AS NumberOfCommitments
             , COUNT(DISTINCT FundId)                                       AS NumberOfFunds
        FROM Commitment com
          INNER JOIN LimitedPartner lp ON lp.Id = com.LimitedPartnerId
        GROUP BY lp.Id)
SELECT lp.*
     , COALESCE(com.NumberOfFunds, 0)                 AS NumberOfFunds
     , COALESCE(com.CommittedCapital, 0)              AS CommittedCapital
     , COALESCE(com.NumberOfCommitments, 0)           AS NumberOfCommitments
FROM LimitedPartner lp
  LEFT JOIN CommitmentsCTE com ON com.Id = lp.Id

We had two use cases:

1. Get by ID
2. Get all
   This worked when we fetched by id and fetching all where we returned limited partner regardless whether there is a commitment or not.

But now we have a new use case:

1. Get all by funds:
   With the filter based on a fund is introduced, it affects both the data coming from CTE and the final result produced (displaying only commitments details filtered by funds, and consequently returning only limited partners that committed to a fund).
   So what changes to the original view in case there is a fund filter is that CTE is filtered out based on funds and left join becomes inner join.

What I tried to implement this and it works ok, is creating a dynamic query using criteria builder and then mapping to a dto using selectNew.

I am wondering whether something can be done using entity views. I tried this approach by implementing CTEProvider following https://staging-persistence.blazebit.com/documentation/1.6/entity-view/manual/en_US/index.html#using-ctes-in-entity-views and using EntityViewRoot to join CTE, but got to a point where I need to change join type if there is a filter or not.

Do you know of another approach that could be helpful or I missed the point totally? :)

[beikov]

I don't quite understand why you need to change the join type. Usually, if you have a filter based on an expression with a left joined table as data origin, the semantics are as if you had inner joined i.e. left join x on .. where x.col1 = 1 is semantically the same as inner join x on .. where x.col1 = 1 and a database optimizer could actually infer that. I know that some optimizers aren't that great, but either way, the left join shouldn't be a big problem anyway.
Do you have performance problems that an inner join would solve? Please open a new discussion for this topic though to not pollute this one here :)