How to query Id and Name for multiple SObject types without a for loop?

Question

I know having a query in a for loop is bad practice because you can easily hit the governor limits but am having trouble working out a better way.

What I'm trying to do is get the Id and Name of all SObject types that are referenced to by the Contact record.

What I have so far:

for(String referenceFieldName : referenceFieldNames){
    // TODO query in a for loop, although limited by number of lookup fields on contact
    String stringQuery = 'SELECT Id, Name FROM ' + referenceFieldName;
    List<SObject> soList = Database.query(stringQuery);

    Map<String, Id> fieldNameToId = new Map<String, Id>();
    for(SObject so : soList){
        fieldNameToId.put((String)so.get('Name'), so.Id);
    }
    fieldDescribeToNameIdMap.put(referenceFieldName, fieldNameToId);
}

Where referenceFieldNames is Account, Contact, etc.

Answer 1

Based on comments, it sounds like you're looking to find a bunch of records by name. There is a dynamic way to do this, too. Most objects are searchable, but for those that are not, you need to fall back to a query. Here's the most optimized version I could come up with without spending too much time on it.

// Example inputs
String[] terms = new String[] { 'Demo', 'Test' };
String termString = String.join(terms,' OR ');

// Storage for data types
Set<SObjectType> searchTypes = new Set<SObjectType>();
Set<SObjectType> queryTypes = new Set<SobjectType>();
String[] relatedTos = new String[0];
Map<SObjectType, Map<String, Id>> allResults = new map<SobjectType, Map<String, Id>>();

// Discover all fields
for(SObjectField fieldToken: SObjectType.Contact.fields.getMap().values()) {
    DescribeFieldResult describeResult = fieldToken.getDescribe();
    String relatedName = describeResult.getRelationshipName();
    if(describeResult.getSoapType() == SoapType.Id && relatedName != null && describeResult.isAccessible()) {
        searchTypes.addAll(describeResult.getReferenceTo());
    }
}
// if not searchable (e.g. RecordType) move to fallback plan
for(SObjectType theType: searchTypes) {
    if(!theType.getDescribe().isSearchable()) {
        queryTypes.add(theType);
    }
}
// and remove them from searching
searchTypes.removeAll(queryTypes);
// Build SOSL RETURNING value
for(SObjectType theType: searchTypes) {
    relatedTos.add(theType+'(Name)');
}
// Map by SObjectType, then Map Name to ID.
for(SObject[] objList: search.query('FIND :termString IN Name FIELDS RETURNING '+String.join(relatedTos,','))) {
    Map<String, Id> objResults = new Map<String, Id>();
    for(SObject record: objList) {
        objResults.put((String)record.get('Name'),record.Id);
    }
    allResults.put(objList.getSObjectType(), objResults);
}
// Do the same for queries
for(SObjectType theType: queryTypes) {
    Map<String, Id> objResults = new Map<String, Id>();
    for(SObject record: Database.query('SELECT Name FROM '+theType+' WHERE Name = :terms')){
        objResults.put((String)record.get('Name'), record.Id);
    }
    allResults.put(theType, objResults);
}

---

To get all the parents at once, expand your query:

String[] fields = new String[0];
for(SObjectField fieldToken: SObjectType.Contact.fields.getMap().values()) {
    DescribeFieldResult describeResult = fieldToken.getDescribe();
    String relatedName = describeResult.getRelationshipName();
    if(describeResult.getSoapType() == SoapType.Id && relatedName != null && describeResult.isAccessible()) {
        fields.add(relatedName+'.Name');
    }
}

Then, just query the Contact itself:

Contact[] contacts = Database.query(
    'SELECT '+
    String.join(fields,',')+
    ' FROM Contact WHERE Id = :recordId')