Queries

Routier queries are fluent and can only be performed through a collection. Build your query by chaining operations and finish with a terminal method to execute.

Quick Reference

Terminal Methods (Query Execution)

Method	Description	Example
toArrayAsync()	Get all results as an array	await ctx.products.toArrayAsync()
firstAsync()	Get first item (throws if none)	await ctx.products.firstAsync()
firstOrUndefinedAsync()	Get first item or undefined	await ctx.products.firstOrUndefinedAsync()
someAsync()	Check if any items match	await ctx.products.someAsync(p => p.active)
everyAsync()	Check if all items match	await ctx.products.everyAsync(p => p.active)
countAsync()	Count total items	await ctx.products.countAsync()
sumAsync(field)	Sum numeric field	await ctx.products.sumAsync(p => p.price)
minAsync(field)	Get minimum value	await ctx.products.minAsync(p => p.price)
maxAsync(field)	Get maximum value	await ctx.products.maxAsync(p => p.price)
distinctAsync()	Get unique values	await ctx.products.distinctAsync()
toGroupAsync(selector)	Group items by key	await ctx.products.toGroupAsync(p => p.category)

Query Operations (Chaining)

Method	Description	Example
where(predicate, params?)	Filter results	ctx.products.where(p => p.price > 100)
sort(field)	Sort ascending	ctx.products.sort(p => p.name)
sortDescending(field)	Sort descending	ctx.products.sortDescending(p => p.price)
map(selector)	Transform/select fields	ctx.products.map(p => ({ name: p.name }))
skip(count) / take(count)	Window results	ctx.products.skip(10).take(5)
join(inner, outerKey, innerKey)	Return matching tuples	ctx.teams.join(s => s.members, t => t.id, m => m.teamId)
leftJoin(...)	Keep unmatched left rows	ctx.teams.leftJoin(s => s.members, t => t.id, m => m.teamId)
groupJoin(...)	Each left row with an array of its matches	ctx.teams.groupJoin(s => s.members, t => t.id, m => m.teamId)
nearest(field, vector, count)	Rank vector similarity	ctx.products.nearest(p => p.embedding, query, 10)
search(terms, options?)	Ranked full-text search	ctx.products.search("copper pipe")
subscribe()	Enable live updates	ctx.products.subscribe().toArray(callback)
explain()	Return { data, explanation } from terminals	ctx.products.where(p => p.active).explain()

Major query features

• Joins — inner and left equi-joins, same-store and cross-store joins, tuple operations, scopes, and backend execution.
• Full-Text Search — ranked search over .searchable() strings.
• Vector Search — cosine-similarity ordering with s.vector() and .nearest().
• Reusable Queries — define a typed query with Queryable.compose() and execute it through collection.apply().
• Explain — see which options ran in the database, which ran in memory, and the statements the plugin executed.

Detailed Examples

Getting All Results

// Get all products
const allProducts = await ctx.products.toArrayAsync();

Getting Single Items

// Get first product (throws if none exist)
const firstProduct = await ctx.products.firstAsync();

// Get first product or undefined if none exist
const firstOrUndefined = await ctx.products.firstOrUndefinedAsync();

Checking Existence

// Check if any products exist
const hasProducts = await ctx.products.someAsync();

// Check if all products are in stock
const allInStock = await ctx.products.everyAsync((p) => p.inStock);

Counting Items

// Count total products
const totalCount = await ctx.products.countAsync();

// Count products in specific category
const electronicsCount = await ctx.products
  .where((p) => p.category === "electronics")
  .countAsync();

Filtering Data

// Simple filtering
const expensiveProducts = await ctx.products
  .where((p) => p.price > 100)
  .toArrayAsync();

// Multiple filters
const activeElectronics = await ctx.products
  .where((p) => p.category === "electronics")
  .where((p) => p.inStock === true)
  .toArrayAsync();

// Parameterized filtering
const productsInRange = await ctx.products
  .where((p, params) => p.price >= params.min && p.price <= params.max, {
    min: 50,
    max: 200,
  })
  .toArrayAsync();

Sorting Results

// Sort by price (ascending)
const productsByPrice = await ctx.products.sort((p) => p.price).toArrayAsync();

// Sort by price (descending)
const expensiveFirst = await ctx.products
  .orderByDescending((p) => p.price)
  .toArrayAsync();

// Multiple sort criteria
const sortedProducts = await ctx.products
  .sort((p) => p.category)
  .sort((p) => p.name)
  .toArrayAsync();

Field Selection and Transformation

// Select specific fields to reduce data transfer
const productSummaries = await ctx.products
  .map((p) => ({
    id: p.id,
    name: p.name,
    price: p.price,
  }))
  .toArrayAsync();

// Create computed fields on-the-fly
const productsWithTax = await ctx.products
  .map((p) => ({
    id: p.id,
    name: p.name,
    price: p.price,
    priceWithTax: p.price * 1.1,
  }))
  .toArrayAsync();

Pagination

// Get first 10 products
const firstPage = await ctx.products.take(10).toArrayAsync();

// Get second page (skip first 10, take next 10)
const secondPage = await ctx.products.skip(10).take(10).toArrayAsync();

// Pagination helper
const pageSize = 10;
const pageNumber = 2; // 0-based
const page = await ctx.products
  .skip(pageSize * pageNumber)
  .take(pageSize)
  .toArrayAsync();

Aggregation Operations

// Sum prices of in-stock products
const totalValue = await ctx.products
  .where((p) => p.inStock === true)
  .sumAsync((p) => p.price);

// Get minimum and maximum prices
const minPrice = await ctx.products.minAsync((p) => p.price);
const maxPrice = await ctx.products.maxAsync((p) => p.price);

// Get distinct categories
const categories = await ctx.products.map((p) => p.category).distinctAsync();

Complex Queries

// Complex query with multiple operations
const topExpensiveElectronics = await ctx.products
  .where((p) => p.category === "electronics")
  .where((p) => p.inStock === true)
  .orderByDescending((p) => p.price)
  .take(5)
  .map((p) => ({
    name: p.name,
    price: p.price,
    priceWithTax: p.price * 1.1,
  }))
  .toArrayAsync();

Key Concepts

Query Execution

• Lazy evaluation: Queries don't execute until you call a terminal method
• Chaining: You can chain multiple operations together
• Collection-based: All queries must start with a collection

Performance Tips

• Database filters first: Apply where clauses on database fields before computed fields
• Limit results: Use take() to limit large result sets
• Efficient pagination: Use skip() and take() for pagination

Computed Properties

When filtering on computed properties (not stored in database), the filter runs in memory:

// Good: Database-backed filter first
const expensiveElectronics = await ctx.products
  .where((p) => p.category === "electronics") // Database filter
  .where((p) => p.isExpensive === true) // Computed filter
  .toArrayAsync();

// Less efficient: Computed filter first
const allExpensive = await ctx.products
  .where((p) => p.isExpensive === true) // Loads all records
  .where((p) => p.category === "electronics") // Then filters
  .toArrayAsync();

Related Topics

• Filtering - Detailed filtering examples
• Sorting - Advanced sorting techniques
• Joins - Join collections and views
• Full-Text Search - Ranked text search
• Vector Search - Nearest-neighbor queries
• Field Selection - Data transformation
• Pagination - Pagination strategies
• Aggregation - Aggregation operations
• Terminal Methods - Query execution methods
• Reusable Queries - Queryable.compose() and collection.apply()