[Feature request]: parameterized computed fields — accept arguments in orderBy/where/select

[evgenovalov]

Feature request: let @computed fields take arguments (so you can sort/filter by a parameterized SQL value)

What I want

Today a @computed field is a fixed SQL expression. I'd like a computed field to accept
arguments, and to pass those arguments (as plain data) when I use the field in orderBy,
where, or select.

Because the arguments are plain data, they serialize fine (JSON / superjson). So a frontend can
drive the sort/filter through the auto-generated CRUD API — no custom endpoint, no raw SQL — and
it all stays in one query with access policies and result types intact.

The problem (concrete)

A product list shows one column per "tag category". Clicking a column header should sort products by
the name of their tag in that category — e.g. string_agg(tag.name) where tag.category_id = 5.
The category id is chosen by the user at runtime.

I can't express this today:

• Computed fields can be used in orderBy, but they take no arguments — so the runtime
  categoryId can't get in. (Categories are user data, so "one computed field per category" isn't an
  option.)
• $qb / raw SQL can do it, but bypass access policies and lose the typed result.
• A plugin (onKyselyQuery) can do it, but it doesn't receive the ORM arguments, so the
  categoryId has to be smuggled in via AsyncLocalStorage + hand-written SQL-AST editing.

Every workaround gives up the "one policy-checked, typed query" that the ORM is for.

Proposal

Allow a computed field to declare arguments, and pass them where the field is used.

Small example — sort users by how many posts they made since a date:

model User {
  id    Int    @id
  posts Post[]
  // a computed field that takes an argument
  recentPostCount(since: DateTime): Int @computed
}

// implementation, written once at client construction
computedFields: {
  User: {
    recentPostCount: (eb, { since }) =>
      eb.selectFrom('Post')
        .whereRef('Post.authorId', '=', eb.ref('User.id'))
        .where('Post.createdAt', '>=', since)
        .select(({ fn }) => fn.countAll<number>().as('v')),
  },
},

// usage — `args` is plain data, so this whole object can come from a client
await db.user.findMany({
  orderBy: { recentPostCount: { args: { since: '2024-01-01' }, sort: 'desc' } },
  take: 20,
});

The real use case — sort products by their tag name in a chosen category:

model ProductSite {
  id   Int          @id
  tags ProductTag[]
  tagNameInCategory(categoryId: Int): String? @computed
}

computedFields: {
  ProductSite: {
    tagNameInCategory: (eb, { categoryId }) =>
      eb.selectFrom('tag')
        .innerJoin('product_tag', 'product_tag.tag_id', 'tag.id')
        .whereRef('product_tag.product_site_id', '=', eb.ref('ProductSite.id'))
        .where('tag.category_id', '=', categoryId)
        .select(sql<string>`string_agg(tag.name, ', ' order by tag.name)`.as('v')),
  },
},

// one query, sorted in the DB, policies applied, result still typed
await db.productSite.findMany({
  where: { siteId: { in: allowedSiteIds } },
  orderBy: [
    { tagNameInCategory: { args: { categoryId: 5 }, sort: 'asc', nulls: 'last' } },
    { id: 'asc' }, // tiebreaker — composes with normal ordering
  ],
  select: { id: true, name: true },
  take: 25,
});

The same field (with args) would work in where and select too, since computed fields already
work in all three places:

await db.productSite.findMany({
  where:  { tagNameInCategory: { args: { categoryId: 5 }, not: null } },
  select: { id: true, tagNameInCategory: { args: { categoryId: 5 } } },
});

Why this shape

• It builds on computed fields, which already evaluate in SQL and already work in
  orderBy / where / select. The only new part is "they can take arguments."
• The arguments are plain data, so they serialize and can be sent from a client (unlike a
  function-based escape hatch — see alternatives).
• The implementation type already looks ready for it: ComputedFieldsOptions is
  (eb, ...args) => OperandExpression, i.e. it already allows extra arguments. The missing pieces
  are the ZModel syntax for declaring the args and the query syntax for passing them.

Alternatives considered

• orderBy.$expr — a Kysely-expression escape hatch in orderBy, mirroring the existing
  where.$expr. Good for server-built queries and nice for consistency, but $expr is a
  function, so it can't be serialized / sent from a client (the same reason where.$expr is
  server-only). It doesn't solve the frontend case. Still worth having as a server-side companion.
• Filtered relation-aggregate orderBy, e.g.
  orderBy: { tags: { where: { categoryId: 5 }, _min: { name: 'asc' } } } — fully serializable and
  simpler, but limited to _min/_max/_count, so "sort by the tag names" becomes "sort by the
  min/max tag name". A good lightweight option, but less general than parameterized computed fields.

Notes for maintainers

• Open question: named vs positional args. The current ComputedFieldsOptions type uses
  positional ...args; the examples above use named ({ categoryId }) for readability. Either works.
• Args should be validated (e.g. a zod schema from the ZModel arg types), like custom procedures
  already validate their args.
• Access policy: the computed field runs inside the main query, so the policy plugin still filters
  the queried model. The subquery's referenced tables aren't policy-checked — same as today's
  computed fields and $qb.