Query error when transitive join field has `hasMany: true`

[hai-trung-le]

Describe the Bug

With the below collections:

[{
  slug: "course",
  access: { read: () => true },
  fields: [
    {
      name: "title",
      type: "text",
      required: true,
      unique: true,
    },
    {
      name: "lessons",
      type: "relationship",
      relationTo: "lesson",
      hasMany: true,
    },
  ],
},
{
  slug: "lesson",
  access: {
    read: () => ({
      ["course.title"]: {
        equals: "c1",
      },
    }),
  },
  fields: [
    {
      name: "course",
      type: "join",
      collection: "course",
      on: "lessons",
    },
    {
      name: "quest",
      type: "relationship",
      relationTo: "quest",
    },
  ],
},
{
  slug: "quest",
  access: {
    read: () => true,
  },
  fields: [
    {
      name: "lesson",
      type: "join",
      collection: "lesson",
      on: "quest",
    },
    {
      name: "course",
      type: "relationship",
      relationTo: "course",
    },
  ],
}]

Running curl localhost:3000/api/quest fails with DrizzleQueryError

Failed query: select "id", "course_id", "updated_at", "created_at", (select coalesce(json_group_array(json_object('id',"lesson_alias".id)), '[]') from (select "a2dd1d37_b210_4f2e_b026_9455909ff695"."quest_id", "485c05b5_1d25_4ee3_b22b_1df3576a1f68"."title", "a2dd1d37_b210_4f2e_b026_9455909ff695"."created_at", "a2dd1d37_b210_4f2e_b026_9455909ff695"."id" from "lesson" "a2dd1d37_b210_4f2e_b026_9455909ff695" left join "course_rels" "51981943_2e23_49bd_a5bb_7e4bff1ed8de" on ("lesson"."id" = "51981943_2e23_49bd_a5bb_7e4bff1ed8de"."lesson_id" and "51981943_2e23_49bd_a5bb_7e4bff1ed8de"."path" like ?) left join "course" "485c05b5_1d25_4ee3_b22b_1df3576a1f68" on "51981943_2e23_49bd_a5bb_7e4bff1ed8de"."parent_id" = "485c05b5_1d25_4ee3_b22b_1df3576a1f68"."id" where ("a2dd1d37_b210_4f2e_b026_9455909ff695"."quest_id" = "quest"."id" and "485c05b5_1d25_4ee3_b22b_1df3576a1f68"."title" = ?) order by "a2dd1d37_b210_4f2e_b026_9455909ff695"."created_at" desc limit ?) "lesson_alias") as "lesson_alias" from "quest" "quest" order by "quest"."created_at" desc limit ?
params: lessons,c1,11,10: SQLITE_ERROR: no such column: lesson.id: no such column: lesson.id

Link to the code that reproduces this issue

https://github.com/hai-trung-le/payload-join-bug/tree/transitive-join-hasmany

Reproduction Steps

• Clone repo
• Start npm run dev
• Run curl 'localhost:3000/api/quest'

Which area(s) are affected?

area: core, db: postgres, db: sqlite

Environment Info

Binaries:
  Node: 24.14.0
Relevant Packages:
  payload: 3.80.0
  next: 16.2.1
  @payloadcms/db-sqlite: 3.80.0
  @payloadcms/drizzle: 3.80.0
  @payloadcms/graphql: 3.80.0
  @payloadcms/next/utilities: 3.80.0
  @payloadcms/richtext-lexical: 3.80.0
  @payloadcms/translations: 3.80.0
  @payloadcms/ui/shared: 3.80.0
  react: 19.2.4
  react-dom: 19.2.4
Operating System:
  Platform: win32

[hai-trung-le]

Seems to be a missing usage of aliasTable when adding join condition:

payload/packages/drizzle/src/queries/getTableColumnFromPath.ts

Lines 397 to 402 in 77cdb17

	eq(
	adapter.tables[rootTableName].id,
	aliasRelationshipTable[
	`${(relationshipField.field as RelationshipField).relationTo as string}ID`
	],
	),

The line adapter.tables[rootTableName].id, should be (aliasTable || adapter.tables[rootTableName]).id instead. This branch is only reached when:

• Collection quest joins on lesson
• Collection lesson joins on course
• Collection course has a relationship field to lesson with hasMany: true

Patching this fixes the error.