I’m seeking guidance on how to aggregate (sum) a field across related models in my schema:
<code>model PayrollBatch {
id Int @id @default(autoincrement())
payrolls Payroll[]
}
model Payroll {
id Int @id @default(autoincrement())
employee Employee @relation(fields: [employeeId], references: [id])
employeeId Int
payrollBatch PayrollBatch @relation(fields: [payrollBatchId], references: [id])
payrollBatchId Int
incomeEntries IncomeEntry[]
}
model IncomeEntry {
id Int @id @default(autoincrement())
payroll Payroll @relation(fields: [payrollId], references: [id])
payrollId Int
amount Float
}
</code>
<code>model PayrollBatch {
id Int @id @default(autoincrement())
payrolls Payroll[]
}
model Payroll {
id Int @id @default(autoincrement())
employee Employee @relation(fields: [employeeId], references: [id])
employeeId Int
payrollBatch PayrollBatch @relation(fields: [payrollBatchId], references: [id])
payrollBatchId Int
incomeEntries IncomeEntry[]
}
model IncomeEntry {
id Int @id @default(autoincrement())
payroll Payroll @relation(fields: [payrollId], references: [id])
payrollId Int
amount Float
}
</code>
model PayrollBatch {
id Int @id @default(autoincrement())
payrolls Payroll[]
}
model Payroll {
id Int @id @default(autoincrement())
employee Employee @relation(fields: [employeeId], references: [id])
employeeId Int
payrollBatch PayrollBatch @relation(fields: [payrollBatchId], references: [id])
payrollBatchId Int
incomeEntries IncomeEntry[]
}
model IncomeEntry {
id Int @id @default(autoincrement())
payroll Payroll @relation(fields: [payrollId], references: [id])
payrollId Int
amount Float
}
Currently, I iterate through payrolls
to sum the amount
field in IncomeEntry
for a specific PayrollBatch
. Here’s my approach:
<code>
const payrollBatch = await prisma.payrollBatch.findUnique({
where: { id: batchId },
include: {
payrolls: {
include: {
incomeEntries: true,
},
},
},
});
const totalAmount = payrollBatch.payrolls.reduce((acc, payroll) => {
return acc + payroll.incomeEntries.reduce((acc, entry) => acc + entry.amount, 0);
}, 0);
</code>
<code>
const payrollBatch = await prisma.payrollBatch.findUnique({
where: { id: batchId },
include: {
payrolls: {
include: {
incomeEntries: true,
},
},
},
});
const totalAmount = payrollBatch.payrolls.reduce((acc, payroll) => {
return acc + payroll.incomeEntries.reduce((acc, entry) => acc + entry.amount, 0);
}, 0);
</code>
const payrollBatch = await prisma.payrollBatch.findUnique({
where: { id: batchId },
include: {
payrolls: {
include: {
incomeEntries: true,
},
},
},
});
const totalAmount = payrollBatch.payrolls.reduce((acc, payroll) => {
return acc + payroll.incomeEntries.reduce((acc, entry) => acc + entry.amount, 0);
}, 0);
Is there a more efficient way to achieve this using Prisma’s query capabilities? Can I utilize aggregation directly on IncomeEntry.amount
, or query all IncomeEntry
records related to a specific PayrollBatch
?
References:
- Prisma Documentation on Aggregation