LWC for:each needs 3 levels, but SOQL query aggregate relationship limited to 2. What's your best practice?

Question

so I have a structure like:

<template for:each={containers.data} for:item='container'>
  <....> //container info
  <template for:each={container.Pallets__r} for:item="pallet">
     <...> //pallet data
     <template for:each={pallet.Products__r} for:item="product">
        <...> //products
     </template>

   </template>

</template>                                

Essentially, these are slds-box'es nested in each other with other object info .

However, SOQL cannot aggregate all the way to products, saying: SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.

I was wondering if you have overcome something like this? Thank you

Answer 1

If you wanted to do all the queries from the top level, you could use three queries, return the data to the client, and do further parsing there.

public class Response {
  @AuraEnabled public Container__c[] containers;
  @AuraEnabled public Pallet__c[] pallets;
  @AuraEnabled public Product__c[] products;
}
@AuraEnabled(cacheable=true) public static Response getData() {
  Response res = new Response();
  res.containers = [SELECT ... FROM Container__c];
  res.pallets = [SELECT ... FROM Pallet__c WHERE Container__c = :res.containers];
  res.products = [SELECT ... FROM Product__c WHERE Pallet__c = :res.pallets;
  return res;
}

And on the client, you'd do your rendering somehow, e.g.:

  get containers() {
    if (this.response && this.response.data) {
      return [...this.response.data.containers]
      .map((container) => ({
        ...container,
        Pallets__r: this.response.data.pallets
          .filter((pallet) => pallet.Container__c === container.Id)
          .map((pallet) => ({
            ...pallet,
            Products__r: this.response.data.products.filter(
              (product) => product.Pallet__c === pallet.Id
            ),
          })),
      }));
    }
    return [];
  }

This makes liberal use of the Array.prototype.map, Array.prototype.filter, arrow functions, and spread features of JavaScript.

Answer 2

We effectively did the equivalent by having a top level query for "Containers", in a parent LWC, using these results to iterate and create separate child LWCs for each "Container". That child LWC was responsible for executing its own aggregate query for its "Pallets" and their "Products", and rendering these details through its template (indeed, we actually used further LWCs for these objects that just received their data through API properties).