0

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.

  • You probably shouldn't use the term "record type", as that implies a particular feature that you're clearly not using right now. – sfdcfox May 15 '18 at 3:50
  • @sfdcfox good point, although I'm not sure what the proper term is. I mean different types as in Contact, Account, Response, aCustomRecord__c – Aequitas May 15 '18 at 3:55
2

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')
  • Will this not only get the lookup objects for that particular contact record? I want all of the records for every SObject that the contact record has a lookup to. So Contact has lookup fields to Account and Custom__c. I want the Id and Name of every Account and the Id and Name of every Custom__c – Aequitas May 15 '18 at 4:07
  • In case this is a bit of an x-y problem, What I need this for, is for data importing, and it allows the user to specify the lookup fields with a name: 'my account' and in the backend it will replace 'my account' with my account's actual Id. – Aequitas May 15 '18 at 4:17
  • so it's not possible to reduce it down to a single query. That's unfortunate, I'll just add a limit then, it shouldn't be a problem since I doubt there will eventually be over 70 or so lookup fields on the contact record – Aequitas May 15 '18 at 4:32
  • 1
    @Aequitas Okay, that looks really crazy. But kind of awesome. Be aware that those maps are Case sensitive, so you might want to uppercase/lowercase all the name fields to be safe. – sfdcfox May 15 '18 at 4:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.