Using join fetch and group by in HQL query

Question

I have a Report entity that has a list of ReportedTime as @OneToMany relation. Each ReportedTime has a project, and Project has a manager.

The query itself (shown at the bottom) works fine. The problem is I need to now map these entities to DTOs and for that mapping purpose I need to know the projects that are in each ReportedTime in each Report. So I'm facing an n+1 problem, and I wanted to join fetch the reportedTimes and projects:

 @Query(value = "select " +
            "t as timesheetReport, " +
            "FUNCTION('string_agg', tlpa.username, ',') as projectManagersUsernames, " +
            "case when " +
            "   FUNCTION('string_agg', tlpa.username, ',') like concat('%', :username, '%') " +
            "then true else false " +
            "end as assigned " +
            "from TimesheetReport t " +
            "left join fetch t.reportedTimes rt " +
            "left join fetch rt.project p " +
            "left join p.teamLeaderAssignments tlp " +
            "left join tlp.account tlpa " +
            "group by t " +
            "having sum(rt.workTime)>0 " +
            "order by assigned desc ")
    List<IReportWithManagers> findAllWithManagers(String username, Pageable pageable);

but I get this error:

org.postgresql.util.PSQLException: ERROR: column "reportedtimes1_.id" must appear in the GROUP BY clause or be used in an aggregate function

I tried to see the sql query in the logs and it seems that in this scenario Hibernate also puts the reported_time.id and project.id in the select clause but does not know how to group it? Is there way around this?

score 0 · Answer 1 · answered Nov 07 '20 at 18:17

You're getting the error because, of course, when fetching the joined entities, you're suddenly attempting to select a whole bunch of new columns that are neither included in GROUP BY nor aggregated.

To be able to select columns from rt and p, you would need to GROUP BY t, rt, p, which unfortunately (as far as I understand) changes the semantics of the query. I'd try adding separate JOINS for fetching and grouping:

left join t.reportedTimes rt
left join rt.project p
left join fetch t.reportedTimes rt2
left join fetch rt.project p2
left join p.teamLeaderAssignments tlp
left join tlp.account tlpa
group by t, rt2, p2
having sum(dt.workTime)>0
order by assigned desc

The problem with the above is, duplicate rows will now enter the aggregation, so perhaps you could use ARRAY_TO_STRING(ARRAY_AGG(DISTINCT tlpa.username, ',') or select the array and join the strings in Java (not sure if Hibernate supports selecting PostgreSQL arrays out of the box, though, you'd need to check).

Please be advised that I haven't tried the above solution, so I cannot guarantee it will work.

On second thought, I'm not so sure `GROUP BY t, rt, p` changes the semantics. What is `dt` in `HAVING sum(dt.workTime)>0`? Was the alias supposed to be `rt` instead? — crizzis, Nov 07 '20 at 18:27
Yes, `dt` should be `rt`, I will edit that. When I try the double join as shown here I get `org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list `. I tried just grouping by t, rt, p but then I get duplicate entries for each reportedTime in each report. I would need an equivalent of `distinct on()` in hibernate so I could use distinct on report.id, but hibernate does not support `disctinct on ()` — Matt, Nov 08 '20 at 12:11
Well, in that case, I'm afraid you'll have to use a native query — crizzis, Nov 08 '20 at 17:07

Using join fetch and group by in HQL query

1 Answers1