How do I find the field type of parent/grandparent fields etc?

Question

I have in my SQL relationship fields. The have written this query under a custom object. ex:

 SELECT name,Contact__r.Account.CreatedBy.Name FROM Four_Level_Check__c LIMIT 5

My need is to find the field type of the fields mentioned in the query. here the first field in the sql is "name", since it has no relationships attached to it, i can find the field type of "name" directly from the base object. But the second field "Contact__r.Account.CreatedBy.Name" contains a relationship schema from the custom object-->Account-->owner--> .So to find the field type of "Contact__r.Account.CreatedBy.Name" I have written a logic

String objectType;

if(objectt.contains('__r'))
{
    objectType=objectt.replace('__r','');
}
else if(objectt.equals('LastModifiedBy'))
{
    objectType='User';
}
else if(objectt.equals('CreatedBy'))
{
    objectType='User';
}
else if(objectt.equals('Owner'))
{
objectType='User';
}
else objectType=objectt;

String FormatVal;
String lookupField=Field;
Map<String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();
Schema.SObjectType leadSchema = schemaMap.get(objectType);
Map<String, Schema.SObjectField> M= leadSchema.getDescribe().fields.getMap();
Schema.SObjectField field1 = M.get(fieldVal);
System.debug('###field1'+field1);

For my scenario this code is working fine. But will this code work for all scenarios?

Answer 1

Your approach is not tenable. There are many scenarios where the lookup name may not match the object type. Your logic for custom lookup fields only even works for standard objects.

You should take a more programmatic approach and use describes. Instead of trying to guess the object wholesale, guess the field name and walk the path one step at a time. If you know the path is, for example: Parent__r.Grandparent__r.Owner.Name, then you can be reasonably certain the first field in the path is really Parent__c, the second field is Grandparent__c, the third field is OwnerId, and the fourth field is Name. If Parent__c looks up to an object named Object_A__c, your approach isn't going to work.

Below is a pared down version of my approach to a similar problem while building a Fluent Query tool. It should be pretty efficient on CPU time, saving the results of each object's describe for later use, and only pulling them once you know you need them. This approach works on all the field paths I have tested so far.

global class DescribeCache
{
    static final Map<SObjectType, Map<String, SObjectField>> fields =
        new Map<SObjectType, Map<String, SObjectField>>();
    global static SObjectField getField(SObjectType sObjectType, String fieldPath)
    {
        if (sObjectType == null || fieldPath == null) return null;
        if (!fields.containsKey(sObjectType))
            fields.put(sObjectType, sObjectType.getDescribe().fields.getMap());
        if (!fieldPath.contains('.')) return fields.get(sObjectType).get(fieldPath);

        Relationship relation = new Relationship(fieldPath.substringBefore('.'));
        SObjectField field = fields.get(sObjectType).get(relation.getFieldPath());
        if (field == null) return null;

        SObjectType parentType = field.getDescribe().getReferenceTo()[0];
        return getField(parentType, fieldPath.substringAfter('.'));
    }

    static final Map<SObjectType, Map<String, SObjectField>> fields =
        new Map<SObjectType, Map<String, SObjectField>>();

    class Relationship
    {
        final String name;
        public Relationship(String name) { this.name = name; }
        public String getFieldPath()
        {
            if (name == null) return null;
            return name.endsWith('__r') ?
                name.replace('__r', '__c') : name + 'Id';
        }
    }
}

Then to check, you would just do something like:

String fieldPath = 'Contact__r.Account.CreatedBy.Name';
SObjectType sObjectType = MyObject__c.sObjectType;

DescribeFieldResult describe = DescribeCache.getField(sObjectType, fieldPath);
SoapType fieldType = describe.getSoapType();

Answer 2

Without calling out to the Metadata API, there isn't a great way to determine this generically. One possibility is to run the query against one record and use that record to try to determine types.

public static List<Schema.sObjectField> getFieldTypes(string initialSObject, List<string> fieldList)
{
    List<Schema.sObjectField> fields = new List<Schema.sObjectField>();
    List<String> whereList = new List<String>();
    string query = 'select ' + String.join(fieldList, ',') + ' from ' + initialSObject + ' where ';
    for(string field : fieldList)
    {
        whereList.add(field + ' != null');
    }
    query += String.join(whereList, ' and ');
    query += ' limit 1';
    List<sObject> objectList = Database.query(query);
    if(objectList.isEmpty())
    {
        return null; //Could throw an exception
        //There needs to be at least one object in the database with 
        //all of the reference fields (including parent reference fields) populated.
    }
    sObject testObj = objectList[0];
    for(string field : fieldList)
    {
        fields.add(getSObjectField(testObj, field));
    }
    return fields;
}

public static Schema.sObjectField getSObjectField(sObject testObj, string field)
{
    if(field.indexOf('.') == -1)
    {
        return testObj.getSObjectType().getDescribe().fields.getMap().get(field);
    }
    else
    {
        return getSObjectField(testObj.getSObject(field.substringBefore('.')), field.substringAfter('.'));
    }
}

I've implemented this using two call parameters since I think it makes the logic easier to understand, but you could just pass in the whole query whose fields you want to check and have the method parse it if you wanted. Unfortunately, this does require that there is at least one object in the database that the current user has access to which has every reference field you want to check populated (you could also break it up into multiple calls with different fields, which would let it pull different objects, but would cost more soql queries).