function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
ErikssonEriksson 

How to retrieve multi-select picklists using includes/excludes in static soql

Hi guys,

 

Here is my situation, I want to query multi-select picklists in static soql, I have done this in dynamic soql, but in static soql, I don't know how to do it?

 

code snippet:

 

String categories = 'Featured, Super Featured';List<Product2> products = [select Category__c from Product2 where Category__c includes (:categories)];

 

In this way, I want to select all the products include Featured or Super Featured category, but I cannot get the right records.

 

But in this way, I got the right records.

 

List<Product2> products = [select Category__c from Product2 where Category__c includes ('Featured','Super Featured')];

Is there anyone can help me this problem? Thanks in advance!!

 

Thanks

 

Message Edited by Eriksson on 03-02-2010 12:50 AM
Message Edited by Eriksson on 03-02-2010 05:03 PM
Message Edited by Eriksson on 03-02-2010 05:04 PM
NaishadhNaishadh

Use Set for passing multiple value.  

 

e.g. 

Set<String> categorySet = new Set<String>();categorySet.add('Featured');categorySet.add('Super Featured');List<Product2> products = [select Category__c from Product2 where Category__c includes :categorySet];

 

 

ErikssonEriksson

Thank you for your help, but it dosen't work. Have you tested your code in your side?

 

First of all, we have to add parentheses to the param in SOQL statement.

Second of all, I got error message "Invalid bind expression type of SET:String for column of type String", so we cannot use the Set or List here.

 

 

Thank you all the same.

jjvdevjjvdev

I have the same problem and according to the documentation "Using Apex Variables in SOQL and SOSL Queries", "Bind expressions can't be used with other clauses, such as INCLUDES."

 

Has anyone found a workaround to this?

 

-Thanks

SindhugsSindhugs

Were you able to resolve or find a workaround for this limitation ?

SindhugsSindhugs

Found Solution. Added semicolon and comma and that worked !!!

String categories = 'Featured'+';, '+ 'Super Featured'+';' and your SOQL includes(:categories)

 

docbilldocbill

Sindhug,

 

I would consider it a bug that the solution works, and would not want to rely on it in code, as salesforce could correct the bug anytime...

 

An alternative to using dynamic SOQL is to just to use a fixed size array.  e.g.

 

[ select Id from Bar__c where MyVar__c includes (:values[0],:values[1],:values[2],:values[3],:values[4]) ]

 

This solution works well when your picklist is small.   To handle the case where you have less than four values, then just append extra values to the list.  e.g.

 

while(value.size() < 4) values.add(value[0]);

List<Bar__c> bars = [

  select Id from Bar__c where MyVar__c includes (:values[0],:values[1],:values[2],:values[3],:values[4]) ];

 

The problem of course is what happens when you have too many.   Which is why this solution only works well for small lists.


gbargergbarger

I know no one has posted to this in a while, but I found it in a search, and after thinking about it a while, I came up with a better way to handle this.

 

You can use the database.query method to do a query of a string structured just like a query, so you could transform what you're wanting into a string, then do a query on it like this.

 

String Category1 = 'Featured';

String Category2 = 'Super Featured';

String QueryString = 'select Category__c from Product2 where Category includes (\''+

                                      Category1+'\',\''+Category2+'\')';

List<Product2> products = Database.Query(QueryString);

 

If you're wanting to do this dynamically, you could start with an empty string like this, then keep concatenating onto that string with a loop if you have a set<string>. Something like this...

String categories = '';

 

for(String s: setString)

{

    categories += '\''+s+'\',';

}

 

Something like that anyway.