Travis Bickle Posts - Answer Overflow

Travis Bickle

•Created by Travis Bickle on 2/18/2024 in #help

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]);

  // 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

 ⨯ 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.

3 replies

Gaming

Programming