How to get a record list with nested record list through advanced SQL or any way

How to get a record list with nested record list through advanced SQL or any way

  
For my project, I need to structure the data as required by the client. Here is what I need:
For example, I have Order entity and OrderItem entity, I need a Record List of Order, each order has a Record List typed field which is populated with OrderItems, I have defined OrderItem structure, and Order structure with  a Record List (OrderItem)field. But I don't know to generate the result through in one query. I was trying to populate the fields in two query, one for getting order related fields and one for getting the order items, but outsystems does not allow me to let any field out, I have tried anything like NULL, blank string, but nothing make outsystems happy. 
Can anyone please share some ideas?
You aren't going to be able to do this with one query. Instead, query the parents. Loop over those results and for each parent, push the values into a Structure that has the parent AND and attribute of type Record List for the children... then query for the children, put them into the Structure's Attribute for children with ListAppend, then append this structure to a Record List with that structure as its type.

J.Ja