Question 318537 in PostgreSQL Question posted in
The official documentation can be found here.

Extracting multiple result with child tables in PostgreSQL and HQL

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?

Tags:

2

Answers


  1. 0

    Two left joins can compute the result. For example:

    select sum(s), sum(a.c), sum(b.c)
from (select id, sum(amount) as s from parent where year = 2020 group by id) p
left join (select parent_id as id, count(*) as c from child_a group by id
          ) a on a.id = p.id
left join (select parent_id as id, count(*) as c from child_b group by id
          ) b on b.id = p.id
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search