Drizzle Team•11mo ago

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

2 Replies

Mykhailo•11mo ago

Hello, @dewodt! As for now, PR is in development https://github.com/drizzle-team/drizzle-orm/pull/1605

GitHub

Add support for insert into ... select by L-Mario564 · Pull Reque...

Addresses #398. This PR adds support to insert rows from one table into another via the insert into ... select syntax. The following is valid Drizzle syntax: const users = pgTable('users', ...

Travis BickleOP•11mo ago

I see, okay thank you for your respond!

Gaming

Programming

Subquery in insert().values()