Search

Totals

Return the counts and measures of every row that a search matches, next to its page, in SQL and on search engines.

A search page can return the totals of every row that its search matches, next to the rows of the page: a sum or an average over all the rows, and the number of rows per value of a field. A list screen shows them above its table, such as the invoices of a customer by status, with the amount of all of them.

The totals belong to the search: they count the rows of the same perimeter, extension restrictions, scopes, instructions, filters, text and trashed as the page, in one request.

Declaring totals

A field opts in with groupable(), to count the rows per value, and measurable(), to measure its values:

app/Rest/Resources/InvoiceResource.php
use Lomkit\Rest\Fields\Enum;
use Lomkit\Rest\Fields\ID;
use Lomkit\Rest\Fields\Integer;

public function fields(): array
{
    return [
        ID::make('id'),
        Enum::make('status', InvoiceStatus::class)->groupable(),
        Integer::make('customer_id')->groupable(),
        Integer::make('amount_cents')->measurable(),
    ];
}
  • groupable() takes an Enum, Boolean, Text, Integer or ID field with one value per row. measurable() takes an Integer or Number field.
  • isGroupable() and isMeasurable() read them, and FieldSet::groupable() and FieldSet::measurable() list them.
  • The definition of the resource refuses, with InvalidFieldException: a groupable() field of another type, a measurable() field of another type, either one on a virtual(), resolveUsing(), inputOnly() or writeOnly() field, measurable() with selectable(false), and groupable() with filterable(false) or filterUsing().
  • A field that a NarrowsFields extension hides or makes unfilterable for the user is neither groupable nor measurable for that user, and answers as an unknown field.

The request

search.totals asks for measures over all the rows, and groups, each counting the rows per value of one field:

POST /api/invoices/search
{
  "search": {
    "filters": [{"field": "customer_id", "value": 42}],
    "totals": {
      "measures": [{"type": "sum", "field": "amount_cents"}, {"type": "avg", "field": "amount_cents"}],
      "groups": [{"field": "status"}]
    }
  }
}
  • A measure has a type, sum, avg, min or max, and a measurable() field.
  • A group has a groupable() field and an optional limit, the most values it returns, from 1 to rest.limits.sizes.groups (100), which is also its default.
  • A search may send measures alone or groups alone, and needs at least one measure or group.

The response

The page adds a root totals key next to total, only when the request asks for it:

200 OK
{
  "current_page": 1,
  "data": [...],
  "from": 1,
  "last_page": 3,
  "per_page": 50,
  "to": 50,
  "total": 128,
  "totals": {
    "exact": true,
    "all": {"count": 128, "sum_amount_cents": 1534200, "avg_amount_cents": 11985.9375},
    "groups": [
      {
        "field": "status",
        "buckets": [{"value": "paid", "count": 97}, {"value": "open", "count": 24}, {"value": "void", "count": 7}],
        "other": {"count": 0},
        "truncated": false
      }
    ]
  },
  "meta": {}
}
  • all.count counts every row that the search matches. It is always returned, and is not a measure type. It equals total, except in two cases: on Turbopuffer, whose total stops at 10,000 while all.count counts every row (total_capped), and in SQL when a page holds every row of a search with distinct() and a join, where total counts the distinct rows of that page and the totals count the joined rows.
  • Each measure is under <type>_<field>, such as sum_amount_cents.
  • groups holds one entry per group asked for, in the order of the request, and [] without groups.
  • buckets lists the values of the field with their count, by count descending, then by value. A bucket value has the type of its field: the backed value for an Enum.
  • other.count counts the rows outside the buckets: the other values and the rows without a value. A row without a value never forms a bucket.
  • truncated is true when at least one other value exists past the limit.
  • The buckets plus other.count always add up to all.count.
  • exact is always true: every figure counts every matched row. A backend that cannot count exactly refuses, with a 422 when the request asks for what it cannot compute, and with an exception when its answer reveals an inexact count.
  • meta stays for the resource and the extensions.

Measures

  • sum, min and max of an Integer field are integers. Those of a Number field, and every avg, are floats. JSON keeps the fraction of a whole float, such as 150.0.
  • Over no row, sum is 0, or 0.0 on a Number field, and avg, min and max are null. The measures ignore null values.
  • A text without terms, such as *, matches no row and returns empty totals without a query. See Text search.

Tied values

When values tie at the limit of a group, the database or the engine chooses which tied value it returns. The buckets are then sorted again by count and value, so the order of tied values can differ from the database order while the counts and other stay right.

Refusals

search.totals answers 422 for:

CaseKeyMessage
No measure and no group ({})search.totalsThe search.totals field must ask for measures or groups.
A field that is not groupable or measurablesearch.totals.groups.0.field, search.totals.measures.0.fieldThe selected search.totals.groups.0.field is invalid.
Another measure type, count includedsearch.totals.measures.0.typeThe selected search.totals.measures.0.type is invalid.
A repeated group or measuresearch.totals.groups.1The search.totals.groups.1 field has a duplicate value.
A limit that is not an integer from 1 to sizes.groupssearch.totals.groups.0.limitThe search.totals.groups.0.limit field must not be greater than 100.
More groups than sizes.totals_groupssearch.totals.groupsThe search.totals.groups field must not have more than 3 items.
A resource whose fields declare no totalsearch.totalsThe search.totals field is prohibited.
A resource that caps its count, with $maxTotal or a NarrowsLimits extension for the usersearch.totalsThe search.totals field is prohibited: this resource caps the count of its rows.
A resource that overrides paginate()search.totalsThe search.totals field is prohibited: this resource counts its pages itself.
  • Counting every row would pass the cap of a resource that caps its count, and the package cannot count the pages of a paginate() override the same way.
  • totals is refused in includes (The search.includes.0.totals field is prohibited.), in the search of an action or a metric, and in Rest::search()->lazy().

Outside HTTP

Rest::search()->render() returns the same root totals, with the PHP types above. page() throws SearchRunException::totalsOnPage() when the payload holds totals, since a paginator holds the rows only: call render(). See Searching outside HTTP.

In the details

/details?with=fields.capabilities adds two keys to each field:

{"name": "amount_cents", "label": null, "type": "integer", "nullable": false, "select": true, "sort": true, "group": false, "measures": ["sum", "avg", "min", "max"], "create": true, "update": true}
  • group is true when search.totals.groups accepts the field, and measures lists the measure types that search.totals.measures accepts on it.
  • Both are false and [] when the resource refuses totals to the user: a capped count or a paginate() override.
  • The pivot fields of a relation do not have them.

See Details. The OpenAPI document adds search.totals to the search of a resource that declares totals, as {Resource}SearchTotals, and an optional root totals to its page, as {Resource}Totals. See OpenAPI.

How SQL counts

  • The totals run one query for all, count(*) with the measures, and one query per group, GROUP BY the column with limit + 1 rows, on the query that counts total.
  • Each group counts its own rows with a window function, so its buckets and other always add up. It needs MySQL 8, MariaDB 10.2, PostgreSQL, or SQLite 3.25 or later.
  • The page and the totals are separate queries: they agree when no write happens between them. Ask for totals when the filters change rather than on each page.
  • The totals count the rows that total counts: a scope that joins counts each joined row, a scope that groups counts each group, and a union() counts the rows of the whole union.
  • A grouped Text column follows its collation: a case-insensitive MySQL collation counts Jazz and jazz as one value.

On a search engine

A search on a search engine computes totals too, with the same request and the same root totals, when its ScoutEngine declares them:

app/Rest/Resources/InvoiceResource.php
use Lomkit\Rest\Fields\Integer;
use Lomkit\Rest\Text\Engine\EngineQuery;
use Lomkit\Rest\Text\ScoutEngine;

return ScoutEngine::make()
    ->filterable(['status', 'customer_id', 'amount_cents'])
    ->groupable(['status', 'customer_id'])
    ->measurable(['amount_cents'])
    ->perimeter(fn (EngineQuery $query) => $query->whereIn('team_id', $this->request()->user()->teamIds()))
    ->indexAttributes([Integer::make('team_id')])
    ->servesLists();
  • groupable() and measurable() take names of the filterable() list, never of indexAttributes(), as sortable() does. A grouped attribute is an ID, Integer, Text, Boolean or Enum attribute with one value per row, a measured one an Integer or Number attribute.
  • The first search of the resource, /details?with=text and rest:check (under rows) refuse, with InvalidTextSearchException, a name outside filterable(), a type that the engine does not group or measure, a repeated name, and these declarations on an engine that computes no totals. Typesense cannot facet on the document id: grouping it throws InvalidTextSearchException::ungroupableAttributeType().
  • A resource whose engine computes totals but declares none prohibits the key: The search.totals field is prohibited.
  • The totals come from the search that reads the page, with the same filters, text, perimeter, restrictions and trashed rows. They count the index, as total does, so a row counted in dropped is counted in the totals too. Curated rows count as the page lists them: nothing outside the filters is counted.
  • The index must facet each grouped and measured attribute, as it must sort a sortable() one, and index each measured attribute as a JSON number.
  • A field that a NarrowsFields extension hides or makes unfilterable leaves both lists for the user, and answers as an unknown attribute.

What each engine computes

Every built-in engine counts the rows per value. The measures depend on the engine:

EngineMeasuresNotes
Typesensesum, avg, min, maxA measured Number attribute needs a decimal:N cast on the model. The schema of the index is read before each search with totals
Meilisearchmin, maxA search with groups reads the faceting settings of the index once
Algoliasum, min, maxA group returns at most 999 values: a limit above 999 answers 422. The faceting settings of the index are read at each search with totals
Turbopuffersum, avg, min, maxEngine lists without text only. A group that reaches 9,999 values, the most that one query returns, throws cappedFacetValues(). all.count counts every matched row while total stops at 10,000
collection, databasesum, avg, min, maxdatabase computes the totals in SQL on the query that it builds, collection in PHP on the rows that it matched
nullsum, avg, min, maxEmpty totals
  • A measure that the engine does not compute on a field answers 422 under search.totals.measures.N.type: The search.totals.measures.0.type field holds a measure that the search engine of this resource does not compute on this field. /details leaves it out.
  • On Turbopuffer, a text search with totals answers 422: The search.totals field is refused: the search engine of this resource computes totals for a list without text only. Its totals come from aggregation queries apart from the page, with the same filters and a strong consistency, so they agree with the page when no write happens between them. These figures rely on the aggregations that the Turbopuffer documentation describes: they could not be checked against a live server.
  • A profile that an extension provides computes no totals: The search.totals field is refused: the search engine of this resource computes no totals.

Exact figures only

The package refuses rather than shows a figure that does not count every matched row. A search throws Lomkit\Rest\Exceptions\EngineSearchException when:

  • the engine reports that it stopped early or sampled the facets (partialFacets());
  • the values of a group count more than the matched rows, since the attribute holds several values per row (multiValuedFacet()): a grouped attribute must hold one value per row;
  • the engine cannot facet an attribute, or leaves one out of its facets (missingFacet());
  • a measured attribute is not a numeric facet in the schema of the index, or is indexed as a string (unmeasurableFacet());
  • the index keeps one hit per distinct value, such as distinct with attributeForDistinct on Algolia (distinctFacets());
  • the engine answers without the count of the matched rows (missingCount(), on Turbopuffer);
  • the index caps the values that it returns for an attribute without sorting them by count (cappedFacetValues()): on Typesense, Meilisearch and Algolia, raise the cap of the index, or sort its values by count and keep limit below the cap; on Turbopuffer, the cap of 9,999 values is fixed;
  • the package cannot read that cap or the schema of the index (unreadFaceting(), unreadSchema()).

Options that totals own

A resource whose ScoutEngine declares totals owns the engine options that make its facets exact, on every search, with or without totals: EngineQuery::options() refuses them with InvalidEngineQueryException, and a model that returns one from its search parameters throws InvalidTextSearchException at the first search, which rest:check reports under rows.

EngineOwned options
Typesensefacet_by, max_facet_values, facet_query, facet_strategy, facet_sample_percent, facet_sample_threshold, facet_return_parent, search_cutoff_ms, pinned_hits, hidden_hits
Meilisearchfacets, distinct
Algoliafacets, maxValuesPerFacet, facetingAfterDistinct, sortFacetValuesBy, facetFilters, distinct
Turbopufferaggregate_by, group_by, consistency

Decimal measures

An engine may store decimal numbers with limited precision. The package rounds the measures of a Number attribute, avg included, to the scale of its decimal:N cast on the model, on Typesense, Meilisearch, Algolia, Turbopuffer and the collection driver, when the attribute has that cast. The database driver computes its measures in SQL and does not round them.

app/Rest/Resources/InvoiceResource.php
Number::make('amount')->measurable(),
app/Models/Invoice.php
protected function casts(): array
{
    return ['amount' => 'decimal:2'];
}
  • Typesense stores decimals with limited precision, so it requires the cast: measurable() on a Number attribute without it throws InvalidTextSearchException::unscaledMeasure() at the first search, and rest:check reports it under rows.
  • There, a single value above about 167,772 with cents is no longer exact to the cent: index money as integer cents in an Integer attribute, such as amount_cents, whose measures stay exact.

One value per row

A grouped attribute holds one value per row. On an engine with a typed index schema, a list type throws multiValuedFacet(); elsewhere, keep it to one value yourself. A search engine may also normalize text values when it groups them, as a database collation does: values that differ in letter case can count as one bucket, shown in one of their forms.

Totals in the details

/details?with=text adds totals to the engine block of a resource whose engine declares them, for the user of the request:

{"totals": {"groups": ["status", "customer_id"], "measures": {"amount_cents": ["sum", "avg", "min", "max"]}, "lists_only": false}}

measures lists the measure types that the engine computes on each attribute, and lists_only is true when the engine computes totals for engine lists only, such as Turbopuffer.

Grouping on two values

A group counts the rows per value of one field. To count them per pair of values, such as a status per month, store the pair as one value: a column that the database computes, declared as a Text field with groupable(), or an attribute that toSearchableArray() builds and the index facets.

app/Models/Invoice.php
public function toSearchableArray(): array
{
    return [
        // ...
        'status_month' => $this->status->value.'|'.$this->issued_on->format('Y-m'),
    ];
}

On a search engine, declare it in filterable() and groupable():

app/Rest/Resources/InvoiceResource.php
use Lomkit\Rest\Fields\Text;

return ScoutEngine::make()
    ->filterable(['status', 'customer_id', 'amount_cents', Text::make('status_month')])
    ->groupable(['status', 'customer_id', 'status_month'])
    // ...

The buckets then read paid|2026-03, and the client splits each value.

Limits

KeyDefaultBounds
rest.limits.sizes.measures20The measures of one search
rest.limits.sizes.totals_groups3The groups of one search
rest.limits.sizes.groups100The limit of each group, and its default

Their entries also count in rest.limits.totals.entries. A published config gets the new keys from the package config, which is merged at every level. See Configuration.