1

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?

SockworkOrange
  • 185
  • 2
  • 12
  • Do I understand you correctly. You would like to have a LAZY loading behaviour for the formula. If I tell you how to make it lazy would this be an answer ? – Alexander Petrov May 23 '19 at 12:26
  • 1
    Possible duplicate of [Hibernate count collection size without initializing](https://stackoverflow.com/questions/2913160/hibernate-count-collection-size-without-initializing) – Udith Gunaratna May 23 '19 at 12:26
  • Alexander - not exactly. I know how to have a @OneToMany relationship and have it initialized - but in that case the query executed retrieves the objects from the child table, and I have to perform the count in the code. I have no need for the actual objects of the child table, I just need their count per parent row. Udith - Looks interesting I'll check it out. – SockworkOrange May 23 '19 at 12:36
  • Udith - Yeah, it is actually not helpful. I don't want to have a lazy initialization: it kinda misses the point for me. I want to always have that attribute (perform the count for each of the parent rows) but in the same query. – SockworkOrange May 23 '19 at 13:45

0 Answers0