Pages

Thursday, May 10, 2012

Get the Best Out of Apex Describe Methods

After a long absence from the blogging and community, I have finally gotten the chance to write again!
Working very hard on various exciting projects (that I will be sharing with you in the upcoming posts), had brought me to a point where I felt there was no time do anything remotely personal!

Anyhow, now that I am back at it again, let's see if I still got it...
Today I like to show you how you can use Apex describe methods in Database Queries to write less code  and take advantage of the platform's API.

First off, how many times have you thought of being able to use the following in Apex?

 SELECT * FROM Account WHERE Name LIKE 'Test%' 

Well as you know when writing SOQL queries you always have to know what fields the Object has and if a field is added or removed your query is impacted!
While there are always pros and cons in using different methods of programming, you may find the below useful in certain scenarios:

Initially we need to build a utility method and then use it later on to dynamically build our query, let's call our utility Apex class: "Util".

public class Util
{
   public static string FormatFieldsForQuery(Map<String, Schema.SObjectField> M, string prefix)
   {
       Set<string> fieldSet = M.keySet();  
       string fields = '';
       for(string f : fieldSet) fields += prefix + f +',';
       if(fields.endsWith(',')) fields= fields.substring(0,fields.length()-1);
       return fields;
   }

}

Using the above class I can receive a Map of fields and generate a comma separated string of fields.
Very useful piece of code, now we can get to the actual work: constructing the SOQL Query!

Let's being with putting the escalation of the query together:

 string query = 'SELECT '+ ? + ' FROM Account WHERE Name LIKE \'Test%\''

Where we can replace the "?" with the actual list of fields.
That brings us to the last step, accessing the Schema information in Apex.
Apex Schema class is very useful to be able to quickly access the object's meta-data without the need to perform any additional database queries (SOQL).

Schema class provides access to property called sObjectType, this property allows you to gain access to all Objects within salesforce.com and further more all of their fields:

//to access type of an object:
Account acc = new Account();
System.assert(acc.getsObjectType() == Account.sObjectType);

//to get the describe result for an object:
Schema.DescribeSObjectResult r = Account.sObjectType.getDescribe();

//To describe a field of an object:
Schema.DescribeFieldResult f = Schema.sObjectType.Account.fields.Name;

//To get a map of all fields an object:
Map<String, Schema.SObjectField> map;
map = Schema.SObjectType.Account.fields.getMap();


Using the last statement (from above) it seems like I can get a list of all fields an object and then pass to it my Util class to get a comma-separated list of fields!
Now I guess all the pieces of the puzzle are coming together, let's add them all up and see how it goes:

//get the fields Map:
Map<String, Schema.SObjectField> map;
map = Schema.SObjectType.Account.fields.getMap();

//get the field list:
string fields = Util.FormatFieldsForQuery(map);

//perform the query:
string q = 'SELECT '+ fields +' FROM Account WHERE Name LIKE \'Test%\''
List<Account> accounts = Database.Query(q);

There is definitely more to learn about Schema class and what it offers, in future posts I will try to cover some of usages that you might find it useful or intriguing!
Ciao for now.


12 comments:

  1. Welcome back! I missed your posts. In your method, you really should do a check on isAccessible() since the describe will bring back all fields even if a user cannot see them. The API does not do this, but in Apex it does and you need to use isAccessible to double check access.

    ReplyDelete
  2. Thanks

    One question: what is the prefix for?

    ReplyDelete
  3. Good question, You can use the prefix to query related objects.

    Let's say of you are querying "Contact" and want to bring over some of "Account" fields too, then you can to pass a map of Account fields plus the prefix: "Account."

    output will be:
    SELECT id, name, Account.Id, Account.Name ... FROM Contact

    ReplyDelete
  4. hi sam
    i have to created Joined format report?there is no option for export to excel?then how to export the data to excel?please help to me

    ReplyDelete
  5. public class Util
    {
    public static string FormatFieldsForQuery(Map M )
    {
    Set fieldSet = M.keySet();
    string fields = '';
    for(string f : fieldSet) fields += f +',';
    if(fields.endsWith(',')) fields= fields.substring(0,fields.length()-1);
    return fields;
    }

    //to access type of an object:
    Account acc = new Account();


    //to get the describe result for an object:
    Schema.DescribeSObjectResult r = Account.sObjectType.getDescribe();

    //To describe a field of an object:
    Schema.DescribeFieldResult f = Schema.sObjectType.Account.fields.Name;

    //To get a map of all fields an object:
    Map map;
    map = Schema.SObjectType.Account.fields.getMap();

    //get the field list:
    string fields = Util.FormatFieldsForQuery(map);

    //perform the query:
    string q = 'SELECT '+ fields +' FROM Account WHERE Name LIKE \'Test%\''
    List accounts = Database.Query(q);
    public List getAccountRecords()
    {
    return accounts;
    }
    }
    i have place ur code in controller. but it showing errors in it.

    and one more since ur not creating instance of class how it should work in fields variable . i thought i should apply static to FormatFieldsForQuery method.
    can u clear my confusion plz..

    ReplyDelete
  6. please provide the error messages you are encountering.

    ReplyDelete
  7. Hi sam,
    I am new to salesforce getting the job in sfdc is difficult for me can u send any real-time scenarios in apex&VF.

    Thanks&Regrds
    Balaji
    nagabaloo@gmail.com

    ReplyDelete
  8. Hi sam,
    Here we can get Field label,name e.t.c. But How can I get created/modified date and created/modified by for individual field.

    ReplyDelete
  9. I noticed this blog entry and I am so thrilled by the style you create your posts! Which methods do you prefer spread the knowledge that you shared a brand new blog entry to your blog?

    ReplyDelete
  10. Code had errors.. updated working code if anyone wants it. (Not wrapped in class... obviously just add the Util. back and wrap the FormatFieldsForQuery in the Util class!)


    // Put this method in same class/controller/extension
    public static string FormatFieldsForQuery(Map M, string prefix){
    Set fieldSet = M.keySet();
    string fields = '';
    for(string f : fieldSet) fields += prefix + f +',';
    if(fields.endsWith(',')) fields= fields.substring(0,fields.length()-1);
    return fields;
    }

    // And your code to run query - Acc.Id is an account id you have from somewhere

    MAP mapA;
    mapA= Schema.SObjectType.Account.fields.getMap();
    string fields = FormatFieldsForQuery(mapA,'');
    string q = 'SELECT '+ fields +' FROM Account WHERE Id= \''+Acc.Id+'\'';
    List MyAccount = Database.Query(q);

    ReplyDelete
  11. How to retrieve created date of object in a pageblocktable using schema methods in visualforce page?

    help me......

    ReplyDelete