dev0 Posts - Answer Overflow

dev0

•Created by dev0 on 5/17/2024 in #help

Effectively mixing QueryBuilder and sql`` using CTEs

Is it possible to amend Drizzle QueryBuilder queries with custom SQL? For example, I have a query composing of an update, a CTE and a returning clause. The query however cannot execute because its missing a FROM clause that actual "connects" the CTE and the update query. Drizzle does not support from on its update or insert query builders. I'd like to "hack" that from clause in the otherwise QueryBuilder constructed query. Like sql``.append, but for QueryBuilder. Is there a way to do that? Example code:

const q = await db
    .with(myCte)
    .update(myTable)
    .set({ myCol: 'val' })
    // Eample 1: This API doesnt exist
    //.from(myCte)
    // Example 2: This API doesnt exist
    //.appendSQL(sql`from ${myCte}`)
    .where(eq(myTable.myCol. myCte.myCol))
    .returning({col: myCte.myCol});

const q = await db
    .with(myCte)
    .update(myTable)
    .set({ myCol: 'val' })
    // Eample 1: This API doesnt exist
    //.from(myCte)
    // Example 2: This API doesnt exist
    //.appendSQL(sql`from ${myCte}`)
    .where(eq(myTable.myCol. myCte.myCol))
    .returning({col: myCte.myCol});

If not, how do I obtain the SQL for a CTE? I could construct the update query manually and just pull in the CTE "pre-built" into the custom sql. For example:

  const customSql = sql`with ${myCte} update ${myTable} set ${myTable.myCol} = ${'val'} from ${myCte} where ${myTable.myCol} = ${myCte.myCol} returning ${myCte.myCol}`

  const customSql = sql`with ${myCte} update ${myTable} set ${myTable.myCol} = ${'val'} from ${myCte} where ${myTable.myCol} = ${myCte.myCol} returning ${myCte.myCol}`

The ${myCte} use just inserts the CTE's alias, which is the required behavior in the second case, but not the first where I need the actual CTE table sql. How do I get that so I can construct my own custom update using CTEs?

5 replies

Gaming

Programming