import type { CollectionConfig } from 'payload'

export const Products: CollectionConfig = {
  slug: 'products',
  fields: [
    {
      name: 'brandRelation',
      label: 'Relation to brand',
      type: 'relationship',
      relationTo: 'brands',
    },
  ],
}

import type { CollectionConfig } from 'payload'

export const Brands: CollectionConfig = {
  slug: 'brands',
  fields: [
    {
      name: 'products',
      type: 'join',
      collection: 'products',
      on: 'brandRelation',
    },    
  ],
}

[Error: Failed query: select "id", "updated_at", "created_at", (select coalesce(json_agg(json_build_object('id',"products_alias".id)), '[]'::json) from (select "brand_relation_id", "created_at", "id" from "payloadJason4"."products" "ba1d38d5_a77d_4b20_a729_f620966e14e4" where "ba1d38d5_a77d_4b20_a729_f620966e14e4"."brand_relation_id" = "payloadJason4"."brands"."id" order by "ba1d38d5_a77d_4b20_a729_f620966e14e4"."created_at" desc limit $1) "products_alias") as "products_alias" from "payloadJason4"."brands" "brands" order by "brands"."created_at" desc limit $2
params: 11,10] {
  query: `select "id", "updated_at", "created_at", (select coalesce(json_agg(json_build_object('id',"products_alias".id)), '[]'::json) from (select "brand_relation_id", "created_at", "id" from "payloadJason4"."products" "ba1d38d5_a77d_4b20_a729_f620966e14e4" where "ba1d38d5_a77d_4b20_a729_f620966e14e4"."brand_relation_id" = "payloadJason4"."brands"."id" order by "ba1d38d5_a77d_4b20_a729_f620966e14e4"."created_at" desc limit $1) "products_alias") as "products_alias" from "payloadJason4"."brands" "brands" order by "brands"."created_at" desc limit $2`,
  params: [Array],
  [cause]: [error: invalid reference to FROM-clause entry for table "brands"] {
    length: 227,
    severity: 'ERROR',
    code: '42P01',
    detail: 'There is an entry for table "brands", but it cannot be referenced from this part of the query.',
    hint: undefined,
    position: '317',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_relation.c',
    line: '3640',
    routine: 'errorMissingRTE'
  }
}

Node.js v22.12.0

Binaries:
  Node: 22.12.0
  npm: 11.1.0
  Yarn: 3.2.3
  pnpm: N/A
Relevant Packages:
  payload: 3.43.0
  next: 15.3.0
  @payloadcms/db-postgres: 3.43.0
  @payloadcms/graphql: 3.43.0
  @payloadcms/next/utilities: 3.43.0
  @payloadcms/richtext-lexical: 3.43.0
  @payloadcms/translations: 3.43.0
  @payloadcms/ui/shared: 3.43.0
  react: 19.1.0
  react-dom: 19.1.0