CODEWITHSUNDEEP
javajpajpql
Channa
Channa

Reputation: 5233

How to write complex join query with JPA

How to write this kind of complex join query with JPA, Some of syntax I have denoted below not work with JPA. I have used them for demonstrate way that sql query should build, so sorry about that. 

SELECT Result1.name1, Result1.count1, Result2.name2, Result2.count2 FROM (
    SELECT 
        taskOne.user.name AS name1,
        COUNT(taskOne.taskId) AS count1
    FROM
        Task AS taskOne
    INNER JOIN
        taskOne.defect AS defectOne
    WHERE (
        defectOne.defId = taskOne.defect.defId
        AND
        taskOne.taskCategory.tcaId = 1
    )
    GROUP BY
        taskOne.user.usId
) AS Result1
FULL JOIN (
    SELECT
        taskTwo.user.name AS name2,
        COUNT(taskTwo.taskId) AS count2
    FROM Task AS taskTwo
    INNER JOIN taskTwo.defect AS defectTwo
    WHERE (
        defectTwo.defId = taskTwo.defect.defId
        AND
        taskTwo.taskCategory.tcaId = 2
    )
    GROUP BY taskTwo.user.usId
)
AS Result12
WHERE Result1.name1 = Result12.name2

Upvotes: 1

Views: 2691

Answers (1)

James
James

Reputation: 18379

JPQL is desired for selecting objects. Your query seems incredibly complex, I would recommend a native SQL query, or simplifying it.

JPQL does not support sub selects in the from clause.

Upvotes: 3

Related Questions