Derived Fields

Derived fields are subqueries automatically joined to the main query. They are useful for aggregations, cross-table computations, and any value that comes from a related table.

Defining derived fields

import { createRelayerEntity } from '@relayerjs/drizzle';

import * as schema from './schema';

const UserEntity = createRelayerEntity(schema, 'users');

class User extends UserEntity {
  @UserEntity.derived({
    query: ({ db, schema: s, sql, field }) =>
      db
        .select({ [field()]: sql`count(*)::int`, userId: s.posts.authorId })
        .from(s.posts)
        .groupBy(s.posts.authorId),
    on: ({ parent, derived, eq }) => eq(parent.id, derived.userId),
  })
  postsCount!: number;
}

The @UserEntity.derived() decorator accepts a config with:

Property	Description
query	A function that builds a Drizzle subquery
on	A function that defines the JOIN condition
shape	Required for object-type derived fields, defines sub-field keys/types

The query function

The query function receives { db, schema, sql, context, field } and must return a Drizzle query builder. The selected columns must include:

• The value column(s) to be exposed as the derived field
• A join key column used in the on condition

Use the field() helper to name your value columns. It generates the correct key automatically:

query: ({ db, schema: s, sql, field }) =>
  db
    .select({
      [field()]: sql`count(*)::int`,    // value column, resolves to 'postsCount'
      userId: s.posts.authorId,          // join key
    })
    .from(s.posts)
    .groupBy(s.posts.authorId),

The field() helper:

• field(): returns the field name (e.g., 'postsCount') for scalar derived fields
• field('subField'): returns fieldName_subField (e.g., 'orderSummary_totalAmount') for object-type derived fields

The on function

The on function receives { parent, derived, eq } and returns a join condition:

on: ({ parent, derived, eq }) => eq(parent.id, derived.userId),

• parent: column references for the main table
• derived: column references for the subquery
• eq: the Drizzle eq function

Scalar derived fields

When the property type is a scalar, the field resolves to a single value:

class User extends UserEntity {
  @UserEntity.derived({
    query: ({ db, schema: s, sql, field }) =>
      db
        .select({ [field()]: sql`count(*)::int`, userId: s.posts.authorId })
        .from(s.posts)
        .groupBy(s.posts.authorId),
    on: ({ parent, derived, eq }) => eq(parent.id, derived.userId),
  })
  postsCount!: number;
}

const users = await r.users.findMany({
  select: { id: true, firstName: true, postsCount: true },
});
// [{ id: 1, firstName: 'John', postsCount: 3 }, ...]

Object-type derived fields

For multi-value derived fields, declare the property as an object type and provide a shape config describing the sub-field keys and their value types. Use field('subField') to name each sub-field column:

class User extends UserEntity {
  @UserEntity.derived({
    shape: { totalAmount: 'string', orderCount: 'number' },
    query: ({ db, schema: s, sql, field }) =>
      db
        .select({
          [field('totalAmount')]: sql`COALESCE(sum(${s.orders.total}), 0)::text`,
          [field('orderCount')]: sql`count(*)::int`,
          userId: s.orders.userId,
        })
        .from(s.orders)
        .groupBy(s.orders.userId),
    on: ({ parent, derived, eq }) => eq(parent.id, derived.userId),
  })
  orderSummary!: { totalAmount: string; orderCount: number };
}

You can select individual sub-fields:

const users = await r.users.findMany({
  select: { id: true, orderSummary: { totalAmount: true } },
});
// [{ id: 1, orderSummary: { totalAmount: '5000' } }, ...]

Or select the entire object:

const users = await r.users.findMany({
  select: { id: true, orderSummary: true },
});
// [{ id: 1, orderSummary: { totalAmount: '5000', orderCount: 3 } }, ...]

Filtering and sorting by object sub-fields

Object-type derived fields support type-safe dot notation in where and orderBy:

const topSpenders = await r.users.findMany({
  select: { id: true, orderSummary: { totalAmount: true, orderCount: true } },
  where: { orderSummary: { orderCount: { gte: 1 } } },
  orderBy: { field: 'orderSummary.totalAmount', order: 'desc' },
});

Deferred vs eager loading

Relayer automatically decides how to load derived fields:

• Deferred: when the derived field is only in select, it is loaded via a separate batch query after the main query. One extra query per derived field, but the main query stays simple.
• Eager: when the derived field is used in where or orderBy, it is joined via LEFT JOIN in the main query so that filtering and sorting work correctly.

This optimization is automatic, you do not need to configure it.

Example: deferred (select only)

// postsCount is only in select -> deferred batch query
const users = await r.users.findMany({
  select: { id: true, postsCount: true },
});

Main query: SELECT id FROM users Batch query: SELECT id, postsCount FROM users LEFT JOIN (...) WHERE id IN (1, 2, 3)

Example: eager (used in where)

// orderSummary used in where -> eager LEFT JOIN
const users = await r.users.findMany({
  select: { id: true, orderSummary: { totalAmount: true } },
  where: { orderSummary: { orderCount: { gte: 1 } } },
});

Generates a single query with LEFT JOIN:

SELECT users.id, derived."orderSummary_totalAmount"
FROM users
LEFT JOIN (
  SELECT COALESCE(sum(total), 0)::text AS "orderSummary_totalAmount",
         count(*)::int AS "orderSummary_orderCount",
         user_id
  FROM orders GROUP BY user_id
) derived ON users.id = derived.user_id
WHERE derived."orderSummary_orderCount" >= 1

Derived fields with context

Like computed fields, derived fields can access per-query context:

class User extends UserEntity {
  @UserEntity.derived({
    query: ({ db, schema: s, sql, context, field }) =>
      db
        .select({ [field()]: sql`count(*)::int`, userId: s.orders.userId })
        .from(s.orders)
        .where(sql`${s.orders.createdAt} > ${(context as any).since}`)
        .groupBy(s.orders.userId),
    on: ({ parent, derived, eq }) => eq(parent.id, derived.userId),
  })
  recentOrderCount!: number;
}