Hasura aggregation in related tables

Question

I was evaluating Hasura GraphQL engine + Postgres on Heroku and have run into a situation. I have 2 simple tables as shown below; Employees

Payrolls

Employees table and Payrolls have a foreign key employees.ID -> payrolls.employee_id Employees table has a foreign key with itself employees.manager_id -> employees.ID

I have gone ahead and "auto-tracked" all the relationships in Hasura.

What I want to calculate is the "sum of salaries" for all employees reporting. But, when I'm using the Hasura explorer to form the GQL query, I'm not able to find the "sum" aggregation under the "managed_employees_aggregate" subquery.

The expected output is

[
{
    "full_name": "anuj gupta",
    "total_reportee_salary": 4000
},
{
    "full_name": "sowmya",
    "total_reportee_salary": 2000
},
    ... "total_reportee_salary" for everyone else is 0
]

Any suggestions or references is really appreciated.

I have a Hasura query like this till now. Don't know how to proceed further as I don't see a way to sum up the salaries of the "managed_employees". query MyQuery { employees { full_name managed_employees_aggregate { aggregate } } } — Deepak Champatiray, Jun 15 '21 at 09:13
I don't think you can achieve that with a simple query. Instead you can create a view for it. — Muluken Getachew, Jun 15 '21 at 11:30

score 1 · Answer 1 · answered Jun 16 '21 at 13:55

Answering my own question

As suggested by @Ambassel in the comments I ended up creating a view

create view reportee_total_vw AS
select
    employees.manager_id,
    SUM(payrolls.salary)
from
    employees,
    payrolls
where
    payrolls.employee_id = employees."ID"
group by
    employees.manager_id

Next I created a relationship named "reportee" that bound the "ID" from employees table with the "manager_id" from the view.

After that I could issue the underlying GQL query to get the result I wanted (although not in the exact format, but I can live with that :))

{
  employees {
    full_name
    reportee {
      total_reportee_salary:sum
    }
  }
}

Hasura aggregation in related tables

1 Answers1