Is this the correct way to use With clause (CTEs) to delete a row using only one transaction?
export const deleteMainAccountUser = async (publicUserId: string, publicMainAccountId: string) => {
const userId = neonClient
.$with('user_id')
.as(neonClient.select({ id: user.id }).from(user).where(eq(user.publicUserId, publicUserId)))
const mainAccountId = neonClient
.$with('account_id')
.as(
neonClient
.select({ id: mainAccount.id })
.from(mainAccount)
.where(eq(mainAccount.publicId, publicMainAccountId))
)
return neonClient
.with(userId, mainAccountId)
.delete(mainAccountUser)
.where(
and(eq(mainAccountUser.userId, userId), eq(mainAccountUser.mainAccountId, mainAccountId))
)
}I first need to get both the userId and mainAccountId before removing the row in mainAccountUser, is this the best way to do it? Do I have to use sql in order to fully use the CTEs flow?
