3

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

Improve this question
3
  • 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. Commented Dec 21, 2012 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. Commented Dec 21, 2012 at 9:42
  • 3
    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. Commented Dec 21, 2012 at 16:33

5 Answers 5

Reset to default
3

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.

Improve this answer
2
  • I think rumdumdum was already using a bind variable q += ' WHERE Id IN :record_ids', but not getting the expected result. Commented Dec 21, 2012 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. Commented Dec 21, 2012 at 9:50
1

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 .

Improve this answer
1
  • 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. Commented Dec 21, 2012 at 9:54
1

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;
Improve this answer
3
  • 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. Commented Dec 21, 2012 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? Commented Dec 21, 2012 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 Commented Dec 21, 2012 at 15:47
0

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.

Improve this answer
1
  • 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 Commented Dec 21, 2012 at 9:56
0

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.

Improve this answer
2
  • Hi @rumdumdum, how did you solve your problem? I'm experiencing the same problem, but with parameters I get from HTTP response. I'm saving the values from external source as List(String) and then dynamically bind that List into SOQL query. But it only works if I pass only one parameter. When I pass more than one, the query doesn't respond. Thanks for your help! Commented Jan 3, 2018 at 0:23
  • Ha! I take that back :) I solved the problem with a .split(','); function. I took the parameters from my HTTP response, saved them as String and then I performed the split on this String and saved it as List. What a simple solution! Commented Jan 3, 2018 at 0:49

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.