Setting Dynamic Query Criteria Based On a Variable

Question

I need to define a clause for my dynamic SOQL query, based on the value in my userRegion variable, by adapting the query from this answer.

When I try to use

String queueOwnerRegionClause = ( userRegion == 'Asia' ? ' Asia__c = TRUE ' :
                                ( userRegion == 'EU' ? ' EU__c = TRUE ' :
                                ( userRegion == 'NA' ? ' NA__c = TRUE ' )));

I get these errors

Incompatible types in ternary operator: Boolean, String

(error location: 2nd line)

unexpected syntax: 'mismatched input ')' expecting COLON'

(3rd line)

adding a colon at the end of the 3rd line causes this error instead

unexpected token: ')'

(3rd line)

There's obviously a simple fix for these errors but I've tried searching for examples of other SOQL that create clauses by using this method & can't find any. I'm not sure which other search terms to use.

What's the correct syntax to use to set the criteria for this clause?

Answer 1

For your case, I would recommend using a Map instead of multiple ternary conditions. Your question is the proof that the code is unreadable.

So first, you could declare a Map like this:

Map<String, String> userRegionToField = new Map<String, String>{
    'Asia'=>'Asia__c',
    'EU'=>'EU__c',
    'NA'=>'NA__c'
};

Then, you could check if the map contains the current value. If not, you don't want to execute the query:

if(userRegionToField.containsKey(userRegion)){

Finally, format your query using the value returned by the map. The whole code would be:

Map<String, String> userRegionToField = new Map<String, String>{
    'Asia'=>'Asia__c',
    'EU'=>'EU__c',
    'NA'=>'NA__c'
};
if(userRegionToField.containsKey(userRegion)){
    String queueOwnerRegionClause = userRegionToField.get(userRegion) + ' = TRUE';
}