Subqueries & CTEs
Learn how to use subqueries and Common Table Expressions (CTEs) in hypequery
Subqueries & CTEs
hypequery provides support for Common Table Expressions (CTEs) and raw SQL capabilities that allow you to create complex queries with subqueries.
Serve context
These examples live inside query(({ ctx }) => …) resolvers. Alias const db = ctx.db; to keep the snippets concise.
Common Table Expressions (CTEs)
CTEs are temporary result sets that you can reference within a query. They help make complex queries more readable and maintainable.
Using QueryBuilder as a CTE
You can use another QueryBuilder instance as a CTE:
const activeUsersSubquery = ctx.db
.table('users')
.select(['id', 'user_name', 'email'])
.where('status', 'eq', 'active');
const results = await ctx.db
.table('orders')
.withCTE('active_users', activeUsersSubquery)
.select([
'orders.id',
'orders.total',
'active_users.user_name'
])
.innerJoin('active_users', 'user_id', 'active_users.id')
.execute();This will generate SQL similar to:
WITH active_users AS (
SELECT id, user_name, email
FROM users
WHERE status = 'active'
)
SELECT orders.id, orders.total, active_users.user_name
FROM orders
INNER JOIN active_users ON orders.user_id = active_users.idUsing Raw SQL as a CTE
For more complex subqueries, you can use raw SQL strings:
const results = await builder
.table('orders')
.withCTE(
'monthly_totals',
'SELECT user_id, toStartOfMonth(created_at) as month, SUM(total) as monthly_sum FROM orders GROUP BY user_id, month'
)
.select([
'orders.id',
'orders.created_at',
'monthly_totals.monthly_sum'
])
.innerJoin('monthly_totals', 'user_id', 'monthly_totals.user_id')
.where('orders.created_at', 'gte', 'monthly_totals.month')
.execute();Multiple CTEs
You can chain multiple CTEs for complex analytics:
const results = await builder
.table('events')
.withCTE(
'daily_users',
'SELECT user_id, toDate(timestamp) as day, COUNT(*) as event_count FROM events GROUP BY user_id, day'
)
.withCTE(
'active_users',
'SELECT user_id, COUNT(DISTINCT day) as active_days FROM daily_users GROUP BY user_id HAVING active_days > 7'
)
.select(['events.*'])
.innerJoin('active_users', 'user_id', 'active_users.user_id')
.execute();Raw SQL Expressions
For complex conditions that can't be expressed using the fluent API, you can use raw SQL expressions:
HAVING Clauses
The raw() method allows you to add custom conditions to the HAVING clause:
const results = await builder
.table('orders')
.select(['user_id'])
.sum('total', 'total_spent')
.groupBy(['user_id'])
.raw('SUM(total) > 1000')
.raw('COUNT(DISTINCT product_id) >= 3')
.execute();This will generate:
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000 AND COUNT(DISTINCT product_id) >= 3Limitations and Workarounds
hypequery doesn't directly support nested subqueries in WHERE clauses, but you can work around this with CTEs or raw SQL expressions:
Example: IN Subqueries
To achieve a query like:
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active = 1)Use a CTE approach:
// Create a CTE for active categories
const results = await builder
.table('products')
.withCTE(
'active_categories',
'SELECT id FROM categories WHERE active = 1'
)
.select(['products.*'])
.innerJoin('active_categories', 'category_id', 'active_categories.id')
.execute();