+ Start a Discussion
Jim BoudreauxJim Boudreaux 

SELECT * Apex Workaround

Hey everyone.

When I first started developing on the Force.com platform I was pretty miffed to discover there was no SOQL equivalent to the SQL SELECT * query to return data from all your fields.

I was told the thing to do was to use the SQOL Explorer app to generate a SELECT statement with all your fields, then Copy & Paste it into your apex. That works well.

 

Until your boss asks you to add a field and display it on a VF page, and now you've got to go to the sandbox, add the field tot he object, add it to your SOQL queries in Apex, go back to the Production Org and add the field there, and finally deploy to production.

I don't know about you, but after this tedious task is complete, my boss will inevitably think of yet another field he wants added and I get to reenact the whole process again.

 

I don't know if I am just late to the table on this, but I have finally stumbled upon a way out of this scenario. The key is Dynamic Apex mixed with Dynamic SOQL.

 

I created a Class called SelectAll. It has two properties, AllFields and SOQL (both Strings). It can be instantiated with one variable, a string representing the name of the SObject you wish to query. So instead of writing out an inline SOQL statement with all fields spelled out and running the risk of breaking it when you add a field, you can do this:

 

MyCustomObject__c mco = Database.query(new selectall('MyCustomObject__c').soql);

 The above Code is equivalent to this:

 

MyCustomObject__c mco = [select Id, address_picker__c, Name,quote_date__c,ownerid, expiration_date__c, system_inspection__c, CreatedDate, CreatedById, Account__c, Additional_Cost__c, Client__c, Contact__c, Grand_Total__c, Has_Change_Order__c, Name_Link__c, Name__c, Project_name__c, Quote_Name__c, Quote_Number__c, Revision__c, Sold__c, Source_Quote__c, Tax__c, Type__c, Total__c, Full_Project_Name__c, Suite__c from MyCustomObject__c];
    

 If you want to writ a join, or for whatever reason you want just a list of fields, use the allfields property as opposed to the soql property.

 

If you like what you see and think you might want to try it out here is the source code:

 

SelectAll Class:

 

public class SelectAll{
    
    public string allfields;
    public string SOQL;
    
    public selectall(){
    }
    
    public SelectAll(string sfo){
        map<string, schema.sobjecttype> allSObjects = schema.getglobaldescribe();
        schema.sobjecttype q = allsobjects.get(sfo);
        schema.describesobjectresult d = q.getdescribe();
        map<string, schema.sobjectfield> m = d.fields.getmap();
        set<string> s = m.keyset();
        string query = '';
        for(string f : s){
            query = query+f+', ';
        }
        query = query.substring(0,query.length()-2);
        this.allfields = query;
        this.SOQL = 'SELECT ' + query + ' FROM ' + sfo;
    }
}

 Here is a VF Page and a Custom Controller you can use to verify that it works:

 

VF Page:

 

<apex:page controller="dynamicsqltest">
    {!query}
</apex:page>

 

 

Custom Controller:

 

public class dynamicsqltest {
    public selectall mco = new selectall('MyCustomObject__c');
    public string getquery(){
        string q = this.mco.soql;
        return q;
    }
}

 

The above VF Page will display the SOQL statement that is generated when the name of the SObject is passed to it when you instantiate the SelectAll Class. If you really want to see it in action, after you have the VF page up and running, go add a custom field to your SObject and refresh the VF Page to verify that the new field is in the SOQL statement.

 

 

 

 

 

sfdcfoxsfdcfox

I give you a +1 for creativity. While I certainly agree that this is sometimes useful, there is one small problem, and that is that it violates one of the primary rules of database query programming: You should generally never use a SELECT * syntax because of the reduction of code readability, increases in query times (marginally, but still worth noting), and increases in memory usage (there is a 100k memory limit usage in Apex Code). That being said, you are absolutely right that there should be a select * option for the precise scenario that you mentioned when you need to automatically acquire all fields for a record, such as a Visualforce page. Hopefully some people will find this useful, but will only use it when it actually makes sense. A trigger that operates on two fields would hardly benefit from this, and would actually harm performance since records that the triggers run against would take longer to save.

Jim BoudreauxJim Boudreaux

Oh, I agree it's clumsy and I didn't mean to suggest this should be used every time, but only when you really need to.