Tuesday, September 04, 2018

Dynamic SOQL with SubQuery using SObject class

Requirement to have dynamic query that enable to retrieve the child of the parent objects.For example query the Opportunities field while querying Account instead of run separate query for each object.

For this post, I hard code object name and field name which actually can be done dynamically.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
String childF   = 'Name'; 
String childO_1 = 'Opportunity'; 
String childF_1   = 'Name';      

 if(childO  == 'Contact'){ 
     childO = 'Contacts'; 
 } 
 if(childO_1  == 'Opportunity')
  { childO_1 = 'Opportunities';
 } 

// create dynamic SOQL query string 

String query = 'SELECT id, (select ' +childF+ ' from ' +childO+') ,(select '+childF_1 + ' from '+childO_1+') FROM ' +parentO;

// query and store  results in variable p 

SObject[] p = Database.query(query);

for(Integer i = 0; i < p.size(); i++){ 

    system.debug('Parent result from SOQL query: ' +p[i]);

  //sample output ::: Parent result from SOQL query: Account:{Id=0010Q000002RtLCQA0, RecordTypeId=012a00000018HPEAA2, CurrencyIsoCode=GBP}    
 
 //this can  be handled dynamically   
  if(p.get(i).getSObjects(childO) <> null){       

   system.debug('Child result(s) from SOQL query: ' +p.get(i).getSObjects(childO));      
   //sample output :: Child result(s) from SOQL query: (Contact:{AccountId=0010Q000002RtLCQA0, Id=0030Q0000022un6QAA, Name=Clara James, RecordTypeId=0121p0000006x5bAAA, CurrencyIsoCode=GBP})     }  
   }
   if(p.get(i).getSObjects(childO_1) <> null){      
   system.debug('Child result(s) from SOQL query: ' +p.get(i).getSObjects(childO_1));      

   //sample output :: Child result(s) from SOQL query: (Opportunity:{AccountId=0010Q000002RtLCQA0, Id=0060Q000001s6nMQAQ, Name=Test Opportunity, RecordTypeId=012a00000018HPLAA2, CurrencyIsoCode=GBP}, Opportunity:{AccountId=0010Q000002RtLCQA0, Id=0060Q000001rpYaQAI, Name=Blue Hippo, RecordTypeId=012a00000018HPLAA2, CurrencyIsoCode=GBP})    

 }   

 }

In order to retrieve the child use getSObjects() .

The code above is adapted from this forum.


No comments:

Post a Comment