Set depth for a single relationship field in the Payload Local API find method to optimize peformance

[MurzNN]

I have a collection with many relationship fields, let's name it 'ProductSelection', with relationship fields like this:

• title (text)
• owner (relationship to Users, single)
• tags (relationship to Tags, many)
• subscribers (relationship to Users, many)
• products (relationship to Product, many, usually - a lot)

And I need to create a page that should display all collections using the text, and the user.name values.

I plan to load all collections using the Payload Local API find method, but with default depth: 2, it will spend a lot of database resources to load documents from all relationship collections, right?

So, I need to specify only a concrete relationship field, which I need to populate, something like this:

payload.find({
  collection: 'product-selections',
  depth: 0,
  select: {
    title: true,
    owner: {
      name: true,
    },
  },
});

But this doesn't work.
I am aware that I can do this via several separate queries (find ProductSelections and then, for each selection, load the user data), but for performance reasons, it's better to do this using a single Payload find query.

So, is there any possibility of achieving this using a single payload.find() query, where explicitly describe which concrete relationship fields to populate with the desired depth?

[ismaildasci]

payload doesnt support per-field depth, but you can achieve this with joins or custom query:

1. use joins (payload 3.x):

const result = await payload.find({
  collection: "product-selections",
  depth: 0,
  joins: {
    owner: true,  // only populate owner
  },
});

2. or use select with depth 1:

const result = await payload.find({
  collection: "product-selections",
  depth: 1,
  select: {
    title: true,
    owner: true,
  },
});

with depth 1 only direct relationships get populated, nested ones stay as IDs

3. for more control use graphql:

query {
  ProductSelections {
    docs {
      title
      owner {
        name
      }
    }
  }
}

graphql naturally handles selective population

4. or make two queries with Promise.all:

const [selections, users] = await Promise.all([
  payload.find({ collection: "product-selections", depth: 0 }),
  payload.find({ collection: "users", where: { id: { in: ownerIds } } }),
]);

joins feature in v3 is probably what you need

[MurzNN]

Yeah, thanks, the joins feature works well, but seems not for my case, because it requires the relationship field on the target collection. The Join field works well for "categories + posts" case, when each post relates to one or more categories.

But my use case is creating the "post selections", so a separate document that has reference to many posts (can be many, up to 50), but the posts should have no backreference to those selections. For that case, the "Join" field will not work.

The optimal API for my case seems to be GraphQL, but using it locally on the server side will not be good for performance, right?

Wondering how the GraphQL resolver handles this request on the server side, if Payload doesn't provide this API? Seems it executes N+1 queries to fetch the owner's name for each product selection?

[MurzNN]

And about other options:

1. Doesn't suit the case
2. Together with the owner, will load up to 50 relationship documents with the "products" field, slowing down the execution for no profit.
3. Suits well, but has concerns about performance when executing locally, compared to the Local API request.
4. The parallel execution does not suit, to receive the list of owners I have to retrieve the result of the first request, so there will be two separate consecutive requests.

[ismaildasci]

ah i see, yeah joins need the field on other side so wont work for your case

about graphql locally - its not that bad actually. payload graphql resolver uses dataloader pattern internally so it batches the queries, not pure N+1. but yes there is some overhead compared to direct local api

for your case i think best options:

1. depth 1 with select - this should work:

const result = await payload.find({
  collection: "product-selections",
  depth: 1,
  select: {
    title: true,
    owner: true,  // will populate with depth 1
    // dont include tags, subscribers, products - they stay as IDs
  },
});

when you dont select a relationship field, it shouldnt populate it even with depth 1. can you try this?

2. if that doesnt work, manual approach but optimized:

const selections = await payload.find({
  collection: "product-selections",
  depth: 0,
});

// get unique owner ids
const ownerIds = [...new Set(selections.docs.map(s => s.owner).filter(Boolean))];

// single query for all owners
const owners = await payload.find({
  collection: "users",
  where: { id: { in: ownerIds } },
  limit: ownerIds.length,
});

// map them
const ownerMap = Object.fromEntries(owners.docs.map(u => [u.id, u]));
const result = selections.docs.map(s => ({
  ...s,
  owner: ownerMap[s.owner],
}));

only 2 queries total, doesnt matter how many selections you have

3. or use drizzle directly if you on payload 3.x with postgres:

import { db } from "@payloadcms/db-postgres";

const result = await db
  .select({
    title: productSelections.title,
    ownerName: users.name,
  })
  .from(productSelections)
  .leftJoin(users, eq(productSelections.owner, users.id));

this is most performant, single query with join

honestly option 2 is cleanest for your case imo

[MurzNN]

Thank you for the details! Option 1 works well, returning only listed field values, hope that the database query loads only listed relationships too ;)

But I still want to have in the response the list of ids from the ProductSelection's "products" field, to count them, cuz in UI I should display a list like this:

• Selection 1 by User1 (23 products)
• Selection 2 by User6 (34 products)

So, would be great to still have the ability to specify the depth value on a per-field basis, which should suit my case the best.

But until it exists, option 2 with two consecutive calls will be the optimal way, because it seems GraphQL does exactly the same, but with additional overhead on processing the GraphQL request.

Option 3 is the most performant and flexible (I use MongoDB and there can achieve the same using aggregations), but too low-level for such a simple task ;) And one more downside is that Payload hooks will not be triggered in this case.

[MurzNN]

The provided workarounds work, but still be great to provide this feature from the Payload Local API out of the box, so I filed a separate feature request with this: #15249