Execute left join query in JPA

Question

I have two tables, one for party and one for scorecard template mapping. The scorecard template mapping table has a foreign key back to the party (on id). I want to find a list of all of the parties that have scorecard template mapping details.

But I get an error which says :

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: on near line 1, column 172 [select new ScorecardTemplateMapping(p,temMap.scoTemplate,temMap.wrkFlwTemplate) from com.kpisoft.common.web.domain.Party p left outer join ScorecardTemplateMapping temMap on temMap.organization.id=p.id and temMap.gradeType.id=:gradeType where p.organization.organizationTypeId=:orgType and p.clientId=:clientId order by p.organization.name]

This is my query:

Query q = entityManager.createQuery("select new ScorecardTemplateMapping(p,temMap.scoTemplate,temMap.wrkFlwTemplate) from Party p left outer join ScorecardTemplateMapping temMap on temMap.organization.id=p.id and temMap.gradeType.id=:gradeType where p.organization.organizationTypeId=:orgType and p.clientId=:clientId order by p.organization.name");

I have no idea why this isn't working. Please help!

Answer 1

Error message about syntax error is quite clear:

unexpected token: on

There is no support to make join with ON [conditional] in JPQL (ON is not reserved word). How joins are made in JPQL, is told for example here. It boils down to that you have to present join condition in where clause.