Elegant way to convert Set<Id> into String for Dynamic SOQL IN comparison

  • When I have a Set of Id's and I want to do a dynamic SOQL query that I want to use it in for an IN comparison, I have to convert the Set to string in the format of:

    ('id1','id2',id3',...)
    

    The way I do it works, but is not particularly elegant, so I'm wondering if anyone has come up with a better way. Am I missing something? Is there something out there that can just turn a set or list into the properly formatted string?

    Here's my code (yes, my example isn't dynamic SOQL, but I've used this several times in dynamic SOQL):

    Map<Id,Account> accts = new Map<Id,Account>([select Id from Account]);
    
    String idString = '(\'';
    for (Id thisId : accts.keyset()) {
        idString += thisId + '\',\'';
    }
    idString = idString.substring(0,idString.length()-2); //<--this is the bit I hate doing
    idString += ')';
    
    String q = 'select id from Contact where AccountId in ' + idString;
    
    List<Contact> cts = Database.query(q); 
    
  • ca_peterson

    ca_peterson Correct answer

    9 years ago

    Believe it or not bind variables actually work in dynamic SOQL:

    Map<Id,Account> accts = new Map<Id,Account>([select Id from Account]);
    Set<Id> accountIds = accts.keySet();
    String q = 'select id from Contact where AccountId in :accountIds';
    List<Contact> cts = Database.query(q);
    

    Note that you can't embed method calls (e.g. AccountId in :accts.keySet() would fail) when binding in this way.

    This is a very, very good way of doing things as it offers a massive reduction in script statements, heap space, and even added security by automatically escaping input. I believe it also means that the bind data doesn't count against the 10k SOQL query character limit.

    I can confirm there are a number of ISV apps that are dependent on this so there's no way salesforce could remove it in a future version without a massive outcry. This method is actually salesforce endorsed!

    Also, see this question if you're having trouble with more complex uses, in short: you can only use an in-scope variable, not an actual expression with these binds.

    I think ca_peterson's approach is the best way to do it. Also, take a look at the new String methods (http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_methods_system_string.htm) available in Winter 13. String.join is pretty powerful for use in turning lists into strings.

    Wow! I had no idea about binding a variable in a string for use with Database.query(). I had also missed the String.join() method. Much cleaner if I need to do this for other purposes. Thanks a bunch for both tips!

    This is documented here: http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_variables.htm (though to be pedantic, the docs say that it works with lists of any type not sets of any type, though I have used it regularly with sets)

    No idea why we would ever think of removing it, since it's a well documented, highly useful feature of Apex!

    Also, an sobject list works in place of a set of Ids: List accts = [select Id from Account]; String q = 'select id from Contact where AccountId in :accts';

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM