Generate Query From Fieldsets

Monday, October 15, 2012

As Salesforce is becoming more and more advanced with every release we developer are trying to cope up with the new features released.
Salesforce are releasing awesome features with every release. One of the most remarkable feature is fieldsets.

Fieldsets takes away the most annoying problem of visualforce pages i.e. it makes the pages dynamic!!. Yes Dynamic. The let of configure the fields that
appear in your visualforce page.

How to use fieldsets in your visualforce page.??

<apex:page controller="AccountCon">  
   <apex:form>  
     <apex:pageblock>  
       <apex:pageblocksection columns="1">  
         <apex:repeat value="{!$ObjectType.Account.FieldSets.AccountFieldSet}" var="f">  
             <apex:inputfield value="{!accObj[f]}">  
         </apex:inputfield>  
       </apex:repeat>  
     </apex:pageblocksection>  
   </apex:pageblock>  
  </apex:form>  
 </apex:page>  

public class AccountCon(){  
  public Account accObj{get;set;}  
  public AccountCon(){  
  accObj = [SELECT id,Name FROM Account LIMIT 1];  
  }  
 }  

Well easy enough?
But there lies a problem with fieldsets. In the above example you can see that a static query brings out data from salesforce objects. It will work
just perfect, until and unless some new field that is not included in query is introduced in the query. Once such field is included the code will throw an
exception.

Solution??
Make your query as dynamic as fieldsets!!
To do this I have devised a util class that dynamically generates query using fieldset/fieldsets. Just pass the object name and fieldset name this
will generate a dynamic query according to the parameters passed. Use the returned string to query the data.

Show Me The Code!!
Below is the util class that can be used to generate dynamic query from fieldsets.

 /*  
 *This Class contains utility methods that are used by different classes  
 *in the Org  
 **/  
 public class AVCommonUtils{  
   Map<String, Schema.SObjectType> globalDescribe;   
   public AvCommonUtils(){  
     globalDescribe=Schema.getGlobalDescribe();  
   }  
   /*This method return queries form a single fieldset  
   public String generateQueryFromFieldSet(String sObjectName,String fieldSet,Set<String> additionalFields,String whereClause){  
     return generateQueryFromFieldSets(sObjectName,new Set<String>{fieldSet},additionalFields,whereClause);   
   }  
   /*  
   *This method generates query according to passed in object and fieldsets(plural) name  
   **/  
   public String generateQueryFromFieldSets(String sObjectName,Set<String> fieldSets,Set<String> additionalFields,String whereClause){  
     Set<String> fields = new Set<String>{'Id'};  
     String query='SELECT Id';  //initial query  
     if(additionalFields!=null)  
     for( String fs : additionalFields ) {            
       //add only unique fields  
       if(!fields.contains(fs)){  
         //maintain a set of fields so that only unique fields are added in query  
         fields.add(fs);  
         query = query+','+fs;  
       }      
     }  
     //describe the provided sObject  
     Schema.DescribeSObjectResult res=globalDescribe.get(sObjectName).getDescribe();  
     //get the fields set map  
     Map<String, Schema.FieldSet> fieldSetMap= res.fieldSets.getMap();  
     //iterate through provided fieldsets and generate query  
     for(String fieldSetName : fieldSets){  
       Schema.FieldSet fs = fieldSetMap.get(fieldSetName);  
       for( Schema.FieldSetMember fsm : fs.getFields() ) {            
         //add only unique fields  
         if(!fields.contains(fsm.getFieldPath())){  
           //maintain a set of fields so that only unique fields are added in query  
           fields.add(fsm.getFieldPath());  
           query = query+','+fsm.getFieldPath();  
         }      
       }  
     }  
     query = (whereClause == '' || whereClause ==null)   
             ? (query + ' FROM '+sObjectName)   
             : (query + ' FROM '+sObjectName + ' WHERE ' + whereClause);  
     return query;  
   }  
 }  

How to use the util class??
Let us rewrite the above controller "AccountCon" using the util class.

 public class AccountCon(){   
  public Account accObj{get;set;}   
  public AccountCon(){   
      AVCommonUtils util = new AVCommonUtils();  
      String query = util.generateQueryFromFieldSet('Account','AccountFieldSet',null,'LIMIT 1');  
       accObj = database.query(query);    
  }   
  }   

Well the util classs has two methods.

1. generateQueryFromFieldSet : This method generates query from only one fieldset
2. generateQueryFromFieldSets: This method takes in a set of fieldset name to generate query.

Using Variables In Dynamic SOQL - Uncovering

Monday, September 10, 2012

In Earlier post "Using Variables In Dynamic SOQL",  I wrote about how to use variables in a Dynamic SOQL. Well after playing with Dynamic SOQL, I found few interesting thing like:

·         You CAN directly use primitive data types in SOQL.

So what does this means?

This means you can directly use any Integer, String , Date, Datetime,Double, Id variable along with Collection of this variable within the query.

For Example: 

a) You are allowed to do this

//initialize the Datetime with current time

DateTime now = System.now();

//query accounts by merging the variable name inside the query string

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE CreatedDate =:now');

b)You can also include LISTs(Collections) in your queries

List<String> accNameList = new List<String>{'Acc1','Acc2'}

//query accounts by merging the variable name inside the query string

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE Name IN:accNameList');

·         You CANNOT use complex types in a Dynamic SOQL directly.

This means that you cannot use any Sobject, Apex Class or Any other user defined data type inside the Dynamic Query. In short you cannot use a dot (".") operator to specify a field value in a Dynamic query.

For Example :

                a)  Using an Sobject inside the query String will not work

           //initialize a Account with a Name value for demo

Account acc = new Account(Name='MyAccount');

//query accounts by merging the variable name inside the query string

//This will not work

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE Name =:acc.Name');

//But You can always make the above work by writing the code as.

           //initialize a Account with a Name value for demo

      Account acc = new Account(Name='MyAccount');

      String accountName = acc.Name

      //query accounts by merging the variable name inside the query string

      List<Account> accountList = Database.query('SELECT Id FROM Account WHERE                       
      Name =: accountName  ');

Hope this helps!.

Using Variables In Dynamic SOQL

Friday, August 17, 2012

Well how many times you have tried to use a list variable in a Dynamic SOQL?
We generally use Database.query() ,  which takes a query in string format and returns a List of sObject . The problem comes when want to use "IN" statement or want to use "date/date time" in a Dynamic query . I really tried a lot of combination, formatting the data,escapes etc.But finally was surprised to see if you just include the variable inside the query String, it just works !!!

Wasn't clear enough??

See the below example  which shows how to query all the accounts that are created today:

 
//initialize the Datetime with current time
DateTime now = System.now(); 
//query accounts by merging the variable name inside the query string 
List<Account> accountList = Database.query('SELECT Id FROM Account WHERE CreatedDate =:now'); 

Please Note :  that the variable "now" is included in the string itself.

Datetime field in Dynamic SOQL

Thursday, May 24, 2012

[You can see this blog for a more elegant solution ]

This was unknown to me and in past months I tried hard to use Datetime field in Dynamic SOQL query, well after doing lots of permutations and combinations I finally figured it out! which I though was not possible.

Well I guess this is worth sharing so here it is.

You can compare any Datetime field in Dynamic SOQL where query! Just keep in mind the format should be correct. You can use the format method to format datetime field like this where System.Now() is my Datetime field.

System.Now().format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\''));

OR

DateTime dt = System.Now();//initialize datetime with current datetime

String formatedDt = dt.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');//format the datetime to make it Dynamic Soql ready

So a sample query to extract all the Accounts where the CreatedDate is less than Today will be

DateTime dt = System.Now();//initialize datetime with current datetime

String formatedDt = dt.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');//format the datetime to make it Dynamic Soql ready

Database.query('Select Id FROM Account WHERE CreatedDate<'+formatedDt);