Pagination and filtering

How to page through results, sort them stably, and filter them.

First: lists don't come from a REST collection

Check that you are asking the right endpoint. Reading lists is generally not done through classic REST collections in VARIO Cloud.

There is no GET /erp/accounts, no GET /erp/articles and no GET /erp/documents. Those paths take POST and PUT — a GET exists only for a single record with its id, as in GET /erp/accounts/{id}.

Reading across records goes through the computed query API instead. There are two ways to express the same query, and you can pick either:

	VQL	C-Unit
What it is	An SQL-like language, sent as a text statement	The same query as a structured object
You write	SELECT … FROM … WHERE …	A predicate tree, a result list and a pageable
Endpoint	POST /cmn/computed-queries/execute	POST /cmn/computed-queries/{group}/{template}

VQL — the language, and how to discover the available templates and field paths. The same templates and field paths apply to C-Unit; its object form is described below.

There are exceptions. A few endpoints really do return a list — GET /cmn/create-templates is one, and the stock journal, import run entries and full-text search are others. Treat them as exceptions rather than the rule: when you need a list and no such endpoint is documented for it, the answer is a computed query.

Either way this page applies to both worlds, because computed queries are paged too — with different parameter names, which is the next section.

Getting paging right matters more than it looks: paging without a stable sort silently returns some rows twice and skips others.

Paging

Two parameters, everywhere: an offset and a limit. What they are called depends on how you send them — this is the single most common source of confusion.

Where you send it	Offset	Limit	Sort
Query string on a REST collection	start	limit	sort + order
JSON body, same object nested in a request	_start	_limit	sort + _order
C-Unit — the pageable object	start	limit	sortOrders
VQL — in the statement	OFFSET n	LIMIT n	ORDER BY …
VQL — as request fields	offset	limit	in the statement

Yes — the same field is _start in a body and start in a query string, and sort never takes the underscore while _order does. If a parameter seems to be ignored, check that you used the spelling that matches your transport.

For VQL, note that both routes exist: LIMIT 10 OFFSET 20 inside the statement, or limit / offset as request fields. If you set both, the request field wins and the clause in your statement is ignored — so pick one.

Defaults on REST collections: offset 0, limit 100. There is no enforced maximum.

GET /erp/stocks/stockjournal?start=0&limit=50&sort=id&order=asc

With C-Unit the pageable is an object, and sort is a list:

{
  "pageable": {
    "start": 0,
    "limit": 100,
    "sortOrders": [{ "property": "id", "direction": "ASC" }]
  }
}

Knowing when you're done

Two response headers tell you whether more data exists:

Header	Meaning
x-query-more-elements	"true" if there are further rows
x-query-next-offset	The offset to request next

Both are strings, and the offset is "0" when you have reached the end — not the final offset.

So a loop that reuses x-query-next-offset without first checking x-query-more-elements jumps back to the first page and runs forever. Always branch on the flag:

const hasMore = headers['x-query-more-elements'] === 'true';
const nextOffset = Number(headers['x-query-next-offset']);

if (!hasMore) break;

And note that "false" is a truthy string — comparing with === 'true' is not optional.

There is no total count. The API determines "are there more" by reading one row beyond your page, so a total would cost a second query. Build UIs that page forward rather than showing "page 7 of 42".

Always sort by a unique column

This is the rule that prevents the bug in the title of this page:

Offset paging over an unsorted result set is undefined: the pages overlap, so rows come twice while an equal number is never served at all, and differently on every run.

REST collections apply no default order at all. Computed queries add a primary-key tiebreaker, but only under conditions that are easy to miss:

• it requires that a limit or offset is set,
• and that the root entity's id is among the selected results.

Two further traps:

• A non-unique sort column is not enough. Sorting by a date still leaves rows with the same date in arbitrary relative order.
• Sending pageable discards the query template's own default sort. Since paging requires sending pageable, you always lose it — so state your sort explicitly every time you page.

Defensive practice: sort by id, and de-duplicate by id after collecting the pages.

Filtering

VQL: a WHERE clause

If you send a VQL statement, you filter the way you would in SQL — WHERE, AND/OR, LIKE, IN, and so on. See VQL for the operators and the special value expressions.

C-Unit: a predicate tree

In object form the filter is structured rather than written out. A node is either a JUNCTION (with children) or a FILTER (with property and values):

{
  "queryPredicate": {
    "type": "JUNCTION",
    "operator": "AND",
    "children": [
      { "type": "FILTER", "operator": "EQUALS", "property": "active", "values": ["true"] },
      { "type": "FILTER", "operator": "IN", "property": "type", "values": ["A", "B"] }
    ]
  }
}

values is always an array of strings, even for numbers and booleans.

Commonly used operators:

Group	Operators
Junctions	AND, OR
Equality	EQUALS, NOT_EQUALS, IS_NULL_OR_EQUALS
Text	LIKE, NOT_LIKE, STARTS_WITH, ENDS_WITH, CONTAINS, NOT_CONTAINS
Comparison	GREATER_THAN, GREATER_THAN_EQUALS, LESS_THAN, LESS_THAN_EQUALS
Sets	IN, NOT_IN
Ranges	RANGE, NOT_RANGE (exactly two values)
Presence	IS_NULL, IS_NOT_NULL
Subqueries	EXISTS, NOT_EXISTS

Operators have a fixed arity — IS_NULL takes no values, RANGE takes exactly two — and violating it is rejected. GET /cmn/computed-queries/dialect-info reports what the dialect supports in your version.

You can either replace the preset's filter (adhocPreset.queryPredicate) or add to it (additionalPredicate, which is AND-ed on top).

REST collections: per-endpoint parameters

There is no generic filter syntax. No ?field=value, no RSQL, no OData. Each collection endpoint defines its own set of filter parameters, and those are AND-combined.

Consult the API Reference for what a given endpoint accepts. If the filter you need isn't there, fall back to a computed query — in VQL or C-Unit form.

A concrete example that trips people up: to resolve an article id you cannot filter /erp/articles on articleNumber — as noted at the top, that collection cannot be read. Query the article template instead, filtering on number.

Query parameters carry flat values only

Query parameters are plain scalar key–value pairs. There is no deepObject support: a nested object put into a query string does not serialise into something the API can read.

• Scalars — send as normal.
• Lists — repeated keys (types=a&types=b) or comma-joined both work.
• Anything nested — must go in a request body. That is exactly why the pageable exists in both a query-string and a body form.

Pitfalls

What you do	What happens
limit=0 on a REST collection	500 — the limit must be at least 1
offset=0 on /cmn/computed-queries/execute	422 — that endpoint requires positive values; omit the parameter instead
Omit limit on a computed query	You get the entire result set, not 100 rows — and your paging loop exits after one huge page
Select a projection without id	The tiebreaker cannot apply; unstable order returns
Treat x-query-next-offset as authoritative	Infinite loop at the end of the data (see above)
Boolean(header) for more-elements	Always true, because "false" is a non-empty string

Related

• VQL — the query language, and how to discover field paths
• Rate limiting — pace your paging loops
• FAQ