2

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, 2021 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, 2021 at 13:23

1 Answer 1

2

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.

3
  • 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, 2021 at 19:48
  • Great! Glad it worked out in the end! And yeah, this is not pretty eh. Feb 15, 2021 at 20:00
  • This can be a PITA if you have nested SOQLs
    – apz
    Mar 16, 2023 at 14:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .