2

Is it possible to query below data structure using salesforce OOTB API(Rest, Soap or Composite)?

enter image description here

Lets assume that from account all objects below have 1:M relationship with their parents There can be multiple orders for an account, multiple order lines for an order and multiple products per order line, multiple product lines per product. technically the output ids of one object becomes input to another.

The problem that i am trying to is we need to send different data structures like this to external system for document creation and i am trying to check if this can be done without needing to write custom apex wrapper classes.

Based on the document template we need to define certain data structures with fields and that needs to be queried in run time.

I checked Rest API documentation and was not able to figure out how to query hierarchical data using it.

2
  • The other way to think would be from down up. You can use composite API to do 5 queries in a single call. The first query would be on Product lines where Product.OrderLine.Order.AccountId = some Id. The second would be on Product custom object where OrderLine.Order.AccountId = some Id till you reach the Account. This is assuming you have multi level relationships. There would be some caveats on number of records that can be fetched.
    – manjit5190
    Commented Feb 19, 2021 at 19:33
  • Can you write an elaborate answer and give some examples with salesforce links
    – RedDevil
    Commented Feb 20, 2021 at 9:07

2 Answers 2

1

I know I'm way late to the party, but now in the Summer '24 release you can traverse five levels of parent child records.

List<Account> accts = 
[SELECT Name,
    (SELECT LastName,
        (SELECT AssetLevel,
            (SELECT Description,
                (SELECT LineItemNumber FROM WorkOrderLineItems)    
            FROM WorkOrders)    
        FROM Assets)    
    FROM Contacts)    
FROM Account];
-2

If you are looking to accomplish this with a single SOQL query, the answer is unfortunately no. With SOQL you can only query one level deep in a parent-child relationship. Let's look at the Account -> Opportunity -> Opportunity Contact Role structure which replicates to three levels deep the structure you posted. A SOQL query would look like this:

select id, name, (select id, name, (select id, contact.name, role from opportunitycontactroles) from opportunities) from Account

When you execute it, you'll get this response from Salesforce: MALFORMED_QUERY: SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.

This isn't an API-specific limitation. You'd get this same error if you embedded this SOQL in Apex code.

2
  • This does not answer the question in anyway
    – RedDevil
    Commented Feb 27, 2021 at 9:49
  • You asked if you could QUERY the given structure using OOTB API...without writing custom APEX. The answer is no, you can't, for the reason I explained. You will need to use multiple queries to accomplish it. You can accomplish this in Apex or some external routine that makes multiple API calls. If this is not your question, then please clarify what it is you are looking for.
    – Rick Brown
    Commented Feb 28, 2021 at 13:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .