Need help in Dynamic SOQL

Question

In my code i need to get the object name from the record id and based on the object name, i need to get the field name to add in my query. For now, I have hardcoded the field names, is there a better way to do this. The field in the where clause is a lookup field in the lease__c object.

public List<SObject> getObjectData(String recordId) {
        String sObjName = Id.valueOf(recordId).getSObjectType().getDescribe().getName();
       String fieldName = '';
        if (sObjName == 'Property__c') {
            fieldName = 'Property__c';
        }
        else if (sObjName == 'Account') {
                 fieldName = 'Account__c';
        }
        string query = 'SELECT Id, Greatest_MRI_Vacate_Date__c FROM Lease__c WHERE' + fieldName + '=: recordId';
}

score 0 · Answer 1 · answered Jun 18 '21 at 20:54

Ooo, interesting one!

First, would you consider reversing the query? If you can "promise" the related list of leases has always same API name, you could do

SELECT Id, 
    (SELECT Greatest_MRI_Vacate_Date__c FROM Leases__r)
FROM Account
WHERE Id = :recordId

SELECT Id, 
    (SELECT Greatest_MRI_Vacate_Date__c FROM Leases__r)
FROM Property__c
WHERE Id = :recordId

Looks bit simpler?

You can then use result[0].getSobject('Leases__r')[0].get('Greatest_MRI_Vacate_Date__c') to travel the related list.

If not - this should be good start for what you need. It's bit dangerous (well, dangerous... not as generic). Because what if you ever have 2 lookups to Account? Or what if you encounter "mutant fields" like OwnerId can be User or Queue. On Task.WhatId can point to dozens of tables. Etc.

for(Schema.SObjectField sf : Contact.sObjectType.getDescribe().fields.getMap().values()){
    Schema.DescribeFieldResult dfr = sf.getDescribe();
    if(dfr.getType() == DisplayType.REFERENCE){
        System.debug(sf + ' is a lookup to ' + dfr.getReferenceTo());
    }
}
MasterRecordId is a lookup to (Contact)
AccountId is a lookup to (Account)
RecordTypeId is a lookup to (RecordType)
ReportsToId is a lookup to (Contact)
OwnerId is a lookup to (User)
CreatedById is a lookup to (User)
LastModifiedById is a lookup to (User)
IndividualId is a lookup to (Individual)

getReferenceTo() returns list of tokens (like Id.getSobjectType() returns a token, you could compare them as is without casting to string or calling extra describes). Most of the time there will be just 1 on the list. The OwnerId and Task.WhoId, WhatId are bit of edge cases.

With this loop you can also say screw it all, I'm gonna try it all and see what sticks. Performance might be so-so. In theory they're lookups so indexes... You'd need to experiment.

SELECT Id FROM Contact WHERE MasterRecordId = :i OR AccountId = :i OR RecordTypeId = :i OR ...

score 0 · Answer 2 · edited Jun 20 '21 at 19:26

May I ask why you want to do this?
I ask because there is likely a cleaner way to achieve what you're trying to do.
Polymorphism aims to reduce code with if-statements like this.

Have you considered something like this?

Build a simple abstract class

public abstract Lease {
    public Id recordId { get; private set; }

    public Lease(Id recordId) {
        this.recordId = recordId;
    }

    public Date getGreatestMRIVacateDate() {
        return Database.Query( getHighestQuery() )
    }

    private String getHighestQuery() {
        return 'SELECT 
                    Id, 
                    Greatest_MRI_Vacate_Date__c
                FROM Lease__c
                WHERE ' + getObjectIdField() + ' =:recordId';
    }

    protected abstract String getObjectIdField();
}

extend the class for your use cases

public Property extends Lease {
    public Property(Id recordId) {
        super(recordId);
    }

    private override String getObjectIdField() {
        return 'Property__c';
    }
}

public Account extends Lease {
    public Account(Id recordId) {
        super(recordId);
    }
    private override String getObjectIdField() {
        return 'Account__c';
    }
}

Create a Factory class

public class LeaseFactory {
    public class TypeException extends Exception();

    public static Lease getLease(Id recordId) {
        String objType = getSObjectType(recordId);
        switch on objType {
            when 'Property__c' {
                return new Property(recordId);
            }
            when 'Account__c' {
                return new Account(recordId);
            }
            when else {
                throw new TypeException('Unknown Type: ' + recordId);
            }
        }
    }

    private static String getSObjectType(Id recordId) {
        return Id.valueOf(recordId).getSObjectType().getDescribe().getName();
    }
}

Now you can use it in your code dynamically and cleanly

    List<Id> recordIds;
    for (Id recordId : recordIds) {
        Lease l = LeaseFactory.getLease(recordId);
        System.debug( l.getGreatestMRIVacateDate() );
    }

This may seem way over engineered (and there's probably a way to make it cleaner), but the advantage of this approach is that if you ever decide add more functionality such as getLowestMRIVacateDate, you only really need to edit the code in one place: the Lease class. The functionality will then filter down to the rest of your classes.

This reduces the amount of times you have to do that if-statement where it figures out which type it is to one time instead of potentially having to do it in every method. It makes the code cleaner, shorter and easier to follow in the long run.

Please note, I'm pretty sure there's a cleaner way still using an interface but I'm tired and don't want to figure it out right now. I recommend watching this: https://www.youtube.com/watch?v=NU_1StN5Tkk

On another note, if you just have this one method, have you considered SOSL instead of SOQL? I am not well versed in it but I believe you can do something along the lines of:

FIND :recordId IN (Account__c, Property__c) RETURNING Lease(Id, Greatest_MRI_Vacate_Date__c)

See: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_sosl_find.htm

Need help in Dynamic SOQL

2 Answers2