I have an entity and I want it to have an attribute of the count of the children. Let's say I have these two tables:
create table father
(
id int,
name
);
create table child
(
id int,
father_id int
);
I want the father entity in Hibernate to have an attribute like this:
private int countChildren;
From SQL perspective, it is easy:
SELECT f.id, f.name, count(father_id) as count_children
FROM father f JOIN child c ON f.id = c.father_id
GROUP BY f.id, f.name;
Easy and efficient. But, since I want to have it as an Hibernate attribute of the entity, the closest I found is:
@Formula("(select count(*) from child c where c.father_id = id)")
private int countChildren;
However, behind the scenes it is performed as a Scalar sub-query, where the access to the child table is per every father record - highly inefficient (some databases like Oracle do some optimization for these kind queries, but I don't want to rely on that, and it is still not optimal compared to the SQL version with GROUP BY).
Is there an Hibernate way to solve this one? One that behind the scenes is performed in a similar way to the above GROUP BY example?