ORM Aggregate and Group Guide

Why aggregate and group matter

Many backend queries do not need raw rows only. They need derived results such as counts, sums, averages, grouped totals, or grouped summaries attached to related records.

Vona ORM provides aggregate and group operations as typed model-level capabilities rather than forcing every summary query into ad hoc SQL.

count

The simplest aggregate is count.

Representative pattern:

const total = await this.scope.model.post.count();

A count operation can still depend on:

• column
• distinct
• where
• joins

So even the simplest summary query still participates in the wider ORM query model.

aggregate

Use aggregate when the query should return one summary object.

Representative pattern:

const result = await this.scope.model.post.aggregate({
  aggrs: {
    count: ['*', 'stars'],
    sum: 'stars',
    avg: 'stars',
    min: 'stars',
    max: 'stars',
  },
});

A useful mental model is:

• aggrs declares which aggregate functions should run
• Vona infers the returned shape from the aggregate declaration
• aggregate returns one summary object rather than grouped rows

Representative parameter areas include:

• aggrs
• distinct
• where
• joins

group

Use group when the query should return grouped rows instead of one summary object.

Representative pattern:

const result = await this.scope.model.post.group({
  groups: 'userId',
  aggrs: {
    count: '*',
    sum: 'stars',
  },
});

Representative parameter areas include:

• groups
• columns
• aggrs
• distinct
• where
• joins
• limit
• offset
• having
• orders

This matters because grouped results are still part of the structured ORM query language, not a separate unmanaged SQL world.

A practical result-shape distinction is:

• aggregate returns one summary object
• group returns grouped rows keyed by the chosen group columns and aggregate aliases

Aggregate result values

The ORM adapts database-driver aggregate scalars before returning model results or storing query-cache entries. Cached results and relation aggregates follow the same contract; business services do not need their own normalization helper.

Result	Runtime value and inferred type
count_*	Decimal string, with existing undefined optionality
sum_*, avg_*, min_*, max_*	String when populated; SQL null for empty/all-null inputs; existing undefined optionality retained
Group dimensions	Their ordinary column types and dialect conversion, not aggregate strings

Aliases follow ${function}_${column}, with * mapped to all, for example count_all and avg_stars. Adaptation uses the requested aggrs, not a prefix scan of arbitrary column names. Non-count functions currently accept numeric columns; this does not extend min or max to text/date columns.

An empty group query returns []. An aggregate over no matching rows returns "0" for counts and null for non-count metrics. Missing relation objects retain their existing absence semantics rather than being synthesized into zero-valued summaries. $Dto.aggregate and $Dto.group reflect these scalar contracts, including nullable non-count fields.

count() also returns a string (or its existing undefined absence value). In selectAndCount(), total is a string; pageCount, pageSize, and pageNo remain numbers. Convert explicitly at a business arithmetic boundary only when the application's numeric range permits it.

Driver strings are preserved exactly, including large integers and decimal precision. Supported finite numeric/bigint scalars are converted to strings; numeric counts must be nonnegative safe integers. Unsupported values and unsafe numeric counts are rejected. Conversion cannot restore precision already lost by a driver; configure driver precision deliberately when that matters. The ORM does not parse decimal strings through JavaScript Number.

Compatibility: drivers that previously leaked native numeric aggregate values now return strings, matching the existing declared string contract. Untyped code that depended on native numbers must adapt deliberately. Non-count TypeScript and DTO fields now explicitly admit SQL null; callers must handle it rather than assuming a populated value.

Bounded, client-selectable analysis APIs

Raw model.group() / model.aggregate() remain server-authored ORM operations. Do not expose their full parameter objects directly to clients. For an API that lets clients choose dimensions and metrics, define one $defineAnalysis(modelLike, config) policy and attach it to the resource SelectReq DTO with @Dto({ analysis: policy }).

Use @Arg.filter(SelectReq, { mode: 'group' }) or { mode: 'aggregate' }, infer finite optional response fields with $Dto.analysisGroup(policy) / $Dto.analysisAggregate(policy), and execute through $executeAnalysisGroup(model, policy, params) / $executeAnalysisAggregate(model, policy, params). The same policy supplies field allowlists, per-action defaults, limits, request enums and public filter.capabilities.analysis metadata. It is an API boundary, not an Entity-wide permission flag.

These helpers deliberately expose less than the raw ORM: no joins, relation loading, having, custom expressions or aliases. A named filter that requires joins is rejected for analysis rather than silently dropped. Ordinary select retains its existing joined-filter behavior. Projection, order and pagination controls are validated but do not drive analysis execution; dimension ordering and the result cap are server-owned.

The default limits are 3 dimensions, 10 function/column metric pairs and 1000 grouped rows. Group execution reads at most the configured row cap plus one and fails with 422 on overflow instead of returning a silently truncated result. This bounds materialized/returned rows, not database scan or aggregation cost. Existing instance scope, soft-delete handling, query caching and scalar normalization remain owned by the ORM.

See CRUD Workflow for the Student policy, JSON query transport and default CLI analysis generation.

having and grouped ordering

Grouped queries often need filtering and ordering on derived fields.

Representative pattern:

const result = await this.scope.model.post.group({
  groups: 'userId',
  aggrs: {
    count: '*',
    sum: 'stars',
  },
  having: {
    count_all: {
      _gt_: 20,
    },
    sum_stars: {
      _gt_: 30,
      _lt_: 50,
    },
  },
  orders: [['count_all', 'desc']],
});

This is one of the reasons aggregate/group deserves dedicated documentation instead of being hidden inside a basic select page.

A practical alias rule is:

• aggregate aliases such as count_all and sum_stars become the names used in having and grouped ordering

That is also why grouped or summary outputs often deserve explicit DTO treatment once they become stable API contracts.

Aggregate and group on relations

Aggregate and group are not limited to top-level model queries. They can also participate in relations.

Dynamic relation example

Representative aggregate-on-relation pattern:

const users = await this.scope.model.user.select({
  with: {
    posts: $relationDynamic.hasMany('test-vona:post', 'userId', {
      aggrs: {
        count: '*',
        sum: 'stars',
      },
    }),
  },
});

Representative group-on-relation pattern:

const users = await this.scope.model.user.select({
  with: {
    posts: $relationDynamic.hasMany('test-vona:post', 'userId', {
      groups: 'id',
      aggrs: {
        count: '*',
        sum: 'stars',
      },
    }),
  },
});

Static relation example

Representative static relation pattern:

@Model({
  entity: EntityUser,
  relations: {
    posts: $relation.hasMany('test-vona:post', 'userId', {
      aggrs: {
        count: '*',
        sum: 'stars',
      },
    }),
  },
})
class ModelUserStats {}

That means summary-shaped relations can still be expressed through model metadata instead of living outside the ORM relation system.

Relationship to select and relations

Read this guide together with:

• ORM Select Guide
• Relations Guide
• DTO Infer and Generation

A practical split is:

• use the select guide for row-oriented query structure and operators
• use this guide when the result shape is aggregate- or group-oriented
• use the relations guide when the summary is attached through relation metadata or dynamic relations

Implementation checks for aggregate and grouped query changes

When editing summary-oriented backend queries, ask:

1. is this a row-oriented select, a one-object aggregate, or a grouped result set?
2. should the summary live at the top level or inside a relation?
3. do having and derived-field ordering belong in the group definition?
4. should DTO inference or OpenAPI output reflect the summary shape explicitly?

That helps AI keep statistical and reporting queries aligned with Vona’s typed ORM model.