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.
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:
import { createQueryBuilder } from '@hypequery/clickhouse';
const builder = createQueryBuilder(schema);
// Create a subquery builder
const activeUsersSubquery = builder
.table('users')
.select(['id', 'user_name', 'email'])
.where('status', 'eq', 'active');
// Use it as a CTE in the main query
const results = await builder
.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();Scalar WITH Aliases
ClickHouse also supports scalar expressions in the WITH clause. Use withScalar() when you want to define an expression once and reuse it in SELECT, WHERE, and ORDER BY.
const results = await builder
.table('orders')
.withScalar('user_name', expr =>
expr.ch.dictGet('users_dict', 'name', expr.col('user_id'))
)
.select(['order_id', 'amount', 'user_name'])
.where('user_name', 'like', '%Alice%')
.orderBy('user_name', 'ASC')
.limit(50)
.execute();This generates:
WITH dictGet('users_dict', 'name', user_id) AS user_name
SELECT order_id, amount, user_name
FROM orders
WHERE user_name LIKE '%Alice%'
ORDER BY user_name ASC
LIMIT 50Raw 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();