Cannot access the Parent field values from list<sObject> and Database.query

Question

I have a dynamic SOQL that is being generated and then I am using database.query(querystring) to store the list of sObject in the dynamic sobject list.

Here's the code :

string queryString ='SELECT id';             
        for(string s : fieldNames){
            queryString += ', '+s;
        }
        system.debug('Case Id :'+objid);
        queryString += ' FROM ' + objName + ' WHERE id=\''+objId+'\'';

        system.debug('Query : '+queryString);

        List<sObject> results = Database.query(queryString);

this is the query it prints in dev console :

SELECT id, Case.Contact.FirstName, Case.Account.Name, Case.Account.website_active__c FROM Case WHERE id='5000R000005XydT'

the query here is for Case object but it can be any object that the query runs for. The results variable assignment looks like this:

23:11:01:062 VARIABLE_ASSIGNMENT [41]|results|[{"Id":"5000R000005XydTQAS","ContactId":"0030R00000flV9jQAE","AccountId":"0010R00000YyS2lQAF","RecordTypeId":"012o0000000ttBUAAY","Contact":{"Id":"0030R00000flV9jQAE","FirstName":"Test","RecordTypeId":"012o0000000tpuTAAQ"},"Account":{"Id":"0010R00000YyS2lQAF","Website_Active__c":"www.mercermarketplac (2..

When I iterate over the results it throws and error variable not exist

for(sObject s : results){
    system.debug(s.contact.name);
 }

How can I access the Contact and Account values if results is not null with list<sObject>, or can I not do that and have to make sure to declare the list with that particular sobject being queried like in this case list<case>?

I have referred to this question and , this question. But the difference is, they have requested to see fields for a specific object query always. I have a dynamic query which can be for any object, how can I retrieve those values using list<sObject>?

Answer 1

In order to get a parent sObject, you need to use getSobject, and to get a field from that, use get:

for(sObject s : results){
    system.debug(s.getSObject('Contact').get('Name'));
    system.debug(s.getSObject('Contact').getSObject('Account').get('Name'));
}

To do this dynamically, you'll probably want to write a generic method:

public static Object getValue(SObject record, String path) {
  try {
    SObject temp = record;
    String[] parts = path.split('\\.');
    while(parts.size() > 1) {
      temp = temp.getSObject(parts.remove(0));
    }
    return temp.get(parts[0]);
  } catch(Exception e) {
    return null;
  }
}

This code should extract a value, or null if there isn't one:

for(sObject s : results){
    system.debug(getValue(s, 'Contact.Name'));
    system.debug(getValue(s, 'Contact.Account.Name'));
}

Answer 2

Apex is a strongly-typed language. When you have a variable the compiler knows to be a List<sObject>, it will only allow you to access fields on the individual sObject components of that list that are defined for the sObject class - which doesn't include Contact and Account-specific fields. That's why you receive a

Variable does not exist

error.

You have two options, one static and the other dynamic.

The first is to assign the result, or cast it, to a concrete type:

List<Contact> contacts = (List<Contact>)results;

That will fail at runtime with an exception if the list isn't really a List<Contact>.

The other is to use dynamic methods. With an sObject variable, you may call the method get() to obtain a field value, and getSobject() for a related object.

Note that get() returns an even more generic value - an Object. You'll again have to cast this to a concrete type to do much with it. getSobject() returns an sObject that acts the same as the ones you've already got, except that its actual underlying type may be different. For example, if you did someCase.getSobject('Contact'), your sObject returned would actually be a Contact - but the compiler doesn't know that at compile time.

Answer 3

In the case of multiple related sObjects you need to grab the last Sobject. In the below code, if you want HealthCloudGA__MedicalRecordNumber__pc which is a field of Person Account, then you need to traverse from Medical_note__c >> Case >> Account >> HealthCloudGA__MedicalRecordNumber__pc.

String tQue = 'select Id,Case__r.Account.HealthCloudGA__MedicalRecordNumber__pc,CreatedDate from Medical_Note__c where Id =: newID limit 1';

Medical_Note__c tnote = Database.query(tQue);              

String medRecordNumber = (String)tnote.Case__r.Account.get('HealthCloudGA__MedicalRecordNumber__pc');