1

This was asked previously here however the answer isnt useful. Using an invocable class with a dynamic database query. If any of the fields in the query return a null value then the field is dropped from the response. That causes an error in the flow. Is there a way to ensure that the query returns ALL of the fields I requested? It's dynamic so one of the responses about initializing specific fields in the class is not useful.

For example for the dynamic query: SELECT Id, Field1__c, Field2__c from ACCOUNT The Debug log shows this is what is queried. If Field2__c = NULL then the class returns the following to the flow:

Id = '12345'
Field1__c = 'somevalue'

I need it to also return

Field2__c = null

This class is from UnofficialSF Execute SOQL

public static String replaceWithFormattedValues(String soqlQuery) {
        String endingClause = '';
        soqlQuery = soqlQuery.replaceAll('\r\n|\n|\r|\t',' ');
        List<String> clausesToRemove = new List<String>{' order by ', ' limit ', ' group by ', ' offset ' };
        for (String curClause : clausesToRemove) {
            if (soqlQuery.containsIgnoreCase(curClause)) {
                endingClause = curClause + soqlQuery.toLowerCase().substringAfter(curClause.toLowerCase());
                soqlQuery = soqlQuery.removeEndIgnoreCase(endingClause);
                break;
            }
        }
        if (soqlQuery != null && soqlQuery.containsIgnoreCase(' from ') && soqlQuery.containsIgnoreCase('select ') && soqlQuery.containsIgnoreCase(' where ')) {
            Pattern mPattern = pattern.compile('(?i)(?<=from )(.*)(?= where .+(\\(select .+\\)))');
            Matcher mMatcher = mPattern.matcher(soqlQuery);
            Boolean found = mMatcher.find();
            String sObjectType = '';
            if (found) {
                sObjectType = mMatcher.group(0);
                // Recurse through sub-queries
                Pattern subPattern = pattern.compile('(?i)\\(select .+\\)');
                Matcher subMatcher = subPattern.matcher(soqlQuery);
                while (subMatcher.find()) {
                    String subQuery = subMatcher.group(0).removeStart('(').removeEnd(')');
                    String formattedSubQuery = replaceWithFormattedValues(subQuery);
                    soqlQuery = soqlQuery.replace(subQuery, formattedSubQuery);
                }
            } else {
                mPattern = pattern.compile('(?i)(?<=from )(.*)(?= where)');            
                mMatcher = mPattern.matcher(soqlQuery);
                if (mMatcher.find()) {
                    sObjectType = mMatcher.group(0);
                    System.debug('Logs: match found: sobject: ' + sObjectType);
                } else {
                    throw new ExecuteSOQLException('Unable to parse query string: ' + soqlQuery);
                }
            }
            Map<String, String> fieldNameValueMap = new Map<String, String>();
            List<String> fieldNames = new List<String>();
            mPattern = pattern.compile('(?i)(?<=where )(.*)');
            mMatcher = mPattern.matcher(soqlQuery);
            while (mMatcher.find()) {
                String statement = mMatcher.group(0);
                                    System.debug('Logs: match found: fields: ' + statement);

                String whereClause = statement.replaceAll('(?i)\\(select .+\\)', '(SUBQUERY)');
                fieldNames.addAll(whereClause.split('\\(|\\)|>=|<=|!=|=|>|<| in\\(| not in\\(| like | in:| or| and'));
            }
            if (!fieldNames.isEmpty()) {
               for (Integer i = fieldNames.size() - 1; i >= 1; i -= 2) {
                    fieldNames[i - 1] = fieldNames[i - 1].replaceAll(' ', '');
                    fieldNameValueMap.put(fieldNames[i - 1], fieldNames.remove(i));
                }
            }
            Map<String, String> fieldTypes = getFieldTypes(sObjectType, fieldNames);
            soqlQuery = putFormattedValues(soqlQuery, fieldNameValueMap, fieldTypes);
        }
        return soqlQuery + endingClause
    public static String putFormattedValues(String query, Map<String, String> fieldNameValueMap, Map<String, String> fieldTypes) {
        Set<String> typesWithSpecialFormatting = new Set<String>{
                'DATETIME', 'DATE'
        };
        for (String fieldName : fieldTypes.keySet()) {
            if (typesWithSpecialFormatting.contains(fieldTypes.get(fieldName))) {
                String formattedValue = getFormattedValue(fieldNameValueMap.get(fieldName), fieldTypes.get(fieldName));
                query = query.replaceAll(fieldNameValueMap.get(fieldName).escapeJava(), formattedValue);
            }
        }
        return query;
2
  • Database.query() definitely returns the null values. It appears that the Unofficial SF code is at fault, but the problem is likely in putFormattedValues(), which isn't shown here.
    – David Reed
    Feb 10 at 20:42
  • Added that method. Didn't include initially for brevity and assumed issue was in the query generation part. @DavidReed
    – ddeve
    Feb 11 at 13:23
1

How about just filling in all the blanks afterwards? I don't know how this will work with that wall of code above, but you can do something like this:

String[] fields = new String[]{'Name','Phone'};

Account[] accts = Database.query('SELECT ' + String.join(fields,',') + ' FROM Account);
for (Account a : accts){
    Map<String, Object> acctMap = a.getPopulatedFieldsAsMap();
    for (String field : fields){
        if (!acctMap.containsKey(field)){
            a.put(field,null);
        }
    }                                  
}                                 
System.debug(accts);

It just uses the good old getPopulatedFieldsAsMap and then checks if there is a key... if not, you already have the key as you've just passed that to your query.

2
  • Gets the desired result! Created a new simpler class though without a lot of the extras I initially had from the unmanaged package. I do feel like there should be a more straightforward method though for these null fields that I'm missing.
    – ddeve
    Feb 15 at 19:48
  • Great! Glad it worked out in the end! And yeah, this is not pretty eh. Feb 15 at 20:00

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.