2 replies

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.

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

Drizzle Team•2y ago•

2 replies

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

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

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

Subquery in insert().values()

Subquery in insert().values()

Similar Threads

Similar Threads

Similar Threads