Stat Tracker

Monday, January 3, 2011

Building Dynamic SOQL - Select All Query

Salesforce SOQL does not allow select * queries. For example, you cannot do "Select * from Account where Id = 'XXXXXXXX'". This can be confusing to new Apex developers who are familiar with SQL, since SOQL syntax is very close to SQL. This presents developers with the question: How can I dynamically query for all the fields on an object? The answer is you can use Salesforce Schema Describe objects to dynamically build SOQL queries at run time to query for all fields on a record.

In this post, we are going to use the Schema Describe methods to do a few interesting things.
  1. We are going to dynamically determine the Object Type based on the ID at run-time.
  2. We are going to retrieve all the field definitions for the Object.
  3. We are going to build a SOQL query dynamically to query for all the fields.
  4. Just for fun we are going to populate the results dynamically in a Visualforce Page.
 The first thing we need to do is use the Schema classes to retrieve the SObject describe results into memory:
     Map<String,Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();

Now that we have the Schema SObjects in memory, we can iterate over the SObject to determine which type of SObject this ID referers to by calling this code here:


        List<Schema.SObjectType> sobjects = schemaMap.values();
        List<Sobject> theObjectResults;
        Schema.DescribeSObjectResult objDescribe;
        List<Schema.SObjectField> tempFields;
        for(Schema.SObjectType objType : sobjects)
        {
            objDescribe = objType.getDescribe();
            String sobjectPrefix = objDescribe.getKeyPrefix();
            if(id != null && sobjectPrefix != null && id.startsWith(sobjectPrefix))
            {
                objectType = objDescribe.getLocalName();
                Map<String, Schema.SObjectField> fieldMap = objDescribe.fields.getMap();
                tempFields = fieldMap.values();
                for(Schema.SObjectField sof : tempFields)
                {
                    fields.add(sof.getDescribe());
                }
                getAllQuery = buildQueryAllString(fields,objDescribe,id);
            }
        }
       
        resultObject = Database.query(getAllQuery);
       
        for(Schema.DescribeFieldResult dfr : fields)
        {
            fieldVals.add(new GenericFieldVO(dfr,resultObject));
        }

We use the objDescribe.getKeyPrefix() method to retrieve the prefix for the Object. This is a great API call that I seldom see used. Many times I see developers hard-code the prefix in the code, which I personally can't stand. After we have the prefix, we check that against the ID to see if the ID starts with the prefix. If it does, then we know that this is the Object Describe to use.

Once we have the correct SObject definition, then we can get all the field definitions by calling objDescribe.fields.getMap(). This returns a Map of the SObjectField type which contains the field definitions (labels, data type, etc). With this information we are now ready to build a dynamic query which will query for all the data for this particular ID.
    //Build the Query String
    private String buildQueryAllString(List<Schema.DescribeFieldResult> queryFields,DescribeSObjectResult obj, String theId)
    {
        String query = QUERY_SELECT;
        for(Schema.DescribeFieldResult dfr : queryFields)
        {
            query = query + dfr.getName() + ',';
        }
        query = query.subString(0,query.length() - 1);
        query = query + QUERY_FROM;
        query = query + obj.getName();
        query = query + QUERY_WHERE;
        query = query + theId + '\'';
        system.debug('Build Query == ' + query);
        return query;
    }


Bam. We now have the ability to build dynamic queries which will retrieve all the information for a object. For this example I have built a Visualforce Page which displays the dynamic values. I will included the full source for this at the bottom of this post. Here is the output of our dynamic SOQL calls for when I give it an Contact ID:
And then I just provide a second ID of a Account:




You can see that the values are dynamically queried and populated on the screen. Of course this particular example doesn't have much business value, you can always just put http://www.salesforce.com/XXXXXXXXX where XXXXXXX is your ID and go straight to the record. But this example shows that you can build dynamic SOQL queries to do 'Select *' type functionality with relative ease.

Now here is the full Apex code dump of this simple page.

To test, just simply pass a URL like https://c.na3.visual.force.com/apex/GenericSelectAll?id=XXXXXXXXX into your browser where ?id=XXXXXXXXXXX is your ID.

SchemaManager
//This class will do all the methods to retrieve schema infomraiton on SObject for apex
//This will ensure that multiple calls to descibes aren't called so we don't hit gov limits
public with sharing class SchemaManager
{
    private static Map<String, Schema.SObjectType> sobjectSchemaMap;
   
    public static Map<String,Schema.SObjectType> getSchemaMap()
    {
        if(sobjectSchemaMap == null)
        {
            sobjectSchemaMap = Schema.getGlobalDescribe();
        }
        return sobjectSchemaMap;
    }
   
    //Retrieve the specific Schema.SobjectType for a object so we can inspect it
    public static Schema.SObjectType getObjectSchema(String objectAPIName)
    {
        getSchemaMap();
        Schema.SObjectType aSObjectType = sobjectSchemaMap.get(objectAPIName);
        return aSobjectType;
    }
}


GenericSelectAllController
//A simple custom controller that will take any ID as input
//and query for all fields (up to 90) on the SObject dynamically
public with sharing class GenericSelectAllController
{
    public List<Schema.DescribeFieldResult> fields {get;set;}
    public List<GenericFieldVO> fieldVals {get;set;}
    public String getAllQuery {get;set;}
    public SObject resultObject {get;set;}
    public String objectType {get;set;}
    public String searchId {get;set;}
    public List<SObject> vals {get;set;}
    public static final String ERROR_ID_MISSING = 'There was no id passed in the parameters. Id is required.';
    public static final String QUERY_SELECT = 'select ';
    public static final String QUERY_FROM = ' from ';
    public static final String QUERY_WHERE = ' where Id = \'';
    //Instantiate the controller. If there is no ID then send the error message to the page.
    public GenericSelectAllController()
    {
        init();
        String id = ApexPages.currentPage().getParameters().get('id');
        if(id == null || id == '')
        {
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.FATAL,ERROR_ID_MISSING));
        }
        else
        {
            searchId = id;
            processSchemaInfo(id);
        }
    }
   
    //Process the Schema information
    //1. Retrieve the global Schema Information
    //2. Iterate over the SObject Schema Information
    //2.A Retrieve the SObject Key Prefix and match to the ID passed into page
    //2.B Describe all the Fields for the SObject
    //2.C Build a Query String from all the Fields
   
    private void processSchemaInfo(String id)
    {
        system.debug(id);
        Map<String,Schema.SObjectType> schemaMap = SchemaManager.getSchemaMap();
        List<Schema.SObjectType> sobjects = schemaMap.values();
        List<Sobject> theObjectResults;
        Schema.DescribeSObjectResult objDescribe;
        List<Schema.SObjectField> tempFields;
        for(Schema.SObjectType objType : sobjects)
        {
            objDescribe = objType.getDescribe();
            String sobjectPrefix = objDescribe.getKeyPrefix();
            if(id != null && sobjectPrefix != null && id.startsWith(sobjectPrefix))
            {
                objectType = objDescribe.getLocalName();
                Map<String, Schema.SObjectField> fieldMap = objDescribe.fields.getMap();
                tempFields = fieldMap.values();
                for(Schema.SObjectField sof : tempFields)
                {
                    fields.add(sof.getDescribe());
                }
                getAllQuery = buildQueryAllString(fields,objDescribe,id);
            }
        }
       
        resultObject = Database.query(getAllQuery);
       
        for(Schema.DescribeFieldResult dfr : fields)
        {
            fieldVals.add(new GenericFieldVO(dfr,resultObject));
        }
       
    }
   
    private void init()
    {
        getAllQuery = '';
        fields = new List<Schema.DescribeFieldResult>();
        fieldVals = new List<GenericFieldVO>();
    }
   
    //Build the Query String
    private String buildQueryAllString(List<Schema.DescribeFieldResult> queryFields,DescribeSObjectResult obj, String theId)
    {
        String query = QUERY_SELECT;
        for(Schema.DescribeFieldResult dfr : queryFields)
        {
            query = query + dfr.getName() + ',';
        }
        query = query.subString(0,query.length() - 1);
        query = query + QUERY_FROM;
        query = query + obj.getName();
        query = query + QUERY_WHERE;
        query = query + theId + '\'';
        system.debug('Build Query == ' + query);
        return query;
    }
}




GenericFieldVO


GenericFieldVO
/* ============================================================
 * Part of this code has been modified from source that is part of the "apex-lang" open source project avaiable at:
 *
 *      http://code.google.com/p/apex-lang/
 *
 * This code is licensed under the Apache License, Version 2.0.  You may obtain a
 * copy of the License at:
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 * ============================================================
 */


// A Generic View Object which is used to wrap generic SObject Field Data for Visualforce Page display.
public with sharing class GenericFieldVO
{
    public String fieldLabel {get;set;}
    public String stringVal {get;set;}
    public Boolean boolVal {get;set;}
    public Date dateVal {get;set;}
    public Integer intVal {get;set;}
    public Double doubleVal {get;set;}
    public DateTime dateTimeVal {get;set;}
    public ID idVal {get;set;}
    public Boolean isBool {get;set;}
   
    private static final List<Schema.DisplayType> STRING_TYPES      = new List<Schema.DisplayType>{
    Schema.DisplayType.base64
    ,Schema.DisplayType.Email
    ,Schema.DisplayType.MultiPicklist
    ,Schema.DisplayType.Phone
    ,Schema.DisplayType.Picklist
    ,Schema.DisplayType.String
    ,Schema.DisplayType.TextArea
    ,Schema.DisplayType.URL
    };
    private static final List<Schema.DisplayType> INTEGER_TYPES     = new List<Schema.DisplayType>{
        Schema.DisplayType.Integer
    };
    private static final List<Schema.DisplayType> ID_TYPES          = new List<Schema.DisplayType>{
        Schema.DisplayType.ID
        ,Schema.DisplayType.Reference
    };
    private static final List<Schema.DisplayType> DOUBLE_TYPES      = new List<Schema.DisplayType>{
        Schema.DisplayType.Currency
        ,Schema.DisplayType.Double
        ,Schema.DisplayType.Percent
    };
    private static final List<Schema.DisplayType> DATETIME_TYPES    = new List<Schema.DisplayType>{
        Schema.DisplayType.DateTime
    };
    private static final List<Schema.DisplayType> DATE_TYPES        = new List<Schema.DisplayType>{
        Schema.DisplayType.Date
    };
    private static final List<Schema.DisplayType> BOOLEAN_TYPES     = new List<Schema.DisplayType>{
        Schema.DisplayType.Boolean
        ,Schema.DisplayType.Combobox
    };

   
   
    public GenericFieldVO(Schema.DescribeFieldResult sourceField, SObject source)
    {
        fieldLabel = sourceField.getLabel();
        if(contains(STRING_TYPES,sourceField.getType())){
            stringVal = (String)source.get(sourceField.getName());
        } else if(contains(INTEGER_TYPES,sourceField.getType())){
            intVal = (Integer)source.get(sourceField.getName());
        } else if(contains(ID_TYPES,sourceField.getType())){
            idVal = (ID)source.get(sourceField.getName());
        } else if(contains(DOUBLE_TYPES,sourceField.getType())){
           doubleVal = (Double)source.get(sourceField.getName());
        } else if(contains(DATETIME_TYPES,sourceField.getType())){
           dateTimeVal = (DateTime)source.get(sourceField.getName());
        } else if(contains(DATE_TYPES,sourceField.getType())){
            dateVal = (Date)source.get(sourceField.getName());
        } else if(contains(BOOLEAN_TYPES,sourceField.getType())){
           boolVal = (Boolean)source.get(sourceField.getName());
           isBool = true;
        }
    }
   
    private static Boolean contains(List<Schema.DisplayType> aListActingAsSet, Schema.DisplayType typeToCheck){
        if(aListActingAsSet != null && aListActingAsSet.size() > 0){
            for(Schema.DisplayType aType : aListActingAsSet){
                if(aType == typeToCheck){
                    return true;
                }
            }
        }
        return false;
    }
}

GenericSelectAllPage
<apex:page controller="GenericSelectAllController">
    <style>
        table {width:100%;}
        td {width:100%;}
    </style>
    <apex:messages />
    <apex:pageBlock title="Generic Search All: {!searchId}">
        <apex:outputPanel layout="block" style="width:100%;" rendered="{!resultObject != null}" id="SobjectDetailPanel">
            <apex:outputText style="font-weight:bold;" value="Type: {!objectType}"/>
            <br/>
            <br/>
            <apex:repeat value="{!fieldVals}" var="fieldVal">
                <apex:outputText style="font-weight:bold;" value="{!fieldVal.fieldLabel}: "/>
                <apex:outputPanel rendered="{!fieldVal.stringVal != null}">
                    <apex:outputText value="{!fieldVal.stringVal}"/>
                </apex:outputPanel>
                <apex:outputPanel rendered="{!fieldVal.isBool == true}">
                    <apex:outputText value="{!fieldVal.boolVal}"/>
                </apex:outputPanel>
                <apex:outputPanel rendered="{!fieldVal.idVal != null}">
                    <a href="/{!fieldVal.idVal}"><apex:outputText value="{!fieldVal.idVal}"/></a>
                </apex:outputPanel>
                <apex:outputPanel rendered="{!fieldVal.intVal != null}">
                    <apex:outputText value="{0, number, 0}">
                       <apex:param value="{!fieldVal.intVal}" />
                     </apex:outputText>
                </apex:outputPanel>
                <apex:outputPanel rendered="{!fieldVal.dateVal != null}">
                    <apex:outputText value="{0,date,yyyy.MM.dd}">
                       <apex:param value="{!fieldVal.dateVal}" />
                    </apex:outputText>
                </apex:outputPanel>
                <apex:outputPanel rendered="{!fieldVal.dateTimeVal != null}">
                    <apex:outputText value="{0,date,yyyy.MM.dd G 'at' HH:mm:ss z}">
                       <apex:param value="{!fieldVal.dateTimeVal}" />
                    </apex:outputText>
                </apex:outputPanel>
                <apex:outputPanel rendered="{!fieldVal.doubleVal != null}">
                    <apex:outputText value="{0, number,0.00}">
                       <apex:param value="{!fieldVal.doubleVal}" />
                     </apex:outputText>
                </apex:outputPanel>
                <br/>
                <br/>
            </apex:repeat>
        </apex:outputPanel>
    </apex:pageBlock>
</apex:page>