Allow passing a per relationship field depth in the select option of the Payload find Local API query to optimize performance

[MurzNN]

For now, we can set the depth globally for the Payload app, and override it individually for each API query. But loading all nested documents, referenced by relationship fields, usually negatively affects the performance of the database request, in result, Payoad users face performance issues, because by default the depth is 2.

But in most cases, developers don't need all the relationships exposed, especially with the depth=2 by default, but just several of them. Having all of them exposed is just "nice to have" but not with decreasing the performance.

The suggestion to resolve this issue is allow developers to pass the desired depth individually for each relationship field, to spend database resources on loading only the relationship documents that are really needed for this request.

But seems this is not possible with the current version of the Payload Local API, but, surprisingly, possible with the GraphQL API, but using GraphQL locally is not good for performance, and not very convenient, having the Local API available.

Here is an idea of how we can implement this:

In the find API query, we have a select option, where we can pass a list of fields with the boolean value, indicating whether it should be included or not.

We can extend this value for the relationship fields to be an object too, accepting the information about loading the referenced document and exposing its fields, something like this:

const result = await payload.find({
  collection: "posts",
  depth: 0,
  select: {
    id: true,
    title: true,
    authors: {
      name: true,
    },
  },
});

This is an example of a query for a page that displays just the list of posts with authors' names, like this:

• Post 1 by Alice
• Post 2 by Bob

The query will join only documents from the collection authors, wasting no additional resources on attaching other collections for all other relationship fields, making the page fast and lightweight.

What do you think about this idea? If there is some way to achieve this using the Payload Local API that already exists, please share a usage example.

[MurzNN]

The base issue for this feature was initially discussed here #15244 with a clearer use case of this feature: a "Product Selection" document on an e-commerce website, and a page displaying the list of the product selections:

• Product Selection 1 by User5 - 76 products.
• Product Selection 2 by User7 - 45 products.

The "Product Selection" document should contain a relationship field that references multiple products (up to 100).

An on the page "Product Selection List," we need the same thing as with posts: display the title of the selection and the author's name.

To load the author name, we should use depth=1 in the find query, but this will lead to loading from the database all up-to-100 products too, significantly degrading the performance of this page, so if we display 50 of the product selections on a page, it will lead to a redundant load of an average of 50 posts * ~50 referenced products = ~2500 additional document loads! And with depth=2, if the product has other relationship fields, even much more!

Yes, we can invent workarounds by loading the list with the depth=0 and then loading and attaching the user names manually via a separate query, but would be great to have a solution for this task out of the box.