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;
Improve this question
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. Commented 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 Commented Feb 11, 2021 at 13:23

1 Answer 1

Reset to default
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.

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

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.