looping over dynamic soql of indeterminite dimensions

Question

Hoping someone has a clever strategy for the following scenario - I have a dynamic soql query that when assembled could be either:

soql = 'select id from a';
soql = 'select id, (select id from b) from a';
soql = 'select id, (select id from b), (select id from c) from a';

SObject results = Database.Query(soql);

In either case, there is also a where clause tacked onto the end 'where a.id =: aid' such that the outside table result will always be limited to a single row. In case it is helpful for this exercise, I also have a variable

Map<String, List<String>> subTableMap = new map<String, List<String>>;

defined whose keyset is the names of the subtables (b and c). The values of which contain the list of fieldnames retrieved from each subtable (just 'id' in the example above). (I used this map to build the soql).

How could I construct a dynamic loop(s) such that I could cycle through 'results' and work with each combination of 'a' (always one record) 'b', 'c' ... 'n'?

Thank you clever people

Answer 1

Here's a method I wrote up for you.

public Map<String, List<sObject>> getChildRecords(sObject record) {
    Map<String, List<sObject>> results = new Map<String, List<sObject>>();
    // This avoids the dreaded:
    // SObject row was retrieved via SOQL without querying the requested field
    Map<String, Object> populatedFields = record.getPopulatedFieldsAsMap();
    for(ChildRelationship relationship: record.getSObjectType().getDescribe()
        .getChildRelationships()) {
        String relationshipName = relationship.getRelationshipName();
        // Some relationships do not have a name, just like real life.
        if(relationshipName == null) {
            continue;
        }
        sObject[] records = (List<sObject>)populatedFields.get(relationshipName);
        // null list means SOQL didn't query those relationships, or no results
        if(records != null) {
            results.put(relationshipName, records);
        }
    }
    return results;
}

We get the populated field map and a describe call for the child relationships, then check to see if the relationship has a name and if any objects were returned, and if so, we put them into the result map.

---

Here's an implementation that creates the Cartesian product set you're looking for.

public static List<List<sObject>> makeCartesianArray(Map<String, List<sObject>> source) {
    // Output variable
    List<List<sObject>> results = new List<List<sObject>>();
    // Cache list of inputs
    List<List<sObject>> values = source.values();
    // List of iterators for n-level cartesian mapping
    List<Iterator<sObject>> valueIterators = new List<Iterator<sObject>>();
    // Populate the first set of iterators
    for(List<sObject> value: values) {
        valueIterators.add(value.iterator());
    }
    // Output for current list of iterators
    List<sObject> currentValues = new List<sObject>();
    // Populate with the first list of values
    for(Iterator<sObject> iterator: valueIterators) {
        currentValues.add(iterator.next());
    }
    // Cache size for efficiency
    Integer maxIterator = valueIterators.size() - 1;
    
    // We break this loop internally
    while(true) {
        // Add current values to output variable
        results.add(currentValues.clone());
        Integer currentIterator = maxIterator;
        // Find the first iterator that has another value to output
        while(currentIterator >= 0 && !valueIterators[currentIterator].hasNext()) {
            valueIterators[currentIterator] = values[currentIterator].iterator();
            currentIterator--;
        }
        // No more iterators have values, so stop here
        if(currentIterator == -1) {
            break;
        }
        // Advance all iterators starting from the last one that had a next value
        for(Integer index = currentIterator; index <= maxIterator; index++) {
            // And store those values in the current value list
            currentValues[index] = valueIterators[index].next();
        }
    }
    return results;
}

In order to make this work, call the first method, then add the root record as a null key.

Map<String, List<sObject>> results = Utils.getChildRecords(record);
results.put(null, new List<sObject>{record});
List<List<sObject>> cartesianProduct = Utils.makeCartesianArray(results);