How to fix the “ORDER BY items must appear in the select list if SELECT DISTINCT is specified” with JPA and Hibernate

问题: 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 Cri...

问题:

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!


回答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 fron this query, you can use a second JPQL query to Join Fetch the root and the child entities as explained in this article.

  • 发表于 2019-03-02 02:04
  • 阅读 ( 310 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除