Group-by-count

The filter contract is where + search + sort + distinct + paginate, and it always returns entity rows. groupByCount adds a terminal aggregation mode for the most common chart-feeding query shape — one the entity-row contract can't express:

SELECT <col> AS value, COUNT(*) AS count
FROM <entity>
WHERE <normal filter clauses>
GROUP BY <col>

and its numeric-bucketed histogram variant, where the grouping is parameterized per request:

SELECT FLOOR(<col> / :bucket) * :bucket AS value, COUNT(*) AS count
FROM <entity>
WHERE <normal filter clauses>
GROUP BY FLOOR(<col> / :bucket) * :bucket

It is mutually exclusive with entity-row output — a request carrying groupByCount is answered with the aggregation, never rows. This makes a root-level GROUP BY safe: aggregation output replaces rows rather than multiplying them, so the "never GROUP BY on the outer query" invariant that guards entity-row queries (and the to-many aggregate subqueries) doesn't apply here.

It also takes a limit — the top N groups by count:

SELECT <col> AS value, COUNT(*) AS count
FROM <entity>
WHERE <normal filter clauses>
GROUP BY <col>
ORDER BY COUNT(*) DESC
LIMIT :limit

A grouping column whose distinct values grow with the data — tags, external ids, a free-text label — answers the unbounded aggregate with one row per value, which is a listing wearing an aggregate's shape. The caller that wants it most, a value picker, renders a handful. Ordering only becomes part of the contract once rows are being dropped: without a limit you get every group, in whatever order the database returns, exactly as before.

Client

The client builder (@dudousxd/nestjs-filter-client) gains a terminal groupByCount(field, opts?) method. The active where/search still apply; sort/paginate/distinct do not.

import { filterQueryTyped } from '@dudousxd/nestjs-filter-client';

// Plain: one { value, count } per distinct value
const byStatus = filterQueryTyped<WorkOrderFields>()
  .where('base.id', 'in', baseIds)
  .where('workOrderStatusCode', 'notEquals', 'V-Voided')
  .groupByCount('workOrderStatusCode')
  .build();

// Bucketed: a histogram of { bucketStart, bucketEnd, count }
const costHistogram = filterQueryTyped<WorkOrderFields>()
  .where('base.id', 'in', baseIds)
  .groupByCount('totalActualCost', { bucket: 1000 })
  .build();

// Top-N: what a value picker over an unbounded column asks for
const topTags = filterQueryTyped<WorkOrderFields>()
  .groupByCount('tag', { limit: 20 })
  .build();

On the codegen per-route typed builder, field is constrained to that route's filter fields, so grouping by an unknown column is a compile error — matching the server-side allowlist validation.

The wire body extends the structured input shape:

{
  "filter": { "where": [ /* ... */ ] },
  "groupByCount": { "field": "totalActualCost", "bucket": 1000, "limit": 20 }
}

Response shape

// Non-bucketed:
Array<{ value: T; count: number }>

// Bucketed (the server derives bucketEnd = bucketStart + bucket):
Array<{ bucketStart: number; bucketEnd: number; count: number }>

Server

Run the aggregation through FilterRunner.groupByCount — dynamic mode, no filter class required, mirroring findAndCount:

@Post('cost-distribution/search')
async costDistribution(@Body() body: unknown) {
  return this.filterRunner.groupByCount(WorkOrder, body);
}

The groupByCount.field is validated against the entity's filterable columns with the same allowlist/metadata machinery sort and distinct use. An unknown identifier is rejected with a 400 and never reaches SQL — so the SQL-injection-by-identifier risk that hand-rolled raw-SQL endpoints manage with maintained column allowlists is closed by construction. The bucket width binds as a query parameter, never string-interpolated.

Adapter contract

Adapters opt in by implementing the optional groupByCount method (following the same convention as applyDistinct):

groupByCount?(
  qb: unknown,
  field: string,          // already validated against entity metadata
  entity: Type<unknown>,
  opts?: { bucket?: number; limit?: number },
): Promise<Array<{ value: unknown; count: number }>>;

An adapter that cannot bound the aggregate may return every group; a caller must not assume the bound was applied. A limit that is not a positive integer degrades to the unbounded form rather than failing the request, the way a non-positive bucket already does — and numeric strings are coerced, so ?groupByCount[limit]=20 works on a GET route.

Identifiers are resolved from ORM metadata (never client text) and the bucket width is bound as a parameter. The runner shapes the returned { value, count } rows into the final response — deriving bucketEnd for the bucketed variant.