CODEWITHSUNDEEP
postgresqlgraphqlhasura
Deepak Champatiray
Deepak Champatiray

Reputation: 121

Hasura aggregation in related tables

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

Payrolls Payroll Table

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.

Upvotes: 0

Views: 1376

Answers (1)

Deepak Champatiray
Deepak Champatiray

Reputation: 121

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
    }
  }
}

Upvotes: 1

Related Questions