Custom Relationship Resolvers

The foreignKey and joinTable resolvers cover ~95% of real-world schemas. For the remainder — closure tables, recursive CTEs, materialized paths, soft-deleted graphs — use the custom resolver to embed raw SQL.

When to reach for custom

• Closure tables / recursive CTEs. Hierarchies that need ancestor / descendant traversal (org charts, comment trees, category hierarchies).
• Computed relationships. Edges that depend on a function call rather than a simple FK (date-window memberships, scope-based visibility).
• Soft-delete-aware paths. Edges that need to filter out tombstoned rows in addition to the base join.
• Polymorphic associations. Tables with entity_type + entity_id columns where the FK target depends on a discriminator.

Shape

import { custom } from 'nest-warden';

const resolver = custom({
  sql: `
    FROM agents {to_alias}
    WHERE EXISTS (
      SELECT 1 FROM agent_hierarchy
      WHERE ancestor_id = {from_alias}.id
        AND descendant_id = {to_alias}.id
        AND active = {:active}
    )
  `,
  params: { active: true },
});

Three structural placeholders the compiler substitutes:

Bound parameters use {:paramName}:

custom({
  sql: `... AND {to_alias}.created_at > {:since} ...`,
  params: { since: new Date('2024-01-01') },
});

The compiler binds each params entry into the QueryBuilder's parameter bag with a fresh name, so re-using the same resolver in multiple rules doesn't cause name collisions.

Example: closure-table ancestors

Schema:

CREATE TABLE agent_hierarchy (
  ancestor_id   uuid NOT NULL,
  descendant_id uuid NOT NULL,
  depth         int  NOT NULL,
  PRIMARY KEY (ancestor_id, descendant_id)
);
-- Closure table: a row exists for every (ancestor, descendant) pair,
-- including (X, X) at depth 0 (every node is its own ancestor).

Define a "descendants of agent" relationship via custom SQL:

graph.define({
  name: 'descendants_of_agent',
  from: 'Agent',
  to: 'Agent',
  resolver: custom({
    sql: `
      FROM agents {to_alias}
      INNER JOIN agent_hierarchy h ON h.descendant_id = {to_alias}.id
      WHERE h.ancestor_id = {from_alias}.id
        AND h.depth > 0
    `,
  }),
});

Then a rule like "Manager Alice can read every agent in her sub-tree":

builder.can('read', 'Agent', {
  $relatedTo: {
    path: ['descendants_of_agent'],
    where: { id: { $ne: null } },
  },
} as never);

Example: recursive CTE

Postgres recursive CTEs work with custom when the closure table isn't pre-materialized. Note the parentheses around the CTE and the final SELECT — the resolver's SQL is what goes between SELECT 1 and the WHERE in the EXISTS subquery.

graph.define({
  name: 'reachable_via_parent',
  from: 'Category',
  to: 'Category',
  resolver: custom({
    sql: `
      FROM categories {to_alias}
      WHERE {to_alias}.id IN (
        WITH RECURSIVE descendants(id) AS (
          SELECT id FROM categories WHERE id = {from_alias}.id
          UNION ALL
          SELECT c.id FROM categories c
          INNER JOIN descendants d ON c.parent_id = d.id
        )
        SELECT id FROM descendants
      )
    `,
  }),
});

Postgres's planner often inlines this efficiently — measure on realistic data before assuming. For very deep hierarchies, a maintained closure table is faster.

Safety contract

Custom resolvers embed your SQL verbatim into the generated EXISTS subquery. The compiler does NOT sanitize the SQL string. As the consumer:

• Use {:param} for any value, never string concatenation.
• Don't construct the sql string from user input — keep it static in app.relationships.ts or similar.
• Audit every custom resolver during code review the same way you'd audit a hand-written query.

If you find yourself building dynamic SQL inside a resolver, that's a sign you need a different abstraction — either splitting into multiple narrower relationships or computing the join via a stored procedure / view.

When custom is the wrong answer

If your relationship is just a plain FK with non-default column names, use foreignKey with the explicit options:

foreignKey({
  fromColumn: 'mgr_id',  // not 'manager_id'
  toColumn: 'employee_pk',  // not 'id'
  fromTable: 'staff',  // override snake_case auto-derivation
});

If your relationship is a junction with extra columns (date ranges, soft-deletes), joinTable won't filter on those automatically — either use custom or filter at rule level via additional conditions on the leaf where.

See also

• Relationship Graph
• $relatedTo operator
• Multi-hop graph design