db-postgres: querying a relationship inside a block returns no rows unless that block is the first one in blocks #18272

Describe the Bug

On Postgres, a where on a hasMany relationship that lives inside a block (stored in _rels) returns zero documents, with no error, whenever that block is not the first entry of the blocks array. Every operator is affected, including exists: true, and dot-notation into the related document (layout.groups.documents.title). Scalar fields of the same block query correctly. Moving the block to the first position makes the exact same queries return the right rows.

// blocks: [textA, textB, documentList]  -> documentList is third
await payload.find({
  collection: 'pages',
  where: { 'layout.groups.documents': { equals: docId } },
}) // -> docs: [] (expected the page that lists docId)

Cause. In packages/drizzle/src/queries/getTableColumnFromPath.ts, the blocks branch tries each block in a .some() loop and reassigns the outer constraintPath on every iteration:

const hasBlockField = (field.blockReferences ?? field.blocks).some((_block) => {
  // ...
  constraintPath = `${constraintPath}${field.name}.%.`

So the Nth block tried inherits N copies of layout.%.. With three blocks, the LIKE bound to _rels.path is:

layout.%.layout.%.layout.%.groups.%.documents

while the stored path is layout.1.groups.0.documents, so nothing ever matches. The pattern is only correct for the first block. Scalar leaves do not go through this path pattern, which is why they are unaffected.

A per-iteration local passed down to the recursive call, instead of mutating the shared variable, should fix it. The line is still present on the 3.x branch (getTableColumnFromPath.ts:244, 3.90.2). On main (v4 canary) this branch was rewritten to pass ${fieldStoragePath}. to the recursive call without reassigning, so v4 does not appear to be affected: this is a request for a fix on v3.

Also reported alongside, same reproduction: #18273 (a join whose on crosses a block crashes every read). Related but distinct: #15593 reworks constraintPath / queryPath tracking for relationship joins, but does not touch this block loop.

Link to the code that reproduces this issue

https://github.com/emmpauldev/payload-repro-block-relationships

Reproduction Steps

1. Start an empty Postgres and set DATABASE_URI (see the README).
2. pnpm install && pnpm repro: documentList is the last of three blocks. The scalar query passes, the 4 relationship queries fail with [].
3. Empty the database, then BLOCK_ORDER=first pnpm repro: same config with documentList first. All 5 queries pass.

Which area(s) are affected?

db: postgres

Environment Info

payload: 3.90.2 (also reproduced on 3.88.0)
@payloadcms/db-postgres: 3.90.2
Postgres: 16
Node.js: 22
No Next.js, no plugins (Local API script)