Tutorial: Building with CleaveDB

Sorting and Choosing Fields

A query can identify exactly the right documents and still return more than a page needs. SCOOP can order those documents, cap the result, and project only selected fields. This keeps the result legible and tailored to its destination: a catalog can show a few products in price order, with just the title and price visible in each item.

Choose the order

ARRANGED BY sorts on a field. Follow it with GOING UP for ascending order or GOING DOWN for descending order. CleaveQL also accepts ORDER BY and SORTED BY, along with ASC and DESC direction forms. Use sorting when rank or sequence matters: a price comparison is more useful when the highest or lowest prices appear where the reader expects them.

SCOOP products ARRANGED BY price GOING DOWN
SCOOP products ORDER BY price GOING UP
SCOOP products SORTED BY price DESC

Sorting is most valuable when the order communicates something: prices from high to low, or a score from low to high. If your screen needs a specific order, name the field and direction explicitly rather than relying on the incidental order in which documents happen to be returned.

Set a useful result size

Add LIMIT to cap how many documents SCOOP returns. It is useful for result pages, previews, or any view that should show an initial slice instead of every matching record. Pair it with a deliberate sort so the limited set contains the records you meant to prioritize.

SCOOP products WHERE category = "Books" ARRANGED BY price GOING DOWN LIMIT 5

This query first narrows the catalog to books, then orders those matches by descending price, and finally keeps the first five. The limit now means “the five highest-priced books,” rather than an arbitrary five books. CleaveDB can use an index when one supports the requested field; without one, the documents are evaluated and the requested ordering is applied to the result.

Return only the fields the caller needs

Use YIELD to project selected fields from each document. This is especially useful when the caller only needs a few values, such as a list view showing a product’s title and price rather than every stored attribute. Projection reduces the amount of document data sent back to the application.

SCOOP products YIELD title, price
SCOOP users WHERE city = "Manila" YIELD name, age

In the second query, the filter decides which users qualify and YIELD decides which fields appear in the result. Combine these ideas with ordering and a limit: first define the records the caller wants, then return them in a useful order and shape.