Developers should be able to define CTEs using the sql escape hatch.
Current State
CTE definitions use a query builder that can be of type TypedQueryBuilder<TSelection, unknown> or (qb: QueryBuilder) => TypedQueryBuilder<TSelection, unknown>). Developers need to implement a custom query builder if they use the first type option or are limited to selects with they use the second option.
Candidate API
Something like this would allow access to the underlying sql:
db.$with('my-cte)
.as(sql<MyCustomType[]>`
select * from sometable
`)
Additional Context
I'm reaching for this because I have a CTE that makes use of a lateral join, which is not currently supported in the select API. I'm watching this MR, which appears to add this support: https://github.com/drizzle-team/drizzle-orm/pull/1079.
mwanago
commented
1 month ago
Describe what you want
Summary
Developers should be able to define CTEs using the
sqlescape hatch.Current State
CTE definitions use a query builder that can be of type
TypedQueryBuilder<TSelection, unknown>or(qb: QueryBuilder) => TypedQueryBuilder<TSelection, unknown>). Developers need to implement a custom query builder if they use the first type option or are limited to selects with they use the second option.Candidate API
Something like this would allow access to the underlying sql:
Additional Context
I'm reaching for this because I have a CTE that makes use of a lateral join, which is not currently supported in the select API. I'm watching this MR, which appears to add this support: https://github.com/drizzle-team/drizzle-orm/pull/1079.