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

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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