Query based on multi-select picklist

Question

I have a method that searches for records based on a picklist value on the user record. We set the picklsit value and the query does a simple search based on the name.

Example:

user = [
        SELECT Department, BudgetToolRole__c, User_BU__c, User_Region__c, User_Sector__c 
        FROM User WHERE Id = :UserInfo.getUserId()
    ];

Practice__c p = [SELECT Id, Name FROM Practice__c WHERE Name = :user.User_BU__c]; 

Now, I have the requirement that the picklist field should really be a multi-select picklist and that there should be multiple practice records returned. So based on the above I want to get the values of the multi-select and then run the query on them.

Example:

String strPA = [SELECT Budget_Cross_Practice__c FROM User WHERE Id = :UserInfo.getUserId() LIMIT 1][0].Budget_Cross_Practice__c;

List<Practice__c> pas = [SELECT Id, Name FROM Practice__c WHERE Name = :strPA];

This complies in anonymous apex but no results are returned. The string returns a value in debug statement value1;values2;value3

I tried changing the = to IN but I get no luck.

How can I perform a query based on values in a multi-select?

Answer 1

You can use the LIKE operator:

String strPA = [SELECT Budget_Cross_Practice__c FROM User WHERE Id = :UserInfo.getUserId() LIMIT 1][0].Budget_Cross_Practice__c;
String likeStrPA = '%' + strPA + '%';
List<Practice__c> pas = [SELECT Id, Name FROM Practice__c WHERE Name LIKE :likeStrPA];

However, this will run into issues if any option is contained in another. A more robust solution is to split your multi-select field into a List<String>:

String strPA = [SELECT Budget_Cross_Practice__c FROM User WHERE Id = :UserInfo.getUserId() LIMIT 1][0].Budget_Cross_Practice__c;
List<String> strPASplit = strPA.split(';');
List<Practice__c> pas = [SELECT Id, Name FROM Practice__c WHERE Name IN :strPASplit];

Answer 2

I think you need to use IN clause to find the matching from multi-select picklist. IN clause always take Iterable element. So, either store strPA as List or Set and use that in SOQL.

String strPA = [SELECT Budget_Cross_Practice__c FROM User WHERE Id = :UserInfo.getUserId() LIMIT 1][0].Budget_Cross_Practice__c;

List<String> strList = strPA.split(';');

List<Practice__c> pas = [SELECT Id, Name FROM Practice__c WHERE Name IN:strList];