[SalesForce] Elegant way to convert Set 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);

Best Answer

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.

Related Topic