how can I avoid a for loop when searching against a list in a picklist field

Question

I need to search for a list of strings and match against a picklist field. So far, the query below only works when there is only one string in the query string. Appending more strings does not work since I think it is matching against the entire string.

for example
Arts and Culture, Animal Welfare

does not return any results. But if you pass either of them, there is a result.

issueAreas = [SELECT Name, Description__c FROM Engagements__c WHERE ( Other_Issue_Area__c INCLUDES ( :searchString ) OR Primary_Issue_Area__c IN (:searchString)) ];

Entire code

both Other_Issue_Area__c and Primary_Issue_Area__c are picklist fields

public with sharing class GetSearchResults {
    @AuraEnabled(cacheable=true)
    public static List<Engagements__c> getSearchResults(Object searchTexts) {

        String message = 'Success';
        String searchString;
        String convStr;
        String searchQuery;
        Set<String> searchSet = new Set<String>();
        List<String> searchArray = new List<String>();
        List<String> searchList = new List<String>();
        List<String> finalSearch = new List<String>();
        List<Engagements__c> issueAreas = new List<Engagements__c>();
        //List<List<SObject>> results = new List<List<SObject>>();
        List<String> res = new List<String>();
        
        try{
            if(searchTexts != null){
                System.debug('Received ' + searchTexts);

                convStr = String.valueOf(searchTexts);
            
                String processedString = convStr.remove('(');            
                convStr = processedString.remove(')');
                System.debug('Processed String ' + convStr);
                searchList = convStr.split(',');

                
                searchSet.addAll(searchList);
                searchSet.addAll(searchSet);     //Removes the remaining duplicate value;
                searchArray.addAll(searchSet);

                for(String s : searchArray){
                   // s = '"'+ s + '"';
                    s = '\''+ s + '\'';
                    //s = '*' + s + '*';
                    finalSearch.add(s);
                    searchSet.add(s);
                }

                System.debug('Modified list ' + searchArray);

            }

            searchString = String.join(searchArray, ',');
            //searchString = '(' + searchString + ')';

            System.debug('Search conv string ' + searchSet);

            //issueAreas = [SELECT Name, Description__c FROM Engagements__c WHERE ( Other_Issue_Area__c INCLUDES ( :searchString ) OR Primary_Issue_Area__c IN (:searchString)) ];

            issueAreas = [SELECT Name, Description__c FROM Engagements__c WHERE ( Other_Issue_Area__c INCLUDES ( :finalSearch ) OR Primary_Issue_Area__c IN (:finalSearch)) ];
           

 // for(String s : searchSet){
            //     issueAreas = [SELECT Id FROM Engagements__c WHERE  Other_Issue_Area__c INCLUDES (:s) ];
            //     for(Engagements__c e: issueAreas){
            //         String issueId = e.Id;
            //         res.add(issueId);
            //     }

               
            // } 
            
            System.debug('Number of issues ' + issueAreas.size());
        }
        catch(Exception e){
            System.debug('Error occured' + e.getMessage());
        }
        return issueAreas;
    }
}

I tried the line below passing a list to INCLUDES which is what I found works for picklist fields but INCLUDES does not take a list.

issueAreas = [SELECT Name, Description__c FROM Engagements__c WHERE ( Other_Issue_Area__c INCLUDES ( :finalSearch ) OR Primary_Issue_Area__c IN (:finalSearch)) ];

So I thought I would have to use a loop(commented) and then check limits but I am trying to avoid that approach. I can't find other alternatives.

Any help will be appreciated.

Thanks.

Answer 1

I had a very similar issue somewhat recently. I was trying to dynamically reference a list of strings for a picklist field in a SOQL 'INCLUDES'. If it's not a multi-picklist field then having a reference of something like "WHERE Field__c IN :someStringList" will work fine as Salesforce handles the formatting of the variable injection natively. However when you try to use INCLUDES on a it doesn't handle it well. If you switch to generating a SOQL String and Database.query(string) it doesn't do any of the String List formatting at all.

I got around this by using the Database.query, constructing my own SOQL String and using a helper method to format the List of Strings.

Example:

    List<String> searchString = new List<String>{'Arts and Culture', 'Animal Welfare'};

String query = 'SELECT Id FROM Engagements__c WHERE Other_Issue_Area__c INCLUDES ' + generateSoqlFriendlyIncludes(searchString); 

List<Engagements__c> engagementList= Database.query(query);

then a helper method below to format that string properly

public static String generateSoqlFriendlyIncludes(List<String> strings) {
    String soqlString;
    if (strings.size() > 0) {
        soqlString = ' ( ';
        Integer i = 0;
        
        for (String a : strings) {
            soqlString += '\'' + a + '\'';
            if (i + 1 < strings.size()) {
                soqlString += ', ';
            }
            i++;
        }
        soqlString += ') ';
    }
    return soqlString;
}

Answer 2

Based on the code you have shared and the SOSL limitation. I would recommend the following (if you are expecting a max. of a couple of hundreds of rows), perform a query with no condition(s):

SELECT Name, Description__c FROM Engagements__c

and you can easily filter results from the client, after fetching results from the server. this would help clean up your code considerably from what you currently have. Additionally, this would avoid having to do multiple trips to the server (based on user input).

• you fetch the data once.
• you perform the filtering on the client, based on user input.