Skip to content

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.

The direct model, relation, having, join and tuple-order examples on this page are trusted server-authored ORM queries. They are not HTTP/MCP inputs. Public requests use the Public Query Guide, with the separate analysis policy described below and no raw-ORM fallback.

count ​

The simplest aggregate is count.

Representative pattern:

typescript
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:

typescript
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:

typescript
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.

ResultRuntime 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 dimensionsTheir 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 client-selectable analysis, associate both a $defineQuery predicate policy and a $defineAnalysis dimension/metric policy with the resource SelectReq DTO: @Dto({ query: queryPolicy, analysis: analysisPolicy }).

Use @Arg.filter(SelectReq, { mode: 'group' }) or { mode: 'aggregate' } and infer finite optional response fields with $Dto.analysisGroup(analysisPolicy) / $Dto.analysisAggregate(analysisPolicy). For a Resource Action, the controller compiles public input with $compileQuery(model, queryPolicy, input, { mode: 'group', analysis: analysisPolicy, scopeWhere }) (or aggregate mode), using scopeWhere: rbacScopeCurrent.where() when RBAC data scope applies. Never pass the public AST to rbacScopeCurrent.where(params.where). The service accepts trusted analysis parameters and passes them to $executeAnalysisGroup / $executeAnalysisAggregate without public-query recompilation. Those execution helpers retain their trusted ORM API. Dedicated self-service methods with explicit identity and business scope may still own public-query compilation; see Public Query Guide. The analysis policy supplies dimension/function-column allowlists, Action defaults, limits, request enums and filter.capabilities.analysis; the query policy separately supplies public predicate capabilities. Neither is an Entity-wide authorization flag.

Public analysis accepts the canonical predicate AST, AND-composed named conveniences and admitted analysis controls. It rejects columns, orders, pageNo and pageSize rather than validating and ignoring list controls. Clients cannot supply joins, relation loading, having, custom expressions or aliases. Only fixed related-field bindings deliberately admitted by the query policy are available; dimension/metric allowlists remain main-model fields.

The complete Boolean filter is evaluated through main-model ID membership rather than aggregating joined rows. Each matching main record contributes once. Keep the predicate together: independent per-leaf EXISTS rewrites can change repeated-field, OR/NOT and same-child AND semantics. Inner joins require a match; left joins preserve unmatched main rows unless the predicate excludes them. Related source restrictions must stay at the source/ON boundary rather than accidentally converting a left join into an inner join. Dimension ordering and result caps remain server-owned.

Main-model instance/deleted scope and mandatory server scopeWhere remain active; services retain business authorization and additional business-scope enforcement. Fixed public related-model bindings retain target instance/deleted scope; additional business visibility must be deliberately enforced. Trusted server analysis may still use richer typed joins, including raw tables, which do not automatically acquire model instance, soft-delete or authorization scope. Supply their required source constraints explicitly. If related-source mutations invalidate warmed analysis queries, declare and verify the existing modelsClear / modelsClearedBy dependency as described in Cross-model query-cache dependencies.

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. 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:

typescript
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:

typescript
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:

typescript
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:

typescript
@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:

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.

Released under the MIT License.