How to get the sum of related model results using Lucid Query builder

Question

I am using AdonisJs and I am trying to fetch all projects and the sum of the estimate time of their tasks.

My code looks like this

const entities = await Project.query()
  .with('task', (builder) => {
    builder.select('project_id')
    builder.sum('estimate as estimateTime')
    builder.groupBy('project_id')
  })
  .where({ account_id: accountId })
  .fetch()

What I expect is for the query to look like this: SELECT "project_id", SUM("estimate") AS "estimateTime" FROM "project_tasks" WHERE "project_id" IN (5,6,1,2) GROUP BY "project_id"

but the actual query is: SELECT "project_id", SUM("estimate") AS "estimateTime" FROM "project_tasks" WHERE "project_id" = 5 AND "project_id" IN (5,6,1,2) GROUP BY "project_id"

It seems that it adds this additional clause "project_id" = 5 when I add builder.sum('estimate as estimateTime') to the query.

I know I can build this query by myself, but I want to get advantage of the other relationships of the Project Model.

Any ideas how can I get around this?

Answer 1

In order to get the sum in relation you will need the Database helper. So your query should look like this:

const entities = await Project.query()
  .with('task', (builder) => {
    builder.select(Database.raw('sum(estimate) as estimate'))
    .select('project_id')
    .groupBy('project_id')
  })
  .where({ account_id: accountId })
  .fetch()