Filter on relation aggregate result

[IlyaSemenov]

I have users and each user has accounts, and each account has balance. How do I select users for whom sum of their account balances is positive?

await db.user.insertMany([
  { name: "Alice", accounts: { create: [{ balance: 10 }, { balance: 20 }] } },
  { name: "Bob" },
])

await db.user.select("name", {
  balance: q => q.accounts.sum("balance"),
}).having(
  q => q.accounts.sum("balance").gt(0),
)
// SELECT "user"."name", "balance".r "balance" FROM "user" LEFT JOIN LATERAL (SELECT sum("accounts"."balance") r FROM "account" AS "accounts" WHERE "accounts"."user_id" = "user"."id") "balance" ON true HAVING sum("balance".r) > $1 [0]
// Error: column "user.name" must appear in the GROUP BY clause or be used in an aggregate function

await db.user.select("name", {
  balance: q => q.accounts.sum("balance"),
}).where(
  q => q.accounts.sum("balance").gt(0),
)
// SELECT "user"."name", "balance".r "balance" FROM "user" LEFT JOIN LATERAL (SELECT sum("accounts"."balance") r FROM "account" AS "accounts" WHERE "accounts"."user_id" = "user"."id") "balance" ON true WHERE (SELECT sum("balance".r) > $1 FROM "account" AS "accounts" WHERE "accounts"."user_id" = "user"."id") [0]
// Error: aggregate functions are not allowed in WHERE

await db.user.select("name", {
  balance: q => q.accounts.sum("balance"),
}).whereExists(db.account, q => q.sum("balance").gt(0))
// SELECT "user"."name", "balance".r "balance" FROM "user" LEFT JOIN LATERAL (SELECT sum("accounts"."balance") r FROM "account" AS "accounts" WHERE "accounts"."user_id" = "user"."id") "balance" ON true WHERE EXISTS (SELECT 1 FROM LATERAL (SELECT sum("balance".r) > $1 FROM "account") "account") [0]
// Error: aggregate functions are not allowed in WHERE

await db.user.select("name", {
  balance: q => q.accounts.sum("balance"),
}).whereExists(
  q => q.accounts.sum("balance").gt(0),
)
// TypeError: Cannot read properties of undefined (reading 'joinedShapes')
//     at processJoinArgs (/Users/is/work/node_modules/.pnpm/pqb@0.40.3/node_modules/pqb/src/queryMethods/join/processJoinArgs.ts:131:13)

Playground

import process from "node:process"

import { createBaseTable, orchidORM, testTransaction } from "orchid-orm"

const BaseTable = createBaseTable({ snakeCase: true })

class UserTable extends BaseTable {
  override readonly table = "user"

  override columns = this.setColumns(t => ({
    id: t.serial().primaryKey(),
    name: t.varchar(),
  }))

  relations = {
    accounts: this.hasMany(() => AccountTable, {
      columns: ["id"],
      references: ["userId"],
    }),
  }
}

class AccountTable extends BaseTable {
  override readonly table = "account"

  override columns = this.setColumns(t => ({
    id: t.serial().primaryKey(),
    userId: t.integer().foreignKey("user", "id"),
    balance: t.decimal(),
  }))
}

const db = orchidORM(
  { databaseURL: process.env.DATABASE_URL, log: true },
  {
    user: UserTable,
    account: AccountTable,
  },
)

await testTransaction.start(db)

await db.$query`
create table "user" (
  id serial primary key,
  name varchar not null
);
create table "account" (
  id serial primary key,
  user_id int not null references "user" (id),
  balance decimal not null
);`

await db.user.insertMany([
  { name: "Alice", accounts: { create: [{ balance: 10 }, { balance: 20 }] } },
  { name: "Bob" },
])

await db.user.select("name", {
  balance: q => q.accounts.sum("balance"),
}).whereExists(
  q => q.accounts.sum("balance").gt(0),
)

await testTransaction.close(db)

[romeerez]

Apologies for the late reply, here you go:

console.log(
  await db.user
    .select("name", {
      balance: (q) => q.accounts.sum("balance"),
    })
    .where({ balance: { gt: 0 } }),
);
// -> [ { name: 'Alice', balance: '30' } ]

SQL:

SELECT "user"."name", "balance".r "balance"
FROM "user"
LEFT JOIN LATERAL (
  SELECT sum("accounts"."balance") r
  FROM "account" AS "accounts"
  WHERE "accounts"."user_id" = "user"."id"
) "balance" ON true
WHERE "balance".r > 0

[IlyaSemenov]

Thank you! I was actually re-reading through my question first and I was questioning myself, what if I simply went with where({ balance: ... }) ? And that was the answer indeed. Shouldn't be opening issues in the evening I suppose. :)