I'm trying to convert the following SQL query in QueryDSL (JPA, Hibernate provider, Oracle database):
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;
My java code:
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]));
It compiles fine but I get a runtime Exception
ORA-00904: "COMPANY0_"."ID": invalid identifier
This is the query generated according to the Hibernate log output:
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
If I run this query by hand in Oracle I get the same error. I don't understand where the two useless joins with company (company3_ and company4_) come from and the whole where clause. If I remove these sections it works in Oracle again.
Calling toString() on the query obj outputs
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
The QCompanyMapping class is defined as
public class QCompanyMapping extends EntityPathBase<CompanyMapping> {
// ..
public final QCompany companyByCompanyIdSource;
public final QCompany companyByCompanyIdTarget;
// ..
}
Possible alternative solution? There's an alternative equivalent SQL query that seems to be even more elegant and might translate correctly throught 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
However I don't know how to express it in QueryDSL.
