Query on array in relation fails with SQL error (table alias is not used properly)

[bohemima]

Link to reproduction

https://github.com/bohemima/payload2-nested-docs-bug

Describe the Bug

nested-docs plugin is used to create a hierarchy of tenants, tenants can "own" contacts, a tenant should be able to see contacts for all sub-tenants.

Set up

• Copy .env.example to .env
• Run docker compose up

Two tenants and two contacts will automatically be created.

The bug

Try to filter contacts based on tenant.breadcrumbs.doc to only get contacts that we should be able to see.

http://localhost:3000/api/contacts?where[tenant.breadcrumbs.doc][in]=_tenant1-id_

Observe that the SQL statement tries to reference the tenants table in the ON clause instead of using the table alias. Thus throwing an error.

invalid reference to FROM-clause entry for table "tenants" at character 593

Perhaps you meant to reference the table alias "1b937a69_0449_4ff5_895f_fec5beffd239".

select distinct "contacts"."id", "contacts"."created_at", "670f15a0-8bd5-4182-b3ea-8fd4c2877452"."path", "6d2c7211-9788-4f6a-b473-cde127ba3165"."path"
from "contacts"
left join "contacts_rels" "670f15a0-8bd5-4182-b3ea-8fd4c2877452" on ("contacts"."id" = "670f15a0-8bd5-4182-b3ea-8fd4c2877452"."parent_id" and "670f15a0-8bd5-4182-b3ea-8fd4c2877452"."path" like $1)
left join "tenants" "1b937a69_0449_4ff5_895f_fec5beffd239" on "1b937a69_0449_4ff5_895f_fec5beffd239"."id" = "670f15a0-8bd5-4182-b3ea-8fd4c2877452"."tenants_id"
left join "tenants_rels" "6d2c7211-9788-4f6a-b473-cde127ba3165" on ("tenants"."id" = "6d2c7211-9788-4f6a-b473-cde127ba3165"."parent_id" and "6d2c7211-9788-4f6a-b473-cde127ba3165"."path" like $2)
left join "tenants" "230c0085_8ece_46c2_afb4_ea30a99a5d91" on "230c0085_8ece_46c2_afb4_ea30a99a5d91"."id" = "6d2c7211-9788-4f6a-b473-cde127ba3165"."tenants_id"
left join "tenants_breadcrumbs" on "tenants"."id" = "tenants_breadcrumbs"."_parent_id"
where "6d2c7211-9788-4f6a-b473-cde127ba3165"."tenants_id" in ($3)
order by "contacts"."created_at" desc limit $4

To Reproduce

• Clone https://github.com/bohemima/payload2-nested-docs-bug
• Copy .env.example to .env
• Run docker compose up

Follow the instructions in the console, two tenants and two contacts will be created for you.

Even if nested-docs plugin is used here, the error is most likely not related to the plugin but the query parser / sql generation.

If the query properly uses the table aliases it will succeed.

Payload Version

2, 3

Adapters and Plugins

db-postgres, nested-docs