Search code examples
jpql

JPQL: SELECT b, count(ts) FROM Branch b JOIN b.tourScheduleList WHERE ts.deleted = 0

I get the desired result here

SELECT b, count(ts) FROM Branch b JOIN b.tourScheduleList ts WHERE ts.deleted =  0 GROUP BY b.id ORDER BY b.name ASC

b1 | 2
b2 | 1

but then I need to get the count of ts.tourAppliedList so I updated the query to

SELECT b, count(ts), count(ta) FROM Branch b JOIN b.tourScheduleList ts JOIN ts.tourAppliedList ta WHERE ts.deleted =  0 GROUP BY b.id ORDER BY b.name ASC

which resulted to 

b1 | 3 | 3
b2 | 2 | 2

the result is wrong. I don't know why count(ts) is equal to count(ta)

I tried returning ts then just do a count later but it's returning all its content without considering the ts.deleted = 0

SELECT b, ts FROM Branch b JOIN b.tourScheduleList ts WHERE ts.deleted =  0 GROUP BY b.id ORDER BY b.name ASC

then in the view I just #{item.ts.tourAppliedList.size()} it's not considering the ts.deleted = 0

Solution

  • The problem is your expectation is wrong. This Join will give you:

    b1 | ts1 | ta1
b1 | ts1 | ta2
b1 | ts2 | ta3
b2 | ts3 | ta4
b2 | ts3 | ta5

    Or something along this line... What happens when you group and count those rows? Simple you have 3 entry for b1 and 2 for b2. What you need there is count(distinct ts) Since there are multiple ts for every different ta you would then find a difference

    P.s. i dont know if jpql permit a count(distinct ), if thats the case you better do two query and count ts with the join only on ts and then ta with the join on ts and ta