function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
loneboatloneboat 

Documentation for SOQL "WHERE x IN :y" syntax?

Can someone point me to the documentation for SOQL's "WHERE x IN :y" syntax, where y is a collection type (list, set, map).  I've tried searching for it, but "where" and "in" are such generic terms that my searches are essentially as useful as just searching for "soql" alone.

 

Thanks!

Starz26Starz26

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_comparisonoperators.htm

 

and even more complete info

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_variables.htm
sfdcfoxsfdcfox

The colon (":") is a binding statement. You can bind almost any legal Apex Code expression here, not just a variable name. Normally, you'd use one of the following:

 

WHERE Id IN :sobjectList
WHERE <typed-field> IN :typedList
WHERE Id IN :idSet
WHERE <typed-field> IN :typedSet
WHERE Id IN :sobjectMap
WHERE <typed-field> IN :typedMap.keySet()
WHERE <typed-field> IN :typedMap.values()
WHERE Id IN :sobjectMap.values()

Where "typed-field" matches the type of typedList, typedSet, typedMap.keyset(), or typedMap.values().

 

For example, if you are querying on AnnualRevenue, you can use List<Decimal>, Set<Decimal>, Map<Decimal,anyType>.keySet(), or Map<anyType,Decimal>.values().

 

The ID types work with List<SObject>, Set<Id>, Map<Id,Sobject>.keyset(), or Map<id,sobject>.values().
@altius_rup@altius_rup

OK, but how does this work in dynamic SOQL ?  What's the syntax for that, please ?

Rup
loneboatloneboat

From what I understand, if you want to use this in dynamic SOQL, you have to build up the string explicitly:

 

List<Id> acctIds = new List<Id>{'id-one', 'id-two', 'id-three', ... };
String SOQL_IDs = '';
for (Id id : acctIds) {
    String id_in_quotes = '\''+id+'\'';
    if (SOQL_IDs!='') { SOQL_IDs+=','; }  //  add a comma if this isn't the first one
    SOQL_IDs += id_in_quotes;
}
String SOQL = 'SELECT Id, Name from Account WHERE Id IN ('+SOQL_IDs+')';
List<Account> accounts = database.query(SOQL);

 
sfdcfoxsfdcfox

My apologies for the late reply, but you can use dynamic bindings without generating your own CSV list:

 

set<id> ids = new set<id> { '0015000000f4QI2','0015000000f4QI3' };
system.debug(database.query('select id,name from account where id in :ids'));

The variable must be visible in the current scope for this to work.
craigmhcraigmh

Is variable binding in dynamic SOQL relatively new? I have used it plenty of times, but only recently. I remember having issues with it working, but I'm not sure if it wasn't supported before, or I just wasn't using it correctly.
sfdcfoxsfdcfox

I remember using it just shortly after dynamic apex came out, but it was unpredictable at first (but seems much better now).
k_bentsenk_bentsen
Apologies for the bump, but does the IN operator account for duplicate values in a set or list?

For example, if I have 3 parent records which each have 3 child records, and I have a list of the parent Ids off the child records so there would be 3 duplicates of 3 ids, would the SOQL for the parent object such that Id IN ParentIdList return 3 or 9 rows?
SF Admin 194SF Admin 194

Its working fine...


List<Id> acctIds = new List<Id>{'id-one', 'id-two', 'id-three', ... }; String SOQL_IDs = ''; for (Id id : acctIds) { String id_in_quotes = '\''+id+'\''; if (SOQL_IDs!='') { SOQL_IDs+=','; } // add a comma if this isn't the first one SOQL_IDs += id_in_quotes; } String SOQL = 'SELECT Id, Name from Account WHERE Id IN ('+SOQL_IDs+')'; List<Account> accounts = database.query(SOQL);
David Roberts 4David Roberts 4
You can also use wild card in a set or list of search criteria:-
Here's a filter on some UK postcodes.

List<String> pcList = new List<String>{'MK%','LN%','IP%','CV%','KT%'};
String queryString = 'SELECT Id, Name FROM Account WHERE BillingPostalCode LIKE :pcList LIMIT 20';
List<Account> lstAccounts = database.Query(queryString);
System.debug(lstAccounts);

see https://salesforce.stackexchange.com/questions/37113/how-to-use-like-and-in-together-in-soql
and https://salesforce.stackexchange.com/questions/65950/soql-bulk-fuzzy-searching
David Roberts 4David Roberts 4
You can exclude postcodes like this:

List<String> pcList = new List<String>{'M%'};
List<String> pcNoList = new List<String>{'MK%'};       
String queryString = 'SELECT Name, BillingPostalCode FROM Account WHERE (BillingPostalCode LIKE :pcList) AND (NOT BillingPostalCode LIKE :pcNoList)  LIMIT 20';
List<Account> lstAccounts = database.Query(queryString);
System.debug(lstAccounts);