Another question that came up while adventuring through JPA criteria queries. Consider the following entities (pseudocode to make it more concise):


Now, suppose we want to query for some As. For each so selected A also fetch the first B in the associated ordered collection of Bs. In other words, the number of Bs that we are fetching per an A is known a priori (as opposed to an undeterminable number of Bs; e.g., fetch all Bs associated to some A).


The fetch join together with the range specification results in the infamous warning 'HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!'. However, because of the additional index predicate, I think Hibernate should be able to optimize query execution not to involve the in memory processing. More generally, for any kind of query where the number of associated entities that are to be fetched is fixed and known a priori, it should be possible to formulate a query that delegates all work to the database system such that in memory processing is optimized away. Do you agree, or am I missing something here? If it is possible, how about filing a feature request issue?

I wrote an article about how this can be done with Window Functions.

However, if this will be supported, it will only go into Hibernate 6. You can add a Jira issue of course.

I'm aware of that article. In fact, I read it before writing this posting. The point about using JPA criteria queries is that I want to remain portable (and that the actual query is again constructed dynamically based on user input). To be portable when using Window Functions one would (i) need to write a query for each DBMS that one wants to support, (ii) provided that it supports Window Functions.


I will.

Until we will support it natively in Hibernate 6, you can build the Window Functions query with jOOQ.

Another option is to fetch the ids first with a Criteria API query, and then provide the ids to a JPQL query with fetches the desired entities.