Spring Data JPA规范-不同+按连接列 [英] Spring Data JPA Specifications - Distinct + order by column in join

问题描述

我有一些要与和"组合的规范:

I have some specifications that I am combining with "and":

Specification.where(predicate1).and(predicate2).and(predicate3);

其中一个具有不同的设置:

One of them has distinct set :

query.distinct(true);

另一人通过联接中的一列进行订购.

Another one makes an order by on a column that is in a join.

query.orderBy(builder.desc(bJoin.get("orderbyColumn")));

此操作失败，并显示 SQLGrammarException ，指出按列排列的顺序应不同.

This fails with a SQLGrammarException stating that order by column should be in distinct.

因此，基本上我们有实体A，主要实体和一些嵌套实体B，我们从A中选择但要按B排序，并且在生成的sql中，它仅从A中选择列.的工作(=也使它也从B中选择)是用提取来代替连接:

So basically we have entity A, the main entity, and some nested entity B, we select from A but want to order by B, and in generated sql it only selects columns from A. The only way I found to make it work (= making it select from B as well) is to replace the join by a fetch :

Fetch < A, B > bFetch = root.fetch("joinCol", JoinType.INNER);
Join < A, B > bJoin = (Join < A, B > ) bFetch;

可以工作一段时间，正在H2中进行本地测试，但是一段时间后开始出现另一个错误:

that worked for some time, was testing locally in H2, but then after some time started getting another error :

org.hibernate.QueryException:查询指定了联接获取，但是获取的关联的所有者不在选择列表中

我在本地指向H2时以某种方式解决了这一问题，方法是要求某些列不为null，但是在使用PostgreSQL的真实服务器中，它根本无法工作，并且在存在提取的所有情况下都会出现该错误.

I solved it somehow in my local pointing to H2 by requiring some columns to not be null, but in real server using PostgreSQL, it's not working at all, getting that error for all cases when a fetch is present.

我的问题是:在未获取的嵌套实体上使用 distinct 和 orderby 的正确方法是什么?我的 fetch 解决方案是否可以解决，是否只需要修复(如果可以，怎么办?)，或者我应该完全选择另一种选择?

My question is : what is the right way to use distinct along with orderby on a nested entity that is not fetched? Is my solution with fetch ok and it just needs to be fixed (and if so how?) or I should go for another option entirely?

对于实际查询，我正在使用此方法:

For the actual query I am using this method :

findAll(Specification<>, Pageable)

是否没有办法使与众不同将整个查询按顺序排列(某种子查询?)并绕过所有这些噩梦?让它生成这样的查询:

Isn't there a way to have distinct wrapping the whole query with order by (some sort of subquery?) and bypassing all this nightmare? Have it generate a query like this:

select distinct colA1, colA2, coAl3 from (select colA1, colA2, coAl3 

from A inner join B b on ........ order by b.colB1)

我是否需要将规范转换为手动谓词并对其进行其他处理以尝试解决我的问题(某种混合方法)?

Do I need to convert my specification to predicate manually and do something else with it to try to solve my issues (some kind of hybrid approach)?

任何建议将不胜感激.

推荐答案

我遇到了相同的错误，但实际上不是错误:)

I encountered same error but actually it was not error :)

findAll(Specification<> ;, Pageable)，此方法引发2个不同的查询.

findAll(Specification<>, Pageable) this method throws 2 different queries.

第一个是计数查询，您必须要小心.其次是行查询的实际执行位置.

First one is count query where you have to be careful. Second is the rows query where you actually did it.

您可以使用以下代码检查查询类型

You can check the query type with code below

if (query.getResultType() != Long.class && query.getResultType() != long.class){
   root.fetch("entity1");
} 

这篇关于Spring Data JPA规范-不同+按连接列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！