Drizzle TeamDT
Drizzle Team•2y ago
dewodt

Subquery in insert().values()

I'm trying to use a subquery inside insert().values() but I can't seemed to solve this error.

Full schema:
https://github.com/dewodt/guess-astro/blob/main/db/schema.ts

Code:
  // If parsing success
  const userAnswer = zodParseResult.data;

  const correctAnswer = db
    .select()
    .from(astronomicalObject)
    .where(eq(astronomicalObject.id, userAnswer.id))
    .as("correctAnswer");

  const res = await db
    .insert(match)
    .values({
      userId: session.id,
      astronomicalObjectId: userAnswer.id,
      mode: userAnswer.mode,
      result: sql`CASE WHEN ${correctAnswer.name} = ${userAnswer.answer} THEN 'correct'::result ELSE 'incorrect'::result END`,
    })
    .returning();

  console.log(res[0]);


The error says
 ⨯ node_modules/@neondatabase/serverless/index.js (1539:47) @ execute
 ⨯ NeonDbError: missing FROM-clause entry for table "correctAnswer"
    at async MatchAction (./actions/match.ts:64:17)
null


I've spent hours on this one. I'm suspecting that drizzle doesnt support subqueries inside of insert()? Or is there something wrong with my query? Thank you.
GitHub
guess-astro/db/schema.ts at main · dewodt/guess-astro
Guess Astro is a website to help students memorize astronomical objects for astronomy national/international science olympiad. - dewodt/guess-astro
Was this page helpful?