0

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

1 Answer 1

2

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);
7
  • This is fantastic! I need to wrap my head some more around this. How would I take the next step to then spit out the Cartesian product set of (for illustration) concatenated id's of each combination of results?
    – Pete
    Jan 24, 2022 at 9:51
  • @Pete I think you're looking for something like: Id[] allChildIds = new Id[0]; for(sObject[] childObjects: results.values()) { for(sObject childRecord: childObjects) { allChildIds.add(childRecord.Id); } }. If you need a field other than Id, use record.get(fieldName). Cartesian product would create a set of every possible combination of Id values from each list, which would be a relatively involved recursive function.
    – sfdcfox
    Jan 24, 2022 at 10:33
  • thank you - I really am after list<list<sObject>> whose members are given b.size()=3 and c.size()=2, would be {a,b0,c0}, {a,b0,c1}, {a,b1,c0}, {a,b1,c1}, {a,b2,c0} and {a,b2,c1} (1 x 3 x 2 = 6 entries). Trying to modify what you've given me, but can't seem to get "a" isolated.
    – Pete
    Jan 24, 2022 at 16:55
  • @Pete Updated my answer with an implementation that should work for any n-th size mapping, assuming you don't hit CPU or heap limits. You can change this to just ID values if you want by changing List<List<sObject>> to List<List<Id>> and using valueIterators[index].next().id instead of just valueIterators[index].next().
    – sfdcfox
    Jan 24, 2022 at 17:45
  • @Pete Though, I didn't exactly specify the order in which the records will come out, e.g. it could end up being [c0,b0,a],[c0,b1,a],[c0,b2,a],[c1,b0,a],[c1,b1,a],[c1,b2,a] or something else instead. If you care about this, you could sort the keys and extract the lists independently.
    – sfdcfox
    Jan 24, 2022 at 17:53

You must log in to answer this question.

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