How do you dynamically select fields in for loop query?

Question

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.

Answer 1

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.

Answer 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
}