1

I am having a dilemma of what kind of query shall I make

Imagine that I'm inside a Details View of an object named Merchandise and of course, with the scope of LWC coding here, the recordID is focused on the Merchandise's.

Production

I want to create a Lightning Tree where I can display the Production Requests related to the Merchandise and put the related Purchase Requests of these Production Requests as sub-trees.

But it seems Salesforce / APEX isn't allowing sub-queries of up to 3rd level.

Legend: PR = Production Request PuR = Purchase Request

Relationships Merchandise has many Production Requests Production Request has many Purchase Requests

Mechandise > Production Requests > Purchase Requests (this is the database structure)

I tried querying this

List<Merchandise__c> merchanList = [SELECT ID (SELECT ID, (SELECT ID FROM Purchase_Requests__r) FROM Production_Requests__r) FROM Merchandise__c)]

And APEX is telling me that I can't query for more than 2 levels of relationship.

Can someone kindly give me a good query or work around for my desired SOQL please? Thank you very much in advance!

Suomynona
  • 639
  • 1
  • 5
  • 20

1 Answers1

0

You'll have to do part of the tree structure manually anyway. tree-grid requires usage of _children element for child nodes. Apex doesn't like variables that start with underscore. In that sense it doesn't matter that you can't write 1 elegant query for everything.

  • you won't be able to name any related list _children
  • you could create a custom wrapper class with field children and then JS-side loop through it anyway

So... it doesn't matter. Probably simplest is to do 2 queries:

SELECT Id, (SELECT Id FROM Production_Requests__r)
FROM Merchandise__c
WHERE Id = :...

SELECT Id, (SELECT ID FROM Purchase_Requests__r)
FROM Purchase_Request__c
WHERE Merchandise__c = :...

Have them as Map<Id,Merchandise__c>, Map<Id,Purchase_Request__c> (JS also has Maps) and loop through them to make final data structure? For each child element in 1st map check if there are grandchildren in 2nd map...

Alternatively you could make it "bottom up" but that would mean making some smart for loops with breaks when needed.

SELECT Id, Production_Request__c, Production_Request__r.Merchandise__c
FROM Purchase_Request__c
WHERE Production_Request__r.Merchandise__c = :...
ORDER BY Production_Request__r.Merchandise__c, Production_Request__c, Id

Yet another idea - can you fake it? OK, you may want a root element for you tree - but it'll always be just 1 element, the single merch record. If you can fake it in JS - or even better, have multiple top-level elements - then it's simpler. You only need simple query on Production + Purchase requests, screw Merchandise level?

eyescream
  • 18,088
  • 2
  • 34
  • 46