Collection options: limit, sort and paginate any query

You can now shape the results of a query directly in TaxiQL - limiting how many rows come back, paginating through them, sorting them, and removing duplicates. We’re calling these collection options, and they look like this:

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

These are the standard database-style controls we all already know and love - limit, offset, orderBy and uniqueBy - but they work on any query, against any source.

When the source can apply an option itself, Orbital pushes the work down to it. When it can’t, Orbital does the work after fetching. Either way you get the same result.

​

A few examples

Sort and take the top N:

find { Film[]( orderBy: RottenTomatoesScore desc, limit: 10 ) }

Sort by more than one field, and paginate:

find { Person[]( orderBy: [CountryCode asc, DateOfBirth desc], offset: 40, limit: 20 ) }

Options work on projections too, including nested collections - handy for trimming child records per parent:

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

Values can be literals or query parameters, so limit: maxRows works just as well when maxRows is a query argument.

​

How pushdown works

For each option, Orbital’s query planner decides - per data source - whether to push it down or to apply it itself.

Pushing down tot he source system is only permitted if it doesn’t change the answer: if a source can’t sort, Orbital won’t push a limit below the (missing) sort either, because limiting before sorting would hand back the wrong rows.

Here’s what’s supported in this release:

• SQL databases (Postgres, MySQL, MSSQL, Oracle, Redshift, Snowflake) push limit, offset and orderBy straight into the generated SQL, in the right dialect for your driver.
• MongoDB pushes limit, offset and orderBy into the Mongo query as sort / skip / limit.
• Hazelcast pushes limit down as a key-ordered page; everything else is applied by Orbital.
• Everything else - REST APIs, Kafka, and any other source - is applied by Orbital after fetching. No source-side support required.

limit also works on streaming queries, completing the stream once it has emitted N items.

​

Coming next

after / before cursors are reserved for cursor-based pagination and will land in a future release - for now they parse but are rejected at runtime.

Full details are in the collection options docs, and the syntax reference lives in the Taxi language docs.