Can't use `where` filter with relationship 4 levels deep (or more)

[ryami333]

Describe the Bug

Imagine a taxonomy where 5 or more collections are related to each other, like this:

Company -> Team -> Employee -> Project -> Task

Now imagine that you might want to query for all of the tasks that are held by one team. You could (and can) do this with Payload, like this:

payload.find({
  collection: "tasks",
  where: {
    "project.employee.team": {
      equals: myTeamId,
    }
  }
});

This works correctly, but if you go one-level deeper, and try to (for example) get all of the tasks held by the entire company, you encounter a bug. This would return an empty set of docs:

payload.find({
  collection: "tasks",
  where: {
    "project.employee.team.company": {
      equals: myCompanyId,
    }
  }
});

Link to the code that reproduces this issue

https://github.com/ryami333/payload-nested-where-repro/tree/main/test/_community

Reproduction Steps

1. Create at least 5 collections, each associated to the next by a "relationship" field (Eg. Companies, Teams, Employees, Projects, Tasks)
2. Populate each collection with docs, using the relationship fields to "nest" the data like this:

Apple/
├── …
└── Engineering/
    ├── …
    └── Mitch/
        ├── …
        └── Data Lake Migration/
            ├── …
            ├── Design Schema Migration
            ├── Implement Data Transfer
            ├── Validate Data Integrity
            └── Decommission Old System

3. Try to query across 4 (or more) relationship boundaries like in my example above (eg. "get all tasks in my company").
4. You'll receive an empty array of docs.

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

Not sure

Environment Info

See reproduction.

[paulpopus]

Hi I just tested your example there using this exact query

http://localhost:3000/api/neighbourhoods?depth=5&draft=false&where["city.state.country.planet"]["equals"]=6846f4b133fb6a6d74a3f40d

I think your issue is likely a depth issue, in your local API you can provide a depth: 4 parameter or depth=4 in REST API. Increase the depth as needed here to go deeper.

If you still can't get the documents I'd check your permissions as well.

If you go very deep I'd recommend you use the populate parameter to optimise your queries here so you're only fetching the fields you need https://payloadcms.com/docs/queries/select#populate

Closing this for now but let us know if this doesn't fix it!

[ryami333]

@paulpopus I'd like to urge you to re-open this, I put a lot of time and effort into first debugging this, and then creating a reproduction which demonstrates a failing test case. As you'll see in my reproduction, I'm not using the Rest API but the Local API. I've just added a new commit to my repro (adding depth: 99) to prove that it still fails.

[paulpopus]

Sorry I missed that you added int tests, thank you! Managed to verify the issue now, as a workaround until we fix this the REST API doesnt seem to be affected

[ryami333]

Awesome, thanks! Good to know I've got a workaround in the meantime.