multiPicklist query

Question

I have a few global picklists which I using for multi picklists.

I'm trying to retrieve a data via SOQL but something goes wrong - not all the rows are back or sometimes none of them. It depends if I have one or more values in the multi picklist.

I tried this approach:

asd - is a set of all the Values in the multi picklists inside my custom object.

for example :

List<String> asd = new List<String>();
asd.add('agg03');
asd.add('agg05');
asd.add('agg06');
asd.add('agg07');
asd.add('agg08');
asd.add('agg09');
asd.add('agg10');

assuming that I have 3 different records that have the next values in the multi picklist :

1 - 'agg01' and 'agg03'

2- 'agg06'

3 - 'efg01' and 'agg08'

'SELECT Id,Active__c,PickList_1__c,PickList_2__c,PickList_3__c FROM Custom_Object__c WHERE Active__c = true AND (PickList_1__c IN: asd OR PickList_2__c IN: asd OR PickList_3__c IN: asd)';

This will return only row 2 - because it has only one value. but will not return row 1 and 3 - although it includes in the Set.

I read about INCLUDE - But Its not working when I did the following :

 String asdJoin= String.join(this.asd, ',');

'SELECT Id,Active__c,PickList_1__c,PickList_2__c,PickList_3__c FROM Custom_Object__c WHERE Active__c = true AND (PickList_1__c INCLUDES (:asdJoin) OR PickList_2__c INCLUDES (:asdJoin) OR PickList_3__c INCLUDES (:asdJoin))';

I also tried to put quotes:

String StringVar= '\'' + String.join(asd, '\',\'') + '\'';
List<Object__c> inti = [SELECT Id,Agriculture__c FROM Interest__c WHERE Active__c = true AND  Agriculture__c INCLUDES (:StringVar)];

Which does not work

But this is Work:

List<Object__c> inti = [SELECT Id,Agriculture__c FROM Interest__c WHERE Active__c = true AND  Agriculture__c INCLUDES ('ag03','ag05','ag06','ag07','ag08','ag09','ag10','ex01','ex02','hu01')];

WHY?! :(

Any Idea how does it can work?

Answer 1

That happens because the soql does not understand (:StringVar) as reference to list, it assumes it as String. Possible solution is to make a soql string manually and get query locator:

List<String> asd = new List<String>();
asd.add('agg03');
asd.add('agg05');
asd.add('agg06');
asd.add('agg07');
asd.add('agg08');
asd.add('agg09');
asd.add('agg10');

String condition =  '(\'' + String.join(asd, '\',\'') + '\')';

String query = 'SELECT Id, PickList_1__c, PickList_2__c, PickList_3__c FROM Custom_Object__c WHERE PickList_1__c INCLUDES ' + condition + ' OR PickList_2__c INCLUDES ' + condition + ' OR PickList_3__c INCLUDES ' + condition;

system.debug(Database.query(query));

This is example, try to rewrite it for your case, feel free to ask if you encounter any problem.