1

I'm working in a project where one task is to create dynamic queries with possibility to sort by related lazy oneToMany entities attribute(s). My first attempt was with Criteria-api but I couldn't make sense of it particulary when I needed to query "non-related" fields, so I switched to plain JPQL where I could express myself more clearly

However it seems that plain JPQL has problems also. One of the use-cases is to fetch AggregateRoots with related entities (e.g join fetch) where predicates can match values of the AggregareRoot or related entity attributes. Same applies to the ordering part. Now results needs to be paginated as well.

I could implement all this, but then when executing the query I got "HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!". After some googling I ended up into https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/ where it was explained and offered 2 solutions. First solution just queries the ID:s of the AggregateRoots (without join fetch) and then the actual data-graph is queried with those IDs (with join fetch).

Now comes the big problem, I need to be able to sort by some of the Aggregate root fields or by related entity fields (all dictated by the UI). My first naive solution was just "select distinct a.id as id from AggregateRoot..." but when it needed sorting I got ORDER BY items must appear in the select list if SELECT DISTINCT is specified. After some wondering I realized I could create projection of all possible "sort-by" fields (AggregateRoot.ID being one of them, used as last resort in the sort by clause to make results coherent) and then fetch AggregateRoot IDs from there to be passed to the actual data-query. However now I ended up in the main problem of the ORM impedance mismatch (as I see it). Basically in the end I have following kind of SQL

select distinct aggregate0_.id as col_0_0_, relatedEntity_.name as col_6_0_ from AggregateRoot aggregate0_ inner join related_entity relatedEntity_ on aggregate0_.id=relatedEntity_.aggregate_id order by col_6_0_ DESC, col_0_0_ offset 0 rows fetch next 3 rows only

which will then end up with results

<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
}
</style>
</head>
<body>
<table>
<tr>
<td><b>col_0_0_</b></td><td><b>col_6_0_</b></td>
</tr>
<tr>
<td>1</td><td>CCC</td>
</tr>
<tr>
<td>1</td><td>BBB</td>
</tr>
<tr>
<td>2</td><td>AAA</td>
</tr>
</table>
</body>
</html>

So now I have only 2 unique IDs (instead of 3) to be passed as a parameter into actual data-query. I also attempted to give hint HINT_PASS_DISTINCT_THROUGH as described here https://vladmihalcea.com/jpql-distinct-jpa-hibernate/ but still I only get 2 aggregate root ids (hibernate-core being version 5.4.1.final). Any advice would be greatly appreciated!

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
theNikki1
  • 151
  • 1
  • 6

1 Answers1

1

Write a derived table for the first query:

select t.id 
from (
    select id, col1, col2
    from root_table
    where ...
    order by col1, col2
    fetch first 50 rows only
) t

Using the ids fetched from this query, you can use a second JPQL query to Join Fetch the root and the child entities as explained in this post.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • Great! I guess it should be select distinct(t.id) from ... but otherwise seems to be working. Now I only have to translate it to JPQL :) – theNikki1 Feb 28 '19 at 14:18
  • 1
    You cannot translate it to JPQL and you don't even have to. Just use a native SQL. Also, Distinct is not needed for this query as you are not joining anything. – Vlad Mihalcea Feb 28 '19 at 14:21
  • OK, as a follow-up question does JPA/Hibernate quarantee that results from the "second" query are in same order as passed-in-list-of-ids ? – theNikki1 Feb 28 '19 at 15:03
  • 1
    That's a good question. However, it's up to the database to enforce that rule as the query is just passed as-is to the DB. – Vlad Mihalcea Feb 28 '19 at 15:04
  • 1
    There seem to be all sorts of hacks to make sure that the second query will match the id order https://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause – Vlad Mihalcea Feb 28 '19 at 15:15
  • There's a problem. I need to be able to sort by the joined entity's attribute. If I give select distinct (t.id)... I'll get back IDs sorted by their natural order. If I don't give it I get duplicates – theNikki1 Mar 01 '19 at 12:01
  • 1
    Use an order by clause in the second JPQL query then. – Vlad Mihalcea Mar 01 '19 at 12:23
  • Unfortunatelly when a join is required, select distinct(t.id).. leads to "pagination page size" problem as it might reduce the actual rows returned, while without distinct-keyword we may have duplicates.. – theNikki1 Mar 01 '19 at 14:54
  • What do you mean by "pagination page size" problem? Anyway, it makes no sense to discuss this problem in StackOverflow comments. If you think you have a problem, send me a Pull Request on my [high-performance-java-persistence GitHub](https://github.com/vladmihalcea/high-performance-java-persistence) repository by adding a clone to the [`testFetchAndPaginateWithTwoQueries`](https://github.com/vladmihalcea/high-performance-java-persistence/blob/88102125ca8ac9b22acf369668f05507f004562d/core/src/test/java/com/vladmihalcea/book/hpjp/hibernate/fetching/pagination/PaginationTest.java#L184) test. – Vlad Mihalcea Mar 01 '19 at 15:13
  • The problem would be apparent if the where-part or ordering would require something from the PostComment (with derived table) – theNikki1 Mar 01 '19 at 15:42
  • Looking forward to seeing your Pull Request then. – Vlad Mihalcea Mar 01 '19 at 15:54
  • I needed to fork that git-repository, but here PR https://github.com/tapioNiemela80/high-performance-java-persistence/pull/1 – theNikki1 Mar 01 '19 at 17:57
  • I'll take a look when I have some time. – Vlad Mihalcea Mar 01 '19 at 18:07