Search code examples
postgresqlhibernatehql

HQL update column with COUNT

I'm working with Hibernate thus HQL, linked to a PostgreSQL database.
I have a table users and a table teams that are linked with a ManyToMany condition throught the table teams_users.
I'd like to update or select the table team so the property usersCount takes the amount of users belonging to a team.

I do not want to add a @Formula to my Entity Class, because I don't want it to be executed all the time, that's too wastful on big JOIN FETCH query where I do not need the count.

I other words, I'd like to find the HQL equivalent of the following PSQL query

UPDATE teams t 
SET users_count = (SELECT COUNT(ut.*)
    FROM teams t1 
    LEFT JOIN teams_users tu 
        ON t1.id = tu.team_id 
    WHERE t1.id = t.id 
    GROUP BY t1.id);

OR

An equivalent of the following

SELECT t.*, count(tu.*) AS users_count 
FROM teams t 
LEFT JOIN teams_users tu 
    ON t.id = tu.team_id 
GROUP BY t.id;

Unsuccessful tries (to get an idea)

UPDATE Team t SET
    t.usersCount = COUNT(t.users)

UPDATE Team t SET
    t.usersCount = (SELECT COUNT(t1.users) FROM Team t1 WHERE t1.id = t.id)

SELECT t, count(t.users) AS t.usersCount
FROM Team t
Solution

  • I've found the solution for the UPDATE query.
    It simply is

    UPDATE Team t 
SET t.usersCount = (SELECT COUNT(u) from t.users u)

    It makes an extra join on the table users whilst the table teams_users would be enought but well... It works.

    If anyone has ths solution for the SELECT one, I'm still curious !