Ecto Query Aggregation returns wrong values

Hey, I would love to perform some aggregation using ecto. I have the following tables

  1. Payrolls
  2. Payslips - Associated with a payroll
  3. Allowances - Saves the name and description of an allowance
  4. Deductions - Saves the name and description of a deduction i.e pension, other
  5. PayslipAllowances - Links a payslip and an allowance has an amount field which stores the value of the specified allowance
  6. PayslipDeductions - Links a payslip and a deduction

Both PayslipAllowances and PayslipDeductions have a field named amount which stores the integer value that is used to sum.

I would like to get the value of other deductions, payroll status, the date of creation for the payroll, the user who created the payroll, gross salary(sum of all allowances), the Payroll’s and total deductions

I wrote the query below but it returns the wrong values.

from(payroll in Payroll, 

join: payslip in Payslip,
join: payslip_allowance in PayslipAllowance,
join: payslip_deduction in PayslipDeduction,
join: deduction in Deduction,
join: allowance in Allowance,

on: payroll.id == payslip.payroll_id,
on: payslip.id == payslip_deduction.payslip_id,
on: payslip.id == payslip_allowance.payslip_id, 
on: allowance.id == payslip_allowance.allowance_id,
on: deduction.id == payslip_deduction.deduction_id, 

select: %{
  other_deductions: fragment("SUM(DISTINCT CASE WHEN ? = ? THEN ? ELSE 0 END)", deduction.name, "Other", payslip_deduction.amount),
  month: payroll.date_to,
  status: payroll.status,
  gross: fragment("SUM(?)", payslip_allowance.amount),
  deductions: fragment("SUM( ?)", payslip_deduction.amount)

},
group_by: [payroll.date_to, payroll.status, payroll.id, payslip.id]
)

Are you sure this generates the SQL query you’re looking for? The order of join: + on: keywords matters.

Try using Repo.to_sql(:all, query) so see the SQL being run and adjust as needed.

1 Like

You seem to have reversed the order on the on:s

Use the format (like @LostKobrakai suggested):

join: payslip_allowance in PayslipAllowance, on: payslip.id == payslip_allowance.payslip_id,
1 Like