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

[tobiasiv]

Hey @r1tsuu - Thanks for the fix! It seems like the depth parameter isn't working as expected. It only functions for depths 0 and 1. In my use case, I have nested categories up to depth 5, for example.

[DanRibbens]

Hey @tobiasiv, #10182 was merged but not released yet. Can you verify after we publish the next version? Looking like it will be later today.

[github-actions]

🚀 This is included in version v3.12.0

[tobiasiv]

Hey @r1tsuu and @DanRibbens – I rechecked the issue after the 3.12.0 release. It looks like the depth for join fields still only supports values of 0 and 1.

[r1tsuu]

Hey @r1tsuu and @DanRibbens – I rechecked the issue after the 3.12.0 release. It looks like the depth for join fields still only supports values of 0 and 1.

Hey! Could you open a new issue for this? I think it's different from what was fixed, we will look into this in either way but that would be easier to track

[github-actions]

This issue has been automatically locked.
Please open a new issue if this issue persists with any additional detail.