---
title: Queries & Indexes
description: >-
  Typed filters and sorts, the index rule (MissingIndexException),
  collection-group queries, keyset pagination and count.
---
> ## Documentation Index
> Fetch the complete documentation index at: https://docs.page/redfieldchristabel/butterfly/llms.txt
> Use this file to discover all available pages before exploring further.

Queries are built from the typed handles the generator emits (`Txn$.bookedOn`)
or that you hand-write (`txnBookedOn`) — both are the same `FieldColumn`
class. Every query needs an index that serves it, exactly like Firestore.

## Filters and ordering

```dart
final a1 = db.collection(accounts).doc('a1');

final q = a1.collection(txns)
    .where(Txn$.bookedOn.isGreaterThanOrEqualTo(20260101))
    .orderBy(Txn$.bookedOn, descending: true)
    .limit(50);

final page = await q.get(); // QueryPage<Txn>: .docs, .nextCursor
for (final snap in page.docs) {
  // snap.ref, snap.id, snap.data
}
```

Filter ops on a `FieldColumn`: `isEqualTo`, `isNotEqualTo`, `isLessThan`,
`isLessThanOrEqualTo`, `isGreaterThan`, `isGreaterThanOrEqualTo`,
`whereIn` (at most 30 values) and `isNull`/`isNotNull` on nullable columns.
`ParamColumn` handles expose `isEqualTo`, `isNotEqualTo` and `whereIn` for
path params. Filter methods accept non-null values only; `isEqualTo(null)`
throws — use `isNull()`.

Rules on ordering:

- Sort keys are never null: `orderBy` on a nullable column throws.
- Blob columns cannot be sorted.
- Each column can appear once as a sort key.
- The identity tiebreaker follows the last sort key: every path param the
  query's scope does not bind (in pattern order), then the document id —
  all with that key's direction.
- A subcollection ref scopes by its parent automatically: `a1`'s txns never
  include `a2`'s.

## The index rule

A query runs only when a declared (or implicit) index serves it — otherwise
`get()` and `count()` throw a `MissingIndexException` whose message ends with
a `Fix:` line: the ready one-line command that edits the model for you, plus
the declaration it would write:

```text
MissingIndexException: No index serves this query on Txn
(accounts/{accountId}/txns). Add @Index(['accountId', '-memo']) above its
@Collection class, or call .allowFullScan() to read it anyway.
Fix: dart run cocoon_generator:index add Txn accountId,-memo (writes @Index(['accountId', '-memo']) above the Txn class)
```

Run that command and it declares the index — `@Indexed()` on the field when
it is a single model field, a stacked `@Index([...])` line above the class
for composites and path params — then prints the two follow-up commands
(`migrate`, then `build_runner build -d`). Adding an index to an existing
collection changes the lock version, so `migrate` comes first. It is a
development tool like `migrate`, never a CI step.

An index serves a query when it has, in order:

1. all equality-filtered names (`=`, `IN`, `IS NULL` — any order among
   themselves, including the scoped path params),
2. then the sort keys with matching directions (or all reversed),
3. and at most one range name (`!=`, comparisons, `IS NOT NULL`), which must
   be the first sort key — or, with no sort keys, the next index part after
   the equality prefix.

Every table also has an implicit `UNIQUE` index over `(path params..., id)`,
which serves "list a parent's docs by id" and plain scoped queries. Queries
Queries with no filters and no sort keys are also fine. `.allowFullScan()`
opts out of the rule explicitly:

```dart
final anyOrder = await a1.collection(txns).query().allowFullScan().get();
```

`unique: true` indexes are the exception to the trailing id: a unique index
never gets the id appended (otherwise the constraint could never fire), and
it is unique across the whole collection group — add the path param, e.g.
`Index(['accountId', 'ref'], unique: true)`, to make it unique per parent.

### The debug-only full-scan fallback

While you are still shaping the schema, throwing on every exploratory query
can get in the way. `CocoonOptions(onMissingIndex: MissingIndexMode.scanAndWarn)`
runs such queries as a full scan instead: the missing index is printed once
per distinct missing index per process (with its `Fix:` line, on the
`dart:developer` log and stderr) and listed in `db.missingIndexes` —
model, index parts, query shape, fix command, first seen. Nothing is ever
created at runtime.

```dart
final db = await Cocoon.open(
  file,
  collections: [accounts, txns],
  options: const CocoonOptions(
    onMissingIndex: MissingIndexMode.scanAndWarn, // debug aid, not a default
  ),
);
// ... later:
for (final report in db.missingIndexes) {
  print(report); // Txn: amountMinor (where amountMinor ==). Fix: dart run ...
}
```

The fallback is honoured **only while asserts are enabled** (debug and
profile-with-asserts builds). In a release build it behaves exactly like the
default: the query throws, so a forgotten `.allowFullScan()` can never
silently degrade a shipped query. Multi-range and no-anchor queries throw
under every mode — there is no index to suggest, so there is nothing to
debug-scan.

### From the log to the index: `--missing`

On a phone the model files are not on the device, but the log is text you
already have. The loop: run the app in debug with the fallback on, save the
output (`flutter run`, `flutter logs`, `adb logcat`, a test or Patrol run),
then feed the whole file to the batch form:

```sh
dart run cocoon_generator:index add --missing run.log   # or - for stdin
```

It scans for every `Fix: dart run cocoon_generator:index add ...` line — any
prefix is fine (logcat tags, timestamps, `I/flutter (1234):`) — dedupes
them, and applies each through the same code path as the single command,
one result line per index (`Added ...` / `Nothing to do: ...` / `refused:
...`), then a summary. Exit 0 when every index ended up declared, 1 when any
was refused. `--dry-run` (both forms) prints what would change and writes
nothing. Like `migrate`, a development tool — never a CI step.

```text
Added @Index(['accountId', '-memo']) above the Txn class in lib/models.dart (dart format applied).
Added @Indexed() on amountMinor to Txn in lib/models.dart (dart format applied).
Nothing to do: Index(['currency']) is already declared on Txn as a stacked @Index above the class.
refused: Txn has no field or path param named "currenci". Did you mean "currency"?
2 added, 1 already declared, 1 refused, of 4 in run.log.
```

Then `migrate`, then `build_runner build -d` — the command prints them for
you.

## Collection-group queries

Dropping the parent filter queries every document of the pattern, whatever
parent it belongs to. Parent params can then be filtered explicitly:

```dart
final postedAcrossAccounts = await db
    .collectionGroup(txns)
    .where(Txn$.status.isEqualTo(TxnStatus.posted))
    .orderBy(Txn$.bookedOn, descending: true)
    .get();

final a2Txns = await db
    .collectionGroup(txns)
    .where(Txn$.accountId.isEqualTo('a2'))
    .get();
```

## Keyset pagination and cursors

`limit` fetches one extra row to detect the next page;
`QueryPage.nextCursor` is `null` on the last page. Cursors are opaque and
query-bound: a cursor from another query (or another parent) is rejected
with a `CursorMismatchException`, a malformed one with an
`InvalidCursorException`. The page size is not part of a cursor's identity,
so you may fetch a wide page after a narrow one.

```dart
QueryPage<Txn>? page = await q.get();
while (page != null) {
  for (final snap in page.docs) { /* ... */ }
  page = page.nextCursor == null
      ? null
      : await q.startAfter(page.nextCursor!).get();
}
```

A cursor's tiebreak is the document's full identity. Within one concrete
parent the id is unique, so a scoped query pages by the sort keys plus the
id. A collection-group query spans parents — two documents under different
parents may share an id — so a group query's keyset ties by every path param
the query's scope leaves unbound (in pattern order) between the sort keys
and the id, all following the last sort key's direction; ordering a group
query by a path param works the same way. Group rows always carry the param
columns, so reading the keys costs nothing extra.

## Pagination: With Cocoon vs. raw sqlite

<Tabs>
  <TabItem label="With Cocoon" value="with-cocoon">
    The planner builds the keyset predicate, mixed sort directions included,
    and the cursor is a fingerprinted opaque string:

    ```dart
    final page = await a1.collection(txns)
        .orderBy(txnBookedOn, descending: true)
        .limit(20)
        .get();
    final next = page.nextCursor == null
        ? null
        : await a1.collection(txns)
            .orderBy(txnBookedOn, descending: true)
            .startAfter(page.nextCursor!)
            .get();
    ```
  </TabItem>
  <TabItem label="Without Cocoon (raw sqlite)" value="without-cocoon">
    You write the row-value keyset comparison and re-validate the offset
    keys on every page:

    ```dart
    import 'package:sqlite3/sqlite3.dart';

    List<Map<String, Object?>> nextPage(Database db, int lastBookedOn, String lastId) =>
        db.select(
          'SELECT path, id, data FROM txns '
          'WHERE p_account_id = ? AND (f_booked_on, id) < (?, ?) '
          'ORDER BY f_booked_on DESC, id DESC LIMIT 20',
          ['a1', lastBookedOn, lastId],
        );
    ```
  </TabItem>
</Tabs>

## Count

`count()` runs `SELECT count(*)` under the same index rule:

```dart
final total = await q.count();
```
