Drizzle TeamDT
Drizzle Team•3y ago
francis

Drizzle join in a subquery results in ambiguous columns

This fails with id ambiguous error, since the orderedQuestions.survey_question.id reference in the field selection of the second query generates the sql as 'id' rather than 'survey_question'.'id' as it should.

      const orderedQuestions = tx
        .select()
        .from(surveyQuestion)
        .leftJoin(surveySection, eq(surveySection.id, surveyQuestion.surveySectionId))
        .where(eq(surveySection.surveyId, LIFESTYLE_AUDIT_SURVEY_ID))
        .orderBy(surveySection.index, surveyQuestion.index)
        .as("orderedQuestions");

      console.log(
        await tx
          .select({ id: orderedQuestions.survey_question.id })
          .from(orderedQuestions)
          .toSQL()
      );


Generates the following ambiguous sql:
'select "id" from (select "survey_question"."id", "survey_question"."survey_section_id", "survey_question"."index", "survey_question"."title", "survey_question"."description", "survey_question"."short_name", "survey_question"."type", "survey_question"."survey_product_category_id", "survey_question"."survey_exposure_id", "survey_question"."survey_question_text_option_id", "survey_question"."survey_question_answer_set_id", "survey_question"."created_by_id", "survey_question"."created_at", "survey_question"."updated_at", "survey_section"."id", "survey_section"."survey_id", "survey_section"."index", "survey_section"."name", "survey_section"."created_by_id", "survey_section"."created_at", "survey_section"."updated_at" from "survey_question" left join "survey_section" on "survey_section"."id" = "survey_question"."survey_section_id" where "survey_section"."survey_id" = $1 order by "survey_section"."index", "survey_question"."index") "orderedQuestions"'
Was this page helpful?