How can i make a multi level query using SOQL in Salesforce?

Question

I am running the nested query

  SELECT Id, Name, Account.Name,
(SELECT Id, OrderItemNumber, Product2.Name
    FROM OrderItems)
  FROM Order

This is only 1 level deep. Is there an example which can show me how to make a 3 to 5 level deep select in SOQL ?

Answer 1

SOQL is limited in that subqueries like you show are only allowed at a single level.

However, you can go higher (5 levels) if you do bottom up.

E.g. we rewrite your query like so:

SELECT Id, OrderItemNumber, Product2.Name,
Order.Id, Order.Name, Order.Account.Name, 
Order.Account.Parent.Name,
Order.Account.Parent.Parent.Name, 
Order.Account.Parent.Parent.Parent.Name, 
Order.Pricebook2.Name
FROM OrderItem

To get some account hierarchy. It's clunky as SOQL is, but you get the idea