Drizzle Team•15mo ago

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

      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"'

'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"'

11 Replies

Angelelz•15mo ago

This is basically a self join, you need to create an alias to be able to create a self join like this

Luxaritas•15mo ago

I’ll also copy from #general - That does mirror SQL behavior - you can’t select * from two joined tables that have a shared ID. You have to select with aliases in the select tied to that join

francisOP•15mo ago

of course - I was expecting drizzle to be smart and use the mapped field names from my subquery in order to select them from the second query see my follow-up comment:

      const orderedQuestions = tx
        .select({
          id: surveyQuestion.id,
          sectionId: surveySection.id,
        })
        .from(surveyQuestion)
        .leftJoin(surveySection, eq(surveySection.id, surveyQuestion.surveySectionId))
        .orderBy(surveySection.index, surveyQuestion.index)
        .as("orderedQuestions");

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

      const orderedQuestions = tx
        .select({
          id: surveyQuestion.id,
          sectionId: surveySection.id,
        })
        .from(surveyQuestion)
        .leftJoin(surveySection, eq(surveySection.id, surveyQuestion.surveySectionId))
        .orderBy(surveySection.index, surveyQuestion.index)
        .as("orderedQuestions");

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

leads to:

'select "id" from (select "survey_question"."id", "survey_section"."id" from "survey_question" left join "survey_section" on "survey_section"."id" = "survey_question"."survey_section_id" order by "survey_section"."index", "survey_question"."index") "orderedQuestions"'

'select "id" from (select "survey_question"."id", "survey_section"."id" from "survey_question" left join "survey_section" on "survey_section"."id" = "survey_question"."survey_section_id" order by "survey_section"."index", "survey_question"."index") "orderedQuestions"'

in this case, there is no way in drizzle to disambiguate the id field - since both orderedQuestions.id and orderedQuestions.sectionId both generate "id" in the select clause I ended up doing it with

sql`${surveySection.id}`.as("sectionId")

sql`${surveySection.id}`.as("sectionId")

which feels extra silly to drop down to raw sql just to rename a column.... for the first example, if orderedQuestions.survey_question.id generated "survey_question"."id" rather than "id", it would work fine - this isn't a "select *" problem, this is an ambiguous sql generation problem, I think for the second example, if the specific field selection generated aliases for the fields, it would work (or if the column reference tracked the table name it came from)

Angelelz•15mo ago

What is the result if you delete the .as("orderedQuestions") from your follow up comment?

francisOP•15mo ago

doesn't work you are required to have an .as() expression in order to use a query as a selection source causes an error in the .from(orderedQuestions) call: Argument of type 'PgSelect<"survey_question", { id: PgColumn<{ name: "id"; tableName: "survey_question"; dataType: "string"; columnType: "PgUUID"; data: string; driverParam: string; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, {}, {}>; sectionId: PgColumn<...>; }, "partial", Record<...> & { ...; }>' is not assignable to parameter of type 'SQL<unknown> | PgTable<TableConfig> | Subquery<string, unknown> | PgViewBase<string, boolean, ColumnsSelection>'. Type 'PgSelect<"survey_question", { id: PgColumn<{ name: "id"; tableName: "survey_question"; dataType: "string"; columnType: "PgUUID"; data: string; driverParam: string; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }, {}, {}>; sectionId: PgColumn<...>; }, "partial", Record<...> & { ...; }>' is missing the following properties from type 'SQL<unknown>': queryChunks, shouldInlineParams, append, toQuery, and 4 more. I think these are two separate issues - in the first case (the first question I posed), the orderedQuestions.survey_question.id field should generate "survey_question"."id". In the second case, either the field should be renamed as an alias in the subquery itself, or the orderedQuestions.sectionId field should retain the reference to the source table in the subquery in order to generate "survey_section"."id"

Luxaritas•15mo ago

I was expecting drizzle to be smart and use the mapped field names from my subquery in order to select them from the second query

To be clear: the keys provided to the object passed to select are not used as aliases in SQL, just when the result is mapped back to JS. Though I don't disagree it could be helpful to let drizzle handle more of that itself Though it gets a bit more complicated given the contents of the query are constructed pretty directly from the objects you use, so implicitly aliasing could cause a number of issues

Angelelz•15mo ago

        .select({
          id: sql`${surveyQuestion.id} as id`,
          sectionId: sql`${surveySection.id} as sectionId`,
        })

        .select({
          id: sql`${surveyQuestion.id} as id`,
          sectionId: sql`${surveySection.id} as sectionId`,
        })

How about like this?

francisOP•15mo ago

that's what I ended up doing - it just seems silly that drizzle has no automatic handling when using a join as a subquery (or even that you can't call .as on a column, but have to drop down to raw sql first)

Andrii Sherman•15mo ago

cc @bloberenober @alexblokh we need to consider this @francis4629 @luxaritas @angelelz thanks for suggestions, we will try to improve this part

PizzaConsole•13mo ago

+1 https://discord.com/channels/1043890932593987624/1161430556312084511

NeonCop•6mo ago

+ 1 my use case here is joining tables a and b which both have id field, and flatten the results in one table but not the other (so result is something like {id, field1FromA, field2fromA, b: {id, field1fromB}}). if i just execute that query, it correctly creates an object with that shape. But if I create a subquery called c, and then try to use c in a join (using c.id i.e. a.id), then it says ambiguous column because the underlying select selects it twice. This is probably some sort of nice runtime magic to nest objects but ends up messing up the sql downstream.

Gaming

Programming

Drizzle join in a subquery results in ambiguous columns