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

  • 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 Shrivastava 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. – rumdumdum Dec 21 '12 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. – superfell Dec 21 '12 at 16:33
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.

  • 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. – rumdumdum Dec 21 '12 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 .

  • 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. – rumdumdum Dec 21 '12 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;
  • 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. – rumdumdum 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 – rumdumdum Dec 21 '12 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.

  • 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 – rumdumdum Dec 21 '12 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.

  • 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! – Štěpán Víteček Jan 3 '18 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! – Štěpán Víteček Jan 3 '18 at 0:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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