> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Computed Fields

> Fields the database computes rather than the caller writes, spliced into each query or stored as a real column.

Source: https://uql-orm.dev/entities/computed-fields

`@Field({ computed })` declares a value the database produces, not one the caller writes. It never takes part in an insert or an update, and reads like any other field. Declare it `readonly`, and a write payload naming it is a compile error rather than a value silently dropped.

It takes one of two forms. An expression over the row - arithmetic, a string built from two columns, a date part - which is SQL, below; or a [relation aggregate](#relation-aggregates), which is data and reads on every engine.

```ts
import { Entity, Id, Field, raw } from 'uql-orm';

@Entity()
export class Product {
  @Id({ type: Number })
  id?: number;

  @Field({ type: Number })
  cost?: number | null;

  @Field({ type: Number })
  salePrice?: number | null;

  @Field({
    type: Number,
    // Each ref renders as its column, qualified and escaped, so the expression survives a join,
    // a subquery whose columns would shadow a bare name, and a rename in your editor.
    computed: (product) => raw`${product.salePrice} - ${product.cost}`,
  })
  readonly profit?: number | null;
}
```

## `stored`: the one dial

By default nothing is persisted: the expression is spliced into every statement that reads the field. Add `stored: true` and it becomes a real column the engine keeps up to date, so it can be indexed, constrained and read without recomputing.

```ts
@Field({
  type: Number,
  computed: (product) => raw`${product.salePrice} - ${product.cost}`,
  stored: true,
})
profit?: number | null;
```

Queries do not change. `$select`, `$where` and `$sort` read the field the same way either side of the dial, so `stored` is something you flip after profiling without touching a call site.

| | unstored (default) | `stored: true` |
| - | - | - |
| where the value is | recomputed per statement | a column, written by the engine |
| DDL | none | `GENERATED ALWAYS AS (...) STORED` |
| indexable | no | yes, like any column |
| expression | anything the dialect parses | must be deterministic, over the row |

A stored column needs a `type`, since a migration has to spell one out. Support is Postgres 12+, MySQL 5.7+, MariaDB 5.2+, SQLite 3.31+.

Either way the expression is SQL, which MongoDB does not evaluate: a query naming one there is refused rather than answered with `undefined`.

## Relation aggregates

The other form reads a relation instead of writing SQL: `count()`, `sum()`, `min()`, `max()` and `avg()`, off the same refs. The aggregate says what the field holds, so it declares no `type`.

```ts
import { Entity, Id, Field, ManyToOne, OneToMany } from 'uql-orm';

@Entity()
export class Order {
  @Id({ type: Number })
  id?: number;

  @OneToMany({ entity: () => OrderItem, mappedBy: (item) => item.order })
  items?: OrderItem[];

  @Field({ computed: (order) => order.items.count() })
  readonly itemCount?: number;

  @Field({ computed: (order) => order.items.sum((item) => item.amount) })
  readonly total?: number;

  @Field({ computed: (order) => order.items.max((item) => item.amount) })
  readonly largestItem?: number | null;

  // Which of the related rows it reads.
  @Field({
    computed: (order) => order.items.count({ $where: { refunded: false } }),
  })
  readonly paidCount?: number;

  // ...and, for a value aggregate, a page of them.
  @Field({
    computed: (order) =>
      order.items.sum((item) => item.amount, {
        $sort: { amount: -1 },
        $limit: 5,
      }),
  })
  readonly topFiveTotal?: number;
}

@Entity()
export class OrderItem {
  @Id({ type: Number })
  id?: number;

  @Field({ references: () => Order, type: Number })
  orderId?: number | null;

  @ManyToOne({ entity: () => Order, references: (item) => item.orderId })
  order?: Order;

  @Field({ type: Number })
  amount?: number | null;

  @Field({ type: Boolean })
  refunded?: boolean | null;
}
```

`count` and `sum` read `0` over a parent with no rows; `min`, `max` and `avg` read `null`, and the property has to admit it. Only a to-many can be aggregated, and `sum` and `avg` only over a numeric column. A many-to-many’s `count` tallies its links; a column of its targets reads each target once.

Each one is a correlated subquery inside the parent’s own statement, so no related row is loaded:

```ts title="You write"
const orders = await pool.findMany(Order, {
  $select: { id: true, total: true },
  $where: { itemCount: { $gte: 2 } },
  $sort: { total: -1 },
});
```

PostgreSQL:

```sql
SELECT "id", (SELECT COALESCE(SUM("items"."amount"), 0) FROM "OrderItem" "items"
  WHERE "items"."orderId" = "Order"."id") "total"
FROM "Order"
WHERE (SELECT COUNT(*) FROM "OrderItem" "items_2"
  WHERE "items_2"."orderId" = "Order"."id") >= $1
ORDER BY (SELECT COALESCE(SUM("items_3"."amount"), 0) FROM "OrderItem" "items_3"
  WHERE "items_3"."orderId" = "Order"."id") DESC
```

MongoDB reads the same field: the declaration is data, not SQL, so it renders there as a `$lookup` ending in a `$count` or a `$group`.

### Reading only some of the rows

`topFiveTotal` above is the shape: a value aggregate reading only some of the related rows takes the `$sort` that picks which ones, together with its `$limit`, since a total over five of them is defined by nothing else.

A `count` takes `$limit` on its own - an order changes which rows a page holds, never how many - and caps the tally, which is how you ask “at least 500?” without counting a million.

### Loading

An aggregate reads the related rows, so, like a relation, it loads only where a query names it in `$select`, `$where` or `$sort`. `eager: true` puts it in every read.

`stored: true` is not accepted on one: a correlated subquery is not something an engine keeps in a generated column.

## Querying

Select and filter them like any other field.

### Selection

```ts title="You write"
const products = await pool.findMany(Product, {
  $select: { id: true, profit: true },
});
```

PostgreSQL:

```sql
SELECT "id", "salePrice" - "cost" "profit" FROM "Product"
```

### Filtering

```ts title="You write"
const products = await pool.findMany(Product, {
  $select: { id: true },
  $where: {
    profit: { $gte: 10 },
  },
});
```

PostgreSQL:

```sql
SELECT "id" FROM "Product" WHERE "salePrice" - "cost" >= $1
```