CODEWITHSUNDEEP
javasqlhibernatejpahql
Sverro2
Sverro2

Reputation: 163

JPA Hibernate JOIN ... ON ... AND

I use the following query:

SELECT * FROM phaseinproject as pip JOIN projectinrelease pir 
ON pip.projectInRelease_id = pir.id
JOIN releaseperiod as rp ON pir.release_id = rp.id
JOIN releasestructure as rs ON rs.id = rp.releaseStructure_id
JOIN phaseinreleasestructure as pirs ON pirs.releaseStructure_id = rs.id
JOIN releasephase as rlp ON rlp.id = pirs.phase_id 
AND rlp.id = pip.phase_id

This query works totally fine. I get three results (the amount I expect).

I convert this query to the following HQL query:

            TypedQuery<PhaseInProjectOverview> findPhasesInRelease = em.createQuery("SELECT NEW nl.dashboard.dto.out.PhaseInProjectOverview(phaseInProject.id, phase.name, phaseInProject.startDate, phaseInProject.plannedEndDate, phaseInProject.endDate) FROM PhaseInProject phaseInProject "
                + "JOIN phaseInProject.projectInRelease projectInRelease "
                + "JOIN projectInRelease.release release "
                + "JOIN release.releaseStructure releaseStructure "
                + "JOIN releaseStructure.phaseInReleaseStructures phaseInReleaseStructure "
                + "JOIN phaseInReleaseStructure.phase phase "
                + "WHERE release.id = :releaseId ORDER BY phaseInReleaseStructure.position, phaseInProject.startDate", PhaseInProjectOverview.class);
        findPhasesInRelease.setParameter("releaseId", releaseId);
        return findPhasesInRelease.getResultList();

No matter what I try: I get 6 results, because HQL does not seem to support the "JOIN ... ON ... AND ..." sql syntax.

Does anyone know how to solve this problem?

edit: I added my own answer with the used solution. Thank you all for the answers/pointers.

Upvotes: 1

Views: 613

Answers (2)

Sverro2
Sverro2

Reputation: 163

I solved my problem with an extra WHERE clause:

phase.id = phaseInProject.phase.id

Now I get the results I was expecting. The 'WITH' keyword does not seem to work with multiple entities. When I try, I get an exception:

HQL error: with-clause referenced two different from-clause elements

When trying to use the 'ON' syntax like JOIN phaseInReleaseStructure.phase phase ON phase.id = phaseInProject.id, I get another error:

unexpected token: ON near line 1, column 473

Upvotes: 0

Thomas
Thomas

Reputation: 88757

Try the with keyword: phaseInReleaseStructure.phase phase WITH phase.id = phaseInProject.phase_id - this should result in SQL like releasephase as rlp ON rlp.id = pirs.phase_id AND rlp.id = pip.phase_id

Alternatively just add that condition in the where clause: 

... WHERE release.id = :releaseId AND phase.id = phaseInProject.phase_id ...

Upvotes: 1

Related Questions