QueryDSL联接生成无效SQL

Question

我正在尝试转换QueryDSL (JPA，Hibernate provider，Oracle database)中的以下SQL查询:

select c.id
      , c.name
      , count(coalesce(s.company_id_source, t.company_id_target))
  from company              c
  left join company_mapping s on(s.company_id_source = c.id)
  left join company_mapping t on(t.company_id_target = c.id)
 group 
    by c.id
      ,c.name;

我的java代码：

QCompany company = new QCompany("company");
QCompanyMapping companyMappingSource = new QCompanyMapping("companymappingS");
QCompanyMapping companyMappingTarget = new QCompanyMapping("companymappingT");
JPAQuery query = new JPAQuery(entityManager);
query = query.from(company)
query = query.leftJoin(company.companyMappingsForCompanyIdSource, companyMappingSource);
query = query.leftJoin(company.companyMappingsForCompanyIdTarget, companyMappingTarget);
List<Expression<?>> outPaths = new ArrayList<Expression<?>>();
// add c.id and c.name to outPaths - omitted
outPaths.add(companyMappingSource.companyByCompanyIdSource.count().add(companyMappingTarget.companyByCompanyIdTarget.count()));
// add the group by clause - omitted
List<Object[]> rows = query.listDistinct( outPaths.toArray(new Expression<?>[0]));

它编译得很好，但我得到了一个运行时异常

ORA-00904：“公司_”。“ID”：无效标识符

这是根据Hibernate日志输出生成的查询：

select distinct company0_.ID as col_0_0_, company0_.NAME as col_1_0_, 
count(companymap1_.COMPANY_ID_SOURCE)+count(companymap2_.COMPANY_ID_TARGET) as col_2_0_
from COMPANY company0_ 
left outer join COMPANY_MAPPING companymap1_ on company0_.ID=companymap1_.COMPANY_ID_SOURCE
, COMPANY company3_
left outer join COMPANY_MAPPING companymap2_ on company0_.ID=companymap2_.COMPANY_ID_TARGET
, COMPANY company4_ 
where companymap1_.COMPANY_ID_SOURCE=company3_.ID and companymap2_.COMPANY_ID_TARGET=company4_.ID 
group by company0_.ID , company0_.NAME

如果我在Oracle中手工运行这个查询，我会得到同样的错误。不明白company的两个无用连接(company3_和company4_)从何而来，以及整个where子句。如果我删除这些部分，它可以在Oracle中再次工作。

对查询obj输出调用toString（）

select company
from Company company
  left join company.companyMappingsForCompanyIdSource as companymappingS
  left join company.companyMappingsForCompanyIdTarget as companymappingT
where upper(company.name) like ?1 escape '!'
group by company.id, company.name

Q公司映射类定义为

public class QCompanyMapping extends EntityPathBase<CompanyMapping> {
// ..
    public final QCompany companyByCompanyIdSource;

    public final QCompany companyByCompanyIdTarget;
// ..
}

可能的替代解决方案？还有一个替代的等效SQL查询，它看起来更优雅，可以通过Hibernate正确转换：

select c.id
,      c.name
,      count(cm.id)
from   company c
left join company_mapping cm on c.id in (cm.company_id_source, cm.company_id_target)
group by c.id, c.name

但是我不知道如何用QueryDSL来表达。

Answer 1

连接后应使用别名路径。

这些可能会触发额外的连接

outPaths.add(QCompanyMapping.companyMapping.companyByCompanyIdSource.count().add(
         QCompanyMapping.companyMapping.companyByCompanyIdTarget.count()));

Answer 2

如果我改变

1. “公司映射来源.companyByCompanyIdSource.count（）”到“公司映射源.count（）”
2. “公司映射目标.companyByCompanyId目标.count（）”到“公司映射目标.count（）”

在第一个查询的java代码中，它可以工作：

outPaths.add(companyMappingSource.count().add(companyMappingTarget.count()));