0

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.

8
  • out of curiosity, why are you not using SOSL instead of SOQL ?
    – glls
    May 5 at 13:42
  • 1
    SOSL does not work with picklist fields.
    – Pauline
    May 5 at 13:44
  • have you considered searching using sosl, and then filtering out based on the picklist value? furthermore, you could combine server/client side filtering.
    – glls
    May 5 at 13:47
  • also, if you dont mind specifying which fields are the picklists in your post, that would be helpful
    – glls
    May 5 at 13:48
0

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;
}
2
  • Thanks for the input.
    – Pauline
    May 6 at 8:20
  • Old.. I tried your approach and it works perfectly. Thanks a lot!
    – Pauline
    May 21 at 12:36
0

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.

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.