Extracting multiple result with child tables in PostgreSQL and HQL

Question

In the following scenario, there are 3 tables in a PostgreSQL database (tables and values are simplified for clarity). I am trying to get the following values:

• sum of the amounts for records (101, 102, 103) in the parent table for 2020 year (1300)
• count of the corresponding records in parent-a (4)
• count of the corresponding records in parent-b (2)

parent:

| id   |  name     | amount    | year  |
----------------------------------------
| 101  |  Henry    | 300       | 2020  |
----------------------------------------
| 102  |  Carol    | 100       | 2020  |
----------------------------------------
| 103  |  Tom      | 900       | 2020  |
----------------------------------------
| 104  |  Fredrick | 150       | 2022  |
----------------------------------------
| 105  |  Mary     | 180       | 2023  |
----------------------------------------

child-a:

| id   |  parent_id  | value         |
--------------------------------------
| 1    |  101        | value-a      |
--------------------------------------
| 2    |  101        | value-b       |
--------------------------------------
| 3    |  101        | value-c       |
--------------------------------------
| 4    |  102        | value-d       |
--------------------------------------

child-b:

| id   |  parent_id  | value         |
--------------------------------------
| 1    |  101        | value-x       |
--------------------------------------
| 2    |  102        | value-y       |
--------------------------------------
| 3    |  105        | value-z       |
--------------------------------------
| 4    |  106        | value-t       |
--------------------------------------

For this, I created the following query (I will revert it to HQL). Because, when I join tables, the amount values are multiplied and when I group records in left join via a sub clause, it gives error in HQL as HQL does not support sub clause in JOIN:

select (
       COALESCE(sum(p.amount), 0),
       (
          select count(ca) from child_a ca left join
             parent p on ca.parent_id = p.id
       ),
       (
          select count(cb) from child_b cb left join
             parent p on cb.parent_id = p.id
       )
    ) from parent p where p.year = 2020

Is there a better way to improve this that also works qith HQL as well?

Answer 1

Try this:

I suppose you use three objects named Parent, ChildA, ChildB to map the three Postgres table

SELECT SUM(p.amount), 
    (SELECT COUNT(1) FROM ChildA ca where ca.parentId = p.record) as childAElement, 
    (SELECT COUNT(1) FROM ChildB cb where cb.parentId = p.record) as childBElement
FROM Parent p
WHERE p.record in (101, 102, 103) and p.year = 2020