# Collection options

> Shape query results with limit, offset, orderBy and uniqueBy - pushed down to your data sources where possible, applied by Orbital everywhere else.

Source: https://orbitalhq.com/docs/querying/collection-options

_Available since 0.38.0_

## Overview
Collection options let you shape the results of a query - limiting how many rows come back, paginating through them,
sorting them, and removing duplicates - without changing what the query means.

They're written as options on a collection expression, alongside any filter criteria:

```taxi
find { Person[]( CountryCode == "GB", orderBy: DateOfBirth desc, offset: 20, limit: 10 ) }
```

This reads as: find people in GB, sort them by date of birth (newest first), skip the first 20, and return the next 10.

The same options work against **any** data source. Where a source can apply an option natively (a database sorting and
limiting in SQL, for example), Orbital pushes the work down to it. Where a source can't, Orbital applies the option itself
after fetching. Either way the result is the same.

The full syntax lives in the [Taxi language reference](https://taxilang.org/docs/taxiql/querying#collection-options) -
this page focuses on how Orbital executes them.

## The options
The following options are available on a collection expression:

| Option     | Description                                                                                          |
|------------|------------------------------------------------------------------------------------------------------|
| `limit`    | The maximum number of items to return.                                                               |
| `offset`   | The number of items to skip before returning results. Useful for pagination.                         |
| `orderBy`  | Sorts results by one or more fields. Each term takes a direction (`asc` or `desc`), e.g. `orderBy: DateOfBirth desc`. |
| `uniqueBy` | Removes duplicates, keeping the first item seen for each distinct value of the given field.           |

Options are comma-separated, and can be combined with filter criteria in the same expression.

Multiple sort terms are supported - list them in square brackets, and they're applied left to right:

```taxi
find { Person[]( orderBy: [CountryCode asc, DateOfBirth desc] ) }
```

Values can be literals, or [query parameters](https://taxilang.org/docs/taxiql/querying#collection-options):

```taxi
query findPeople( maxRows: Int ) {
  find { Person[]( limit: maxRows ) }
}
```


**Note: Cursors are reserved**
The `after` and `before` cursor options parse and compile, but are not yet supported at runtime - a query that uses
  them is rejected with a clear error. They're reserved for cursor-based pagination in a future release.

## Logical order
Regardless of the order you write them in, options are always applied in a fixed logical order:

1. **Filter** - the predicate criteria (e.g. `CountryCode == "GB"`)
2. **uniqueBy** - remove duplicates
3. **orderBy** - sort
4. **offset** - skip
5. **limit** - take

This ordering is what makes combinations predictable. `uniqueBy: PersonId, limit: 10` returns up to 10 *unique* people -
duplicates are removed first, then the limit is applied to what remains.

## Shaping at the source vs. shaping the projection
Options attach to the collection expression they're written on, and their position decides which phase of the query they
shape.

Written on the `find`, they shape what's fetched from the source. Written on a projection, they shape the projected
result. You can use both in a single query:

```taxi
// Fetch up to 100 people from the source, project them, then return the first 10 of the projected result
find { Person[]( limit: 100 ) } as {
  name : PersonName
  country : CountryName
}[]( limit: 10 )
```

Nested collections inside a projection can be shaped too - useful for trimming child records per parent:

```taxi
find { Customer[] } as {
  name : CustomerName
  // Only the 20 most recent transactions for each customer
  transactions : Transaction[]( limit: 20, orderBy: TransactionDate desc )
}[]
```

See [shaping projected collections](https://taxilang.org/docs/taxiql/projections#shaping-projected-collections) in the
Taxi docs for more on projection-position options.

## How options are executed: pushdown vs. Orbital-applied
For each option, Orbital's query planner decides - per data source - whether to **push it down** to the source or to
**apply it itself** after fetching (a "residual" option).

Pushing down is usually more efficient: a database can sort and limit far more cheaply than Orbital can after loading
every row. But not every source can apply every option, so anything a source can't handle is simply applied by Orbital
instead. Nothing is lost - it's a question of *where* the work happens, not *whether* it happens.

Here's what each source applies natively today:

| Source                                         | Pushed down to the source    | Applied by Orbital                    |
|------------------------------------------------|------------------------------|---------------------------------------|
| Databases (Postgres, MySQL, MSSQL, Oracle, Redshift, Snowflake) | `limit`, `offset`, `orderBy` | `uniqueBy` (and `orderBy` when it can't be pushed) |
| MongoDB                                        | `limit`, `offset`, `orderBy` | `uniqueBy` (and everything, for native aggregations) |
| Hazelcast                                      | `limit`                      | `offset`, `orderBy`, `uniqueBy`       |
| REST APIs, Kafka, and all other sources        | -                            | `limit`, `offset`, `orderBy`, `uniqueBy` |

See the per-source pages for the details and caveats:
[Databases](/docs/describing-data-sources/databases#collection-options),
[MongoDB](/docs/describing-data-sources/mongodb#collection-options), and
[Hazelcast](/docs/describing-data-sources/hazelcast#collection-options).

### Pushdown never changes the result
The planner will only push an option down when doing so is guaranteed to produce the same answer as applying it in
Orbital. Where that guarantee doesn't hold, it fetches more and does the work engine-side.

For example: if a source can apply an `orderBy` but a `limit` is also present, Orbital won't push the `limit` below the
sort unless the sort is pushed too. Limiting *before* sorting would return the wrong rows - so instead Orbital fetches,
sorts, and then limits itself. The safety rule is always applied in your favour: a pushed-down option is never allowed to
change the semantics of the query.

## Streaming queries
On a streaming query, `limit` is supported - it completes the stream once N items have been emitted:

```taxi
// Take the first 100 events, then complete
stream { StockPrice }( limit: 100 )
```

`orderBy`, `offset` and `uniqueBy` don't have a meaningful definition over an unbounded stream, so they're rejected with
a clear error rather than silently ignored.

## Error cases
A few inputs are rejected rather than guessed at:

 * **Negative `limit` or `offset`** (including dynamic values resolved from a parameter) fail with a clear error.
 * **`limit: 0`** is valid, and returns an empty result.
 * **`after` / `before` cursors** compile, but are rejected at runtime as not yet supported.
 * **`orderBy`, `offset`, `uniqueBy` on a stream** are rejected - only `limit` is supported when streaming.