CTEs and subqueries
Use db.with for CTEs, and aliased select queries as subqueries inside WHERE or joins.
CTEs
Use db.with(name, callback) to define a Common Table Expression. The CTE is
added to the database scope and you can selectFrom it like any other table.
const rows = await db
.with("active_users", (db) =>
db
.selectFrom("event_logs as e")
.select("e.user_id")
.where("e.event_type", "=", "signup")
.groupBy("e.user_id"),
)
.selectFrom("active_users as au")
.innerJoin("users as u", "u.id", "au.user_id")
.select("u.id", "u.email")
.execute();db.with(...) returns a new db value with that CTE in scope. The
original db is unchanged.
const withActiveUsers = db.with("active_users", (db) =>
db
.selectFrom("event_logs as e")
.select("e.user_id")
.where("e.event_type", "=", "signup")
.groupBy("e.user_id"),
);
withActiveUsers.selectFrom("active_users as au").select("au.user_id");
// type error: the original `db` does not have the CTE in scope
db.selectFrom("active_users as au");You can chain multiple .with(...) calls, and later CTEs can reference earlier
ones from the same chain:
const rows = await db
.with("active_users", (db) =>
db
.selectFrom("event_logs as e")
.select("e.user_id")
.where("e.event_type", "=", "signup")
.groupBy("e.user_id"),
)
.with("active_user_emails", (db) =>
db
.selectFrom("active_users as au")
.innerJoin("users as u", "u.id", "au.user_id")
.select("u.id", "u.email"),
)
.selectFrom("active_user_emails as aue")
.select("aue.id", "aue.email")
.execute();Pre-built query builders
You can also pass a pre-built SelectQueryBuilder directly instead of a
callback. This is useful when a CTE is assembled conditionally in a helper
function and then spliced into the main query:
function buildActiveUsersCte(includeSignup: boolean) {
let query = db
.selectFrom("event_logs as e")
.select("e.user_id")
.groupBy("e.user_id");
if (includeSignup) {
query = query.where("e.event_type", "=", "signup");
}
return query;
}
const rows = await db
.with("active_users", buildActiveUsersCte(true))
.selectFrom("active_users as au")
.innerJoin("users as u", "u.id", "au.user_id")
.select("u.id", "u.email")
.execute();The query builder is cloned via toAST(), so the original instance remains
safe to reuse or modify afterwards.
Prefer unique CTE names instead of reusing real table or view names. Quarry's type and schema metadata are much clearer when CTE names do not collide with existing sources.
Subqueries in WHERE
A select query can be used directly as the right-hand side of an IN
predicate:
const activeUsers = db
.selectFrom("event_logs as e")
.select("e.user_id")
.where("e.event_type", "=", "signup");
const rows = await db
.selectFrom("users as u")
.select("u.id", "u.email")
.where("u.id", "in", activeUsers)
.execute();The subquery is compiled inline into the parent query — this is not a two-round-trip pattern.
Subqueries as join sources
See Joining against a subquery in the Joins guide.