Salesforce - Generate dynamic inner query to fetch parent and related child records

Use Case: In many scenarios, we need to clone the records with related child records.

Issue / Limitation: We may simply use the "clone" method from SObject class. But if we use the clone method, we will get only fields available in the user interface (pagelayout)

Solution: Using inner query we can fetch the records with all fields

Note: SOQL will support only 20K characters, so better to include only necessary fields (not all the fields from schema) in the queries

Below is the example code to query Account and it's related Contacts and Opportunity records.

DataService Class
/**
* @author     Rajadurai Kalyanasundaram
* @date       July 23, 2019
* @decription Data Service class to query the parent and child records
*/
public class DataService{

    public static Map<String, String> childObjAPIAndRelName = new Map<String, String>();
    public static Set<String> parentObjRelFields = new Set<String>();
    public static Map<String, String> childConditions = new Map<String, String>();
    public static Map<String, String> objectAndListOfFields = new Map<String, String>();
 
     /**
    * @method getAccountAndChildRecordsData
    * @param List<Id> 
    * @return  List<Account>
    * Description: Pass list of parent object Id's. It will return the parent record with its child records
    */
    public static List<Account> getAccountAndChildRecordsData(List<Id> parentRecordIdSet){
     
        //Just add the child object API name and its relationship name
        childObjAPIAndRelName = new Map<String, String>{
                'Opportunity' => 'Opportunities',
                'Contact' => 'Contacts'
        };
     
        //Here you can pass any filter criteria for child objects
        childConditions = new Map<String, String>{
            'Opportunity' => ' WHERE AccountId IN: parentRecordIdSet ',
            'Contact' => ' WHERE AccountId IN: parentRecordIdSet '
        };
     
        //Optionally you can pass the parent object relationship fields
        parentObjRelFields = new Set<String>();
     
        //In this map, you can pass the child object fields. If you are not passing the child object fields, it will take all the fields from schema
        objectAndListOfFields = new Map<String, String>{             
            'Contact' => 'Id, Name, FirstName, LastName, Birthdate, Email, AccountId',
            'Opportunity' => 'Id, Name, AccountId, Amount'
        };

        //getInnerQuery-ParentObject, RelationshipFields, childObjAPIAndRelName, contactIdSet, parentQueryFilter
        String queryString = Utility.getInnerQuery('Account', objectAndListOfFields, parentObjRelFields, childObjAPIAndRelName, childConditions, parentRecordIdSet, 'ORDER BY Name');
     
        List<Account> accList = Database.query(queryString);
        for(Account acc: accList){
         
            System.debug('Parent Account::::'+acc.Name);
            for(Contact con: acc.Contacts){
             
                System.debug('Child Contact::::'+con.Name);
            }
         
            for(Opportunity opp: acc.Opportunities){
             
                System.debug('Child Opportunity::::'+opp.Id);
            }
        }
        return accList;
    }

}

Utility Class
/**
* @author     Rajadurai Kalyanasundaram
* @date       July 23, 2019
* @decription Utility class to generate the dynamic inner query
*/
public class Utility {
 
    /**
    * @method getInnerQuery
    * @param1 ParentObjectName
    * @param2 parentObjectRelationshipfields
    * @param3 Map of child objects API name and relationship name
    * @param4 Set of Id's
    * @param5 Query condition for parent query
    * @description Method  to build the dynamic inner query
    * @return  Query String
    */
    public static String getInnerQuery(String parentObjectAPIName, Map<String, String> objectAndFields, Set<String> parentObjectRelationshipfields, Map<String, String> childObjAPIAndRelName, Map<String, String> childObjectConditions, List<Id> parentRecordIdSet, String parentQueryCondition) {
     
     
        String parentObjFieldsString = '';
        if ( parentObjectRelationshipfields == NULL ) parentObjectRelationshipfields = new Set<String> ();
     
        if(objectAndFields != null && objectAndFields.containsKey(parentObjectAPIName)){
         
            parentObjFieldsString = objectAndFields.get(parentObjectAPIName);
        }else{
         
            parentObjFieldsString = getObjectFields(parentObjectAPIName);
        }
     
        if(parentObjectRelationshipfields != null && parentObjectRelationshipfields.size() > 0){
         
            parentObjFieldsString += ', '+( String.join ( new List<String> ( parentObjectRelationshipfields), ',' + ' ' ) );
        }
     
        List<String> innerQueryStringList = new List<String>();
        String combinedInnerQueryString = '';
        for(String childObjectAPIName : childObjAPIAndRelName.keySet()){
         
            String condition = (childObjectConditions != null && childObjectConditions.containsKey(childObjectAPIName)) ? childObjectConditions.get(childObjectAPIName): '';
            String objectFieldsString = '';         
            if(objectAndFields != null && objectAndFields.containsKey(childObjectAPIName)){
             
                objectFieldsString = objectAndFields.get(childObjectAPIName);
            }         
         
            innerQueryStringList.add('('+getQuery(childObjectAPIName, objectFieldsString, condition, childObjAPIAndRelName.get(childObjectAPIName))+')');
        }
     
        combinedInnerQueryString = ( String.join ( new List<String> (innerQueryStringList), ',' + ' ' ) );
        String queryValue = 'SELECT ' + parentObjFieldsString + ','+ combinedInnerQueryString + ' FROM '+ parentObjectAPIName +' WHERE Id IN:parentRecordIdSet ';
     
        if(String.isNotBlank(parentQueryCondition)){
         
            // In the parent query, if we have only Order By we don't need to add 'AND' operator       
            if(parentQueryCondition.startsWithIgnoreCase('Order')){
             
                queryValue += ' '+parentQueryCondition;
            }else{
                queryValue += ' AND '+parentQueryCondition;
            }
        }     
        return queryValue;
    } 
 
    /**
    * @method getQuery
    * @param1 Object name
    * @param2 Set of relationship fields
    * @param3 Query condition
    * @param4 Child object relationship name
    * @description Method  to build the dynamic query
    * @return  Query String
    */
    public static String getQuery(String objectName, String fieldString, String condition, String relationshipName) {
     
        String objFieldsString = '';
        if ( String.isNotBlank(fieldString) ){
         
            objFieldsString = fieldString;
        }else{
         
            objFieldsString = getObjectFields(objectName);
        }
     
        List<String> strings = new List<String> ();
        strings.add ( 'SELECT ' );
        strings.add ( objFieldsString );
        strings.add ( 'FROM' );
        // relationshipName -> To build the inner query
        // objectName -> To build direct/parent object query
        if(String.isNotBlank(relationshipName)){
         
            strings.add (relationshipName);
        }else{
         
            strings.add (objectName);
        }
     
        if (!String.IsBlank ( condition ) ) {
         
            strings.add ( condition );
        }
        return String.join (strings, ' ' );
    }
 
    public static String getObjectFields(String objectName){
     
        // Get a map of field name and field token
        Map<String, Schema.SObjectField> items = Schema.getGlobalDescribe().get(objectName).getDescribe().Fields.getMap();
        List<String> selectFields = new list<String>();
     
        if (items != null){
         
            for (Schema.SObjectField ft : items.values()){ // loop through all field tokens (ft)
             
                Schema.DescribeFieldResult fd = ft.getDescribe(); // describe each field (fd)
                if (fd.isCreateable()){
                 
                    selectFields.add(fd.getName());
                }
            }
        }     
        return ( String.join ( new List<String> ( selectFields), ',' + ' ' ) );
    }
}

Code Snippet for testing 
Execute below sample code in developer console and check the output

System.debug(DataService.getAccountAndChildRecordsData( new List<Id>{'0017F00001vCyrq'}));

IF() to Ternary Operator conversion in Javascript

<script>
function myFunction() {
   
    var str = "IF((4+2)==5,true,false)";
    str = str.replace(/\s/g, '');
    var length = str.length;
    while (str.lastIndexOf("(") != -1 ) {
       
        var start = str.lastIndexOf("(");
        var strEnd = str.substring(start,str.length);
        var end = start + strEnd.indexOf(")");
        var tempWithIf = str.substring(start-2, end+1);
        var tempWithBrack = str.substring(start, end+1);
        var temp = str.substring(start+1, end);
        var tempArr = temp.split(',');
        var strReplace;
        if (tempArr.length == 3) {
            strReplace = tempArr[0] + "?" + tempArr[1] + ":" +  tempArr[2];
            str = str.replace(tempWithIf,strReplace);
        } else {
           
             strReplace = tempWithBrack.replace('(','{');
             strReplace = strReplace.replace(')','}');
             str = str.replace(tempWithBrack,strReplace);
        }
     
    }
    str = str.replace(/{/g,'(');
    str = str.replace(/}/g,')');
    console.log(eval(str));
   
}
</script>

Thanks to Ramarajan

Salesforce Primitive Data Types(dataTypes allowed in apex)

1. Blob – This data type represents binary data stored as a single object.
Example: Blob b1 = Blob.valueof(‘idea’);

2. Boolean – This data type represents Value that can only be assigned true, false, or null.
Example: Boolean isvar = true;

3. Date – This data type represents particular day.
Example: Date myDateVar = Date.today();
Date weekStart = myDateVar.toStartofWeek();

4. Datetime – This data type represents particular time and date.
Example: Datetime myDateTimeVar = Datetime.now();
Datetime newd = myDateTimeVar. addMonths(5);

5. Decimal – This data type represents Number that includes a decimal
point. Decimal is an arbitrary precision number.
Example:Decimal myVar = 12.4567;
Decimal divDecVar = myVar.divide(7, 2, System.RoundingMode.UP);
system.assertEquals(divDecVar,1.78);

6. Double – Represents 64-bit number that includes a decimal point. Minimum value -2^63. Maximum value of 2^63-1.
Example:Double d=3.14159;

7. ID – Represents 18-character Force.com record identifier.
Example:ID id=’00300000003T2PGAA0′;

8. Integer – 32-bit number that doesn’t include a decimal point. Minimum value
-2,147,483,648 — maximum value of 2,147,483,647
Example: Integer i = 1;

9. Long – Represents 64-bit number that doesn’t include a decimal point. minimum value of -263 — maximum value of 263-1.
Example:Long l = 2147483648L;

10. String – Represents Set of characters surrounded by single quotes.
Example:String s1 = ‘Hello';

11. Time – Represents particular time.
Example: Time myTimeVar = Time.newInstance(18, 30, 2, 20);
Integer myMinutes = myTimeVar.
minute();

12. sObject - Salesforce object (Custom or Standard)
Example : List<sObject> obj = new List<sObject>();

Collections
-----------
1. List
2. Set
3. Map

AngularJS Filters Example with Salesforce Page (Bootstrap style)

Example Visualforce Page

<apex:page controller="ContactListView" sidebar="false" >
    
     <style>
         tr.dataRow {
           background-color:white;
         }
         tr.dataRow:hover {
           background-color: #e3f3ff;
         };
     </style>
     
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css" />
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap-theme.min.css" />
     <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
     <script src= "https://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script>
    
    <apex:pageBlock >
    
        <div ng-app="ContactListViewApp" ng-controller="ContactListViewController">
            <div class="row">
                <div class="form-group col-md-2">
                    <label> Search: </label>
                    <input type="text" ng-model="keyWord.$" placeholder="Enter keyword" />
                </div>
                <div class="form-group col-md-2">
                    <label> Contact Search: </label>
                    <input type="text" ng-model="keyWord.Name" placeholder="Enter Contact name" />
                </div>
                <div class="form-group col-md-2">
                    <label> Account Search: </label>
                    <input type="text" ng-model="keyWord.Account.Name" placeholder="Enter Account name" />
                </div>
                <div class="form-group col-md-2">
                    <label> Phone Search: </label>
                    <input type="text" ng-model="keyWord.Phone" placeholder="Enter Contact pnone"/>
                </div>
            </div>
            <div  style="height: 600px;overflow: auto;" class="table-responsive">
            
                <table cellpadding="0px" cellspacing="0px" class="table table-striped table-hover">
                    <tr>
                        <th> Name </th>
                        <th> Account </th>
                        <th> Phone </th>
                    </tr>
                    <tr ng-repeat="con in conListJSON | filter:keyWord">
                        <td>
                            {{ con.Name }}
                        </td>
                        <td>
                            {{ con.Account.Name }}
                        </td>
                        <td>
                            {{ con.Phone }}
                        </td>
                    </tr>
                </table>
            </div>
            <script>
                angular.module('ContactListViewApp', []).controller('ContactListViewController', function($scope){
                    
                    $scope.conListJSON = JSON.parse('{!conListJSON}');
                    console.log($scope.conListJSON);
                    console.log(JSON.stringify('{!conListJSON}'));
                });           
            </script>        
        </div>
    </apex:pageBlock>
</apex:page>

Example Controller

public class ContactListView {
    
    public List<Contact> conList{get;set;}
    public String conListJSON{get;set;}
    public ContactListView(){
    
        conList = [SELECT Id, Name, AccountId, Account.Name, Phone FROM Contact WHERE AccountId != NULL];      
        conListJSON = JSON.serialize(conList);  
        conListJSON = String.escapeSingleQuotes(conListJSON);
    }
}

Salesforce HTML5 Manifest (Visualforce Offline Access)

Simple Example for Offline Manifest in Salesforce Visualforce.

- Salesforce cache supported version : upto 26

You can check the caches in "chrome://appcache-internals/"

Page1: TestPage1

<apex:page showHeader="false" standardStylesheets="false" contentType="text/cache-manifest">CACHE MANIFEST
#review
/apex/testpage2?cache=cache2

NETWORK:
*
</apex:page>

Page2 : TestPage2

<apex:page showHeader="false" sidebar="false" standardStylesheets="false" docType="html-5.0" manifest="/apex/TestPage1">

     <header>
          <h1>Congratulations!</h1>
    </header>
      <article>
         <p>This page looks almost like HTML5!</p>
     </article>

</apex:page>

Multiplication calculator JQuery

<html>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
    <style>
        .schTable{}
        .noOfPay{}
        .amount{}
    </style>
    <body>
        <table class="schTable">
            <tr>
                <td> Total </td>
                <td id="totalId"> </td>
            </tr>
            <tr>
                <td>Input 1 </td>
                <td> Input 2 </td>
            </tr>
             <tr>
                <td><input type="text" class="noOfPay" onkeyup="calculateTotal();" /> </td>
                <td><input type="text" class="amount" onkeyup="calculateTotal();"/> </td>
            </tr>
             <tr>
                <td><input type="text" class="noOfPay" onkeyup="calculateTotal();"/> </td>
                <td><input type="text" class="amount" onkeyup="calculateTotal();"/> </td>
            </tr>
        </table>
    </body>
    <script>      
        var noOfPayArray = [];
        var amountArray = [];
        function calculateTotal(){  
            var i = 0;        
            $(".schTable .noOfPay").each(function( e ) {
               noOfPayArray[i] = $(this).val();
               i++;
            });
           
            i = 0;        
            $(".schTable .amount").each(function( e ) {                
                amountArray[i] = $(this).val();
                i++;
            });
            var total = 0.00;
            for(var i =0; i < amountArray.length; i++){              
               
                if(noOfPayArray[i] != '' && amountArray[i] != '' &&  noOfPayArray[i] != 'null' && amountArray[i] != 'null' && noOfPayArray[i] != null && amountArray[i] != null && !isNaN(noOfPayArray[i]) && !isNaN(amountArray[i])){
                 
                    total += parseFloat(noOfPayArray[i]) * parseFloat(amountArray[i]);
                }
            }
            document.getElementById('totalId').innerHTML = total.toFixed(2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");          
                   
        }      
    </script>
</html>

Web Chart's List

i) D3.js — Data-Driven Documents
    - Open source
    - https://github.com/mbostock/d3/wiki/Gallery

ii) Google Visualization Charts  
    - Pie chart doesn't show the connected lines for all slices
   
iii) Fusioncharts (http://www.fusioncharts.com/charts/pie-doughnut-charts/)
    - With "Fusionchart watermark" its free for both personal and commercial
    - If we need to remove water mark, we need to buy the licence
    - 2d Pie chart available with connected lines
   
iv) Flot
    - Flot is completely free to use and commercial support is provided on special request to the creator.
    - Pie chart available

v) ChartJs
    - Open source
    - Pie chart available
    - Not sure about connected legends

vi) EJS Chart
    - EJS Chart comes in free and paid versions. The free version limits you to use maximum of 1 chart per page and 2 series per chart.
   
vii) uvCharts
    - Open source
    - D3.js base
    - No connected lines for pie chart

viii) Chartist.js
    - Its animations only supported in modern browsers
    - No clear examples for pie charts
   
ix) jqChart - http://www.jqchart.com/jquery/chart/ChartTypes/PieChartLabels
    - Matched with requirement
    - Trail version only available for download
    - Licensed

x) amCharts (http://www.amcharts.com/demos/pie-chart-with-legend/)
    - The free version of amChart will leave a backlink to its website on the top of every chart.
    - Pie chart looks good
    - Commercial

xi) MeteorCharts
    - Commercial
   
xii) Highcharts JS
    - Free for personal use
    - Licence for commercial
    - Pie chart available

xiii) Chartkick
    - Its built for Ruby Application
    - Code available for Rubly only
   
xiv) n3-charts
    - Pie chart not available

xv) Ember Charts
    - Open source
    - Connected legend option not available
   
xvi) Smoothie Charts
    - Pie chart not available
    - Displaying only  smooth live time lines
   
Source : http://www.sitepoint.com/15-best-javascript-charting-libraries/