Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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); 
share|improve this question

4 Answers 4

up vote 49 down vote accepted

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.

share|improve this answer
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. –  Daniel Hoechst Oct 19 '12 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! –  Peter Oct 20 '12 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) –  Stephen Willcock Oct 20 '12 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 '12 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'; –  Stephen Willcock May 8 '13 at 16:26

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

share|improve this answer
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. –  Jesse Altman Feb 13 '13 at 18:48
1  
@JesseAltman added info from the blog :) –  Avidev9 Feb 15 '13 at 8:47
    
Upvoted! Great work @Avidev9 –  Jesse Altman Feb 15 '13 at 12:58

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...

share|improve this answer
    
For merging sets there's always the .addAll method which adds the contents of another set to "this" one. –  ca_peterson Oct 20 '12 at 0:37
7  
You could always cast to a list (new List<Id>(theSet)).join –  Daniel Blackhall Oct 20 '12 at 11:18

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...

share|improve this answer
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 '13 at 17:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.