Tell me more ×
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.
up vote 1 down vote favorite
1

Am building a dynamic SOQL where I am using a list of strings that represent record ids, here's an example similar to my class:

String q = '';
q += 'SELECT Id FROM' + object;
q += ' WHERE Id IN :record_ids';
List<sObject> sobjects = Database.query(q);

When record_ids has a single Id in the list it works fine, more then one and it does not bring back any records.

I have tried creating a string based IN in many forms, for example:

record_scope += '\'' + record_id + '\',';

But when I look at the logs instead of ... IN ('00000000000001','00000000000002') I get ... IN ('00000000000001,00000000000002') looks like ' are subtracted for some reason.

Any help will be appreciated.

Cheers

share|improve this question
 
Ensure your Database has records .I dont see any glitch in the code frankly .If there are records query will return.I have a link to previous post on this topic and it has even how to spilt. –  Mohith Kumar Dec 21 '12 at 5:34
 
There are couple hundred accounts in the org, I have tried using .split() and other methods with no success, when I manually construct the string like this: database.query(... Id IN :('0001','0002','0003') ...) it works fine. But the interesting thing is that q += ' WHERE Id IN :record_ids'; works perfectly fine when record_ids string list has only one entry, but when it has more no records are returned. –  Rich Dec 21 '12 at 9:42
1  
sounds like record_ids is a string which has 'a,b,c' in it, rather than a List<String>, post the code where you declare/populate the record_ids variable. –  superfell Dec 21 '12 at 16:33

5 Answers

up vote 3 down vote

You can directly use bind variables in dynamic soql, as incredible as it sounds.

Alternatively, the more traditional approach of quote delimiting the keyset and constructing your where clause manually. You can use the shiny Winter'13 String.Join method to iterate over a list and append with a delimiter.

share|improve this answer
 
I think rumdumdum was already using a bind variable q += ' WHERE Id IN :record_ids', but not getting the expected result. –  Peter Knolle Dec 21 '12 at 1:19
 
Thanks Peter, as I mentioned, I have tried all of the above approaches. But the interesting thing is that q += ' WHERE Id IN :record_ids'; works perfectly fine when record_ids string list has only one entry, but when it has more no records are returned in the query. –  Rich Dec 21 '12 at 9:50
up vote 1 down vote

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

Have a look at the above post.You need not even split and all

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

Just use the variable as demonstarted in above code .

share|improve this answer
 
Thanks Mohith, I have tried the above and in my case q += ' WHERE Id IN :record_ids'; works perfectly fine when record_ids string list has only one entry, but when it has more no records are returned in the query. –  Rich Dec 21 '12 at 9:54
up vote 1 down vote

The initial approach looks good, though the code you have posted won't work as is:
* object is a reserved word
* you have no space after FROM before the object name

These I guess are only symptoms of simplifying the code for posting it here.

The code below works in my org (though these particular Ids are fake):

String objectName = 'Account';
List<String> record_ids = new List<String> {'001000000000001','001000000000002','001000000000003'};
String q = '';
q += 'SELECT Id FROM ' + objectName;
q += ' WHERE Id IN :record_ids';
List<sObject> sobjects = Database.query(q);

I'd have to assume then that it is the building of record_ids that's at fault.

You could try using this debugging code before the Database.query to verify the state of record_ids:

system.assert(record_ids.size()>1);
for(String item : record_ids)
    Id myId = (ID) item;
share|improve this answer
 
Thanks Steve, the code I posted was more of an example, both of your points are correct. I have tried debugging and everything looks fine, as I mentioned in my previous comments, the list works fine when there is one entry, but does not work with multiple entries. –  Rich Dec 21 '12 at 12:11
 
So, to humour me, does my snipped work in your org if you run it anon? And if not, what's being logged? –  Stephen Willcock Dec 21 '12 at 12:53
1  
Looks like there was a problem with the record_ids list where a Visualforce parameter that held all the ids was passed in as a single entry, this explains why it worked with only one entry and not with multiple –  Rich Dec 21 '12 at 15:47
up vote 0 down vote

Create record_ids as a List of Strings or Ids, e.g.

List<Id> record_ids = new List<Id>{
  '00000000000001',
  '00000000000002'
};

If you have an existing string of Ids, you can convert it an array using String.split.

share|improve this answer
 
record_ids is defined a List of Strings, but still no luck, as I mentioned above it works fine when the list has one entry and does not work when the list has more then one entry –  Rich Dec 21 '12 at 9:56
up vote 0 down vote accepted

Looks like there was a problem with the record_ids list, Visualforce parameter that held all the ids was passed in as a single entry, this explains why it worked with only one entry and not with multiple.

Thanks for your help guys.

share|improve this answer

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.