Dynamic Field Query error from non sys admin users: unexpected token: 'from': ()

Question

I am trying to clone a record and dynamically query all fields. This works fine for system administrator but something is going wrong for non system administrator users trying to trigger the logic. Can anyone provide some insight on what's happening for non system administrator users when querying the fields dynamically? Its happening on line:

for(Pre_Clarification__c p : database.query(soql)){

Error:Apex trigger AccountTrigger caused an unexpected exception, contact your administrator: AccountTrigger: execution of AfterUpdate caused by: System.QueryException: unexpected token: 'from': ()

public static void CreateReviewSOAPreClara(Account[] accs){
Schema.DescribeSObjectResult d = Schema.SObjectType.Pre_Clarification__C; 
rtMapByName = d.getRecordTypeInfosByName();  
rtCST = rtMapByName.get('CST');    
Map<Id,Id> preclaraMap = new Map<Id,Id>();
Pre_Clarification__c[] preclaraDMLList = New Pre_Clarification__C[]{};
Set<String> preclaracloneId = new Set<String>();

for(Account acc : accs){
    if(acc.Regarding_Pre_Clarification__c != null){
        preclaracloneId.add(acc.Regarding_Pre_Clarification__c);      
    }
}

Map<String, Schema.SObjectField> fMap = Schema.getGlobalDescribe().get('Pre_Clarification__c'.toLowerCase()).getDescribe().Fields.getMap();
List<String> fields = new List<String>();
List<String> allfields = new List<String>();
if (fMap != null){
    for (Schema.SObjectField ft : fMap.values()){ // loop through all field tokens (ft)
        Schema.DescribeFieldResult fd = ft.getDescribe(); // describe each field (fd)
        if (fd.isCreateable()){ // field is creatable
            fields.add(fd.getName());
        }
        allfields.add(fd.getName());
    }
}
String soql = 'select ' + 'Related_Account__r.Regarding_Review_Brief__c, Related_Account__r.Regarding_Annual_Review__c,'
                        + 'Related_Account__r.FirstName, Related_Account__r.LastName, '
                + String.join(fields,',') + ' from Pre_Clarification__c where Id in : preclaracloneId';    
System.debug( '********SOQL STRING**************** fields: ' + fields + ' ********** allfields: ' + allfields);   
for(Pre_Clarification__c p : database.query(soql)){
    Pre_Clarification__c pclone = p.clone();
    pclone.Related_Review_Brief__c = null;
    pclone.Related_Opportunity__c = pclone.Related_Account__r.Regarding_Annual_Review__c;
    pclone.Name = 'Name will auto update'; 
    pclone.Related_ASAP_FP__c = null;
    pclone.Related_ASAP_Super__c = null; 
    pclone.RecordTypeId = rtCST.getRecordTypeId();
    pclone.Review_SOA_Pre_Clarification__c = true;
    preclaraDMLList.add(pclone);
}

insert preclaraDMLList;

}

Here is the debug of string soql:

08:26:53.359 (389041028)|USER_DEBUG|[300]|DEBUG|********SOQL STRING**************** stringvalue: select Related_Account__r.Regarding_Review_Brief__c, Related_Account__r.Regarding_Annual_Review__c,Related_Account__r.FirstName, Related_Account__r.LastName, from Pre_Clarification__c where Id in : preclaracloneId

I just debugged fields string and created another string allfields and updated above. It looks like the isCreatable() line is failting to meet criteria and causing fields string to come back null.

08:58:18.349 (374728976)|USER_DEBUG|[302]|DEBUG|********SOQL STRING**************** fields: () ********** allfields: (Id, OwnerId, IsDeleted, Name, RecordTypeId, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, ...)

Answer 1

When building dynamic queries like this, you should make sure your list of fields is not empty. I would add this following line before your declaration of soql:

if (fields.isEmpty()) fields.add('Id');

In this way, you'll never have an empty list after your comma, which would lead to an invalid query:

SELECT ... Related_Account__r.LastName, FROM Pre_Clarification__c WHERE ...

Answer 2

I think the issue is in the below code

String soql = 'select ' + 'Related_Account__r.Regarding_Review_Brief__c, Related_Account__r.Regarding_Annual_Review__c,'
                        + 'Related_Account__r.FirstName, Related_Account__r.LastName, '
                + String.join(fields,',') + ' from Pre_Clarification__c where Id in : preclaracloneId';

Have you debug the soql query string what exactly is coming while executing. If no please debug and see.

I think the below soql query will fail if there is no FLS or fields list is null.

Answer 3

I misunderstood isCreatable() method for describing fields and didn't know it was in context to the current user. I just removed it and the code worked fine. I thought early on while testing this by not having isCreatable was causing an error but it isn't anymore.