73

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); 

4 Answers 4

85

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.

5
  • 7
    I think ca_peterson's approach is the best way to do it. Also, take a look at the new String methods (salesforce.com/us/developer/docs/apexcode/Content/…) available in Winter 13. String.join is pretty powerful for use in turning lists into strings. Oct 19, 2012 at 22:04
  • 4
    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!
    – pchittum
    Oct 20, 2012 at 13:30
  • 4
    This is documented here: salesforce.com/us/developer/docs/apexcode/Content/… (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) Oct 20, 2012 at 13:38
  • 1
    No idea why we would ever think of removing it, since it's a well documented, highly useful feature of Apex!
    – metadaddy
    Oct 20, 2012 at 18:17
  • 2
    Also, an sobject list works in place of a set of Ids: List<Account> accts = [select Id from Account]; String q = 'select id from Contact where AccountId in :accts'; May 8, 2013 at 16:26
11

Well some time back I wrote a blog regarding Dynamic SOQL. Here is excerpt about what you can do what you cant.

You CANNOT use complex types in a Dynamic SOQL directly. This means that you cannot use any Sobject, Apex Class or Any other user defined data type inside the Dynamic Query. In short you cannot use a dot (".") operator to specify a field value in a Dynamic query.

//initialize a Account with a Name value for demo
Account acc = new Account(Name='MyAccount');
//query accounts by merging the variable name inside the query string
//This will not work
List<Account> accountList = Database.query('SELECT Id FROM Account WHERE Name =:acc.Name');

//But You can always make the above work by writing the code as.

       //initialize a Account with a Name value for demo
      Account acc = new Account(Name='MyAccount');
      String accountName = acc.Name
      //query accounts by merging the variable name inside the query string
      List<Account> accountList = Database.query('SELECT Id FROM Account WHERE                       
      Name =: accountName  ');

You can have a look on this

http://blogforce9.blogspot.in/2012/09/using-variables-in-dynamic-soql.html

3
  • 1
    Hi @Avidev9. I downvoted your answer. The reason for this is because your answer doesn't really add any new relevant information to a question that was answered months ago. I posted my explanation for why I feel this post isn't that great - meta.salesforce.stackexchange.com/questions/286/… - I would love to change to an upvote, but you should provide better information here. Specifically, your blog has additional info about complex types. Add that info here and this will be a true benefit to the question. Feb 13, 2013 at 18:48
  • 1
    @JesseAltman added info from the blog :)
    – Avidev9
    Feb 15, 2013 at 8:47
  • Upvoted! Great work @Avidev9 Feb 15, 2013 at 12:58
4

Ah, good thinking Daniel - and don't even need to cast back...this works:

Map<Id, Account> nmap = new Map<Id, Account> ([Select Id, Name from Account LIMIT 10]);
List<Id> nmaps = new List<Id>();
nmaps.addall(nmap.keyset());
String s = '\'';
s += String.join(nmaps,'\',\'');
s += '\'';

system.debug(s);   

I'm thinking this could be great for dynamic SOQL from a fieldmap...

1
  • 2
    Why not combine the last three lines? String s = '\'' + String.join(nmaps,'\',\'') + '\''; No need to build intermediate strings along the way.
    – tomlogic
    Feb 17, 2013 at 17:39
2

I don't think you can use the new join method on sets though, only Lists, which is a shame - being able to join a map's keyset would be helpful...

I totally agree that this is annoying to have to do:

idString = idString.substring(0,idString.length()-2); //<--this is the bit I hate doing

There is now the removeend() string function - same thing, but looks cleaner...

2
  • For merging sets there's always the .addAll method which adds the contents of another set to "this" one. Oct 20, 2012 at 0:37
  • 8
    You could always cast to a list (new List<Id>(theSet)).join Oct 20, 2012 at 11:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .