Postgres: Self-Join Not Working

[tobiasiv]

Describe the Bug

When using a PostgreSQL database and creating a join field on a collection that references itself (self-join), the referenced documents are not displayed or fetched as expected. Specifically:

• The referenced documents do not appear in the corresponding collection's join column in the collection table.
• Column sorting does not affect the relationship column.
• The referenced documents are not accessible through the APIs (Local and REST).

Link to the code that reproduces this issue

https://github.com/tobiasiv/payload-reproduction/tree/self-join

Reproduction Steps

1. Clone the reproduction repo (branch: self-join)
2. Set the env variables
3. pnpm dev
4. Go to collection Categories and open the document Parent
5. Verify it references one child and five posts
6. Open the API Tab (Post docs are fetched, but no Child docs are fetched)
7. Go Back to Categories Collection and make sure the Name, Parent and Children Columns are visible
8. There are no Children visible in the Children Column of the Parent Category (There should be one child)
9. If you try to sort the Parent Column, it does not change. It seems like it is changing the Name Columns Order.

Which area(s) are affected? (Select all that apply)

db-postgres, area: ui

Environment Info

Binaries:
  Node: 20.9.0
  npm: 10.1.0
  Yarn: 1.22.19
  pnpm: 9.7.0
Relevant Packages:
  payload: 3.11.0
  next: 15.1.0
  @payloadcms/db-mongodb: 3.11.0
  @payloadcms/db-postgres: 3.11.0
  @payloadcms/email-nodemailer: 3.11.0
  @payloadcms/graphql: 3.11.0
  @payloadcms/next/utilities: 3.11.0
  @payloadcms/payload-cloud: 3.11.0
  @payloadcms/richtext-lexical: 3.11.0
  @payloadcms/translations: 3.11.0
  @payloadcms/ui/shared: 3.11.0
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: darwin
  Arch: x64
  Version: Darwin Kernel Version 19.6.0: Tue Feb 15 21:39:11 PST 2022; root:xnu-6153.141.59~1/RELEASE_X86_64
  Available memory (MB): 16384
  Available CPU cores: 4