10

Something has been bugging me for a little while now, and I can't seem to find the answer anywhere...

When working with large queries, it is a good idea to use a for loop to iterate through the query results rather than just assigning the results to a list, like so:

for(Change_Order__c relatedChangeOrder : [SELECT Id,Name FROM Change_Order__c]){

    // Do stuff with relatedChangeOrder here

}

That's all fine and dandy until you want to choose specific fields to select dynamically. This is the case for me in one of my classes where I want to only select certain fields that map over properly to another object.

Obviously I can piece together a query string and use Database.query() to get the results with the fields that I want, but then I lose the for loop efficiency.

I have tried to set the Fields to a string:

String fields = 'Id,Name,Account_Number__c';

I have tried to set the Field to a List:

List<String> fields = new List<String>{ 'Id','Name','Account_Number__c' };

I've then tried inserting the fields dynamically into the query like this:

[SELECT :fields FROM Change_Order__c WHERE Active__c = TRUE]

This does not work.

Are there any other methods that people have come across? I have a query that will return a large data set and would really like to dynamically choose the fields and iterate through it with a for loop so that I don't run into SOQL limits.

8

You can use the Database.Query() static method:

for(MyObject__c obj : Database.Query('select Id, Name from MyObject__c'))
{
  // do stuff
}

Be wary of using parameters with this though, you should always protect against bad SOQL injection by users but you're not using user input in the query you'll be safe.

  • I was just trying the same thing and found that I didn't need the cast for it to work. – Daniel Ballinger Jun 6 '13 at 23:51
  • Same :) Just edited it. Pretty sure I used to need the cast! I guess the assignment isn't a list type, it's a record in the list and SObject can probably be cast to a specific object type without any extra bother. – Matt Lacey Jun 6 '13 at 23:52
  • My God... Why did I not try that? This is perfect! Thanks! – VictorKilo Jun 6 '13 at 23:55
  • Now I need to go review all of my code to make sure that I'm using this method. – VictorKilo Jun 6 '13 at 23:56
  • 1
    @VictorKilo I'd also recommend you take a look at the SoqlBuilder methods in the open source Apex-Lang library; they allow easy population of fields and parameters, and give you some nice free features like "select *" and SOQL injection protection. – jkraybill Jun 7 '13 at 5:07
2

Expanding on @LaceySnr's answer, it's also helpful to utilize String.join to build the query. With regards to SOQL injection, if you stick to using bind variables instead of parsing them into the query Salesforce will take care of escaping single quotes for you.

List<String> fields = new String[] { 'id', 'name', 'createdById' };
Id recordId = ApexPages.getCurrentPage().getParameters('searchId');
String query = 'select ' + String.join(fields, ', ') + ' from Object__c where id = :recordId';
for(Object__c record : Database.query(query)) {
   // Here be dragons
}
  • +1 Good point, although in this case I happen to be pulling the fields from Schema.SObjectType. The field names should be safe. What you're saying though is completely valid if the fields are being pulled from another data source. – VictorKilo Jun 7 '13 at 19:12
  • I was referring to the parameters. I.e. passing "1 or id != null" in the searchId param. Regarding the fields, just think String.join makes building the query cleaner. – Ralph Callaway Jun 7 '13 at 22:53

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.