Skip to main content

Common Table Expressions (CTE)

CTEs allow you to build reusable query fragments and simplify complex queries by breaking them into named subqueries.

Example Usage​

const users = await User.query()
  .with('users_cte', (qb) => qb.select('name'))
  .many();

Types of CTEs​

Normal CTE​

const users = await User.query()
  .with('users_cte', (qb) => qb.select('name'))
  .with('users_cte2', (qb) => qb.select('age'))
  .many();

Recursive CTE​

const users = await User.query()
  .withRecursive('users_cte', (qb) => qb.select('name'))
  .withRecursive('users_cte2', (qb) => qb.select('age'))
  .many();

Materialized CTE (PostgreSQL/CockroachDB only)​

const users = await User.query()
  .withMaterialized('users_cte', (qb) => qb.select('name'))
  .many();

CTEs can be used for:

  • Recursive queries
  • Simplifying multi-step data transformations
  • Improving query readability

See also:

Next: MongoDB Introduction