SoliDB Docs
v0.31.0 Home GitHub
Back to SDBQL Reference

Common Table Expressions (CTE)

Overview

Common Table Expressions (CTEs) allow you to define named temporary result sets that can be referenced within your query. They improve query readability and enable recursive data structures.

CTEs are defined using the WITH clause and can be referenced like collections using FOR ... IN.

Simple CTE

Example Query 
-- Define a CTE to get active users
WITH active_users AS (
  FOR u IN users
    FILTER u.active == true
    RETURN u
)
-- Now iterate over the CTE like a collection
FOR u IN active_users
  SORT u.name ASC
  RETURN u

Multiple CTEs

Example Query 
-- Get high-value orders with customer details
WITH 
  high_value_orders AS (
    FOR o IN orders
      FILTER o.total > 1000
      RETURN o
  ),
  vip_customers AS (
    FOR c IN customers
      FILTER c.tier == 'vip'
      RETURN c
  )
-- Join the CTEs
FOR o IN high_value_orders
  FOR c IN vip_customers
    FILTER o.customer_id == c._key
    RETURN {order: o, customer: c}

CTE with Column Names

Example Query 
WITH summary(id, name, total) AS (
  FOR o IN orders
    FILTER o.status == 'completed'
    RETURN {id: o._key, name: o.customer_name, total: o.total}
)
FOR s IN summary
  SORT s.total DESC
  RETURN s

CTEs vs LET

Feature LET CTE
Creates a named result set ❌ ✅
Can use FOR ... IN ✅ ✅
Supports multiple iterations ❌ ✅
Can reference earlier CTEs ❌ ✅
Use case Computed values Named subqueries

Syntax

WITH
  cte_name [(col1, col2, ...)] AS ( query ),
  cte_name2 [(col1, col2, ...)] AS ( query )
query_body
  • cte_name - Identifier for the CTE
  • (col1, col2, ...) - Optional column names
  • query - Any valid SDBQL query
  • query_body - Main query that can reference CTEs