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:
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?
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