function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
TehNrdTehNrd 

Best way to query three level Parent-Child relationship?

What is the best way to query a three level parent child relationship? Assume you have the data model A > B > C.

 

You can't do this:

 

SELECT NAME, (SELECT Name, (SELECT Name FROM C__r) FROM B__r) FROM A

 

And you can't even do this:

 

(SELECT Name, (SELECT Name FROM C__r) FROM B__c)

 

But you can do this:

 

SELECT Name, (SELECT Name FROM B__r) FROM A__c

 

It would appear the only way to query the objects at the bottom of hte hierachy is when an entirely sepeare query. And then where do you put these? It would appear you need a seperate wrapper class to group everything together?

 

Thanks,

Jason

sfdcfoxsfdcfox

If A, B, and C all exist, you can perform your work in a single query; Instead of starting at the top, start from the bottom:

 

select b__r.a__r.name, b__r.a__c, b__r.Name, b__c, Id from C

You can go up four levels, even though you can only aggregate down one.

 

And yes, you can place all this data into a data structure that can be used in Visualforce, etc. Use a wrapper class.

 

Consider the following three maps:

 

Map<Id,A__c> mapA;
Map<Id,Map<Id,B__c>> mapB;
Map<Id,Map<Id,Map<Id,C__c>>> mapC;

 Using these three maps, you can use the relatively new "Map" access in Visualforce to display those records in any reasonable design. The maps all start with an A__c ID, then second two maps have a B__c ID as the second key, and the final one maps to C__c records. If you use the single-query method (the first query above), you can use the maps to keep your data deduplicated as you load it in. If you use a two-query or three-query method, you can perform the same steps, usually in fewer lines of script execution.

 

A three query method would be something like the following:

 

A__c[] ResultA = [SELECT Id FROM A__c];
B__c[] ResultB = [SELECT A__c,Id FROM B__c WHERE A__c IN :ResultA];
C__c[] ResultC = [SELECT B__c,Id FROM C__c WHERE B__c IN :ResultB];

This gives you three easy-to-read lists, which you can then use maps to piece together into coherent data.

 

Wrapper classes would probably be ideal, as you suggested, but using the maps alone would be sufficient, as opposed to entirely custom wrapper classes, which is overkill unless you have another reason you'd like to wrap the results (e.g. checkboxes, etc).
garybgaryb

I saw this post on my phone last night and favourited it so I could respond to it from my laptop, but sfdcfox has beaten me to the punch with a great answer. I agree with both approaches.
mkdjns.123456mkdjns.123456

I posted sfdcfox's first idea to twitter last night. It's a decent single query solution, if you're OK with getting the A__c and B__c records multiple times (assuming you have multiple B__c records with multiple C__c records).
TehNrdTehNrd

Querying up the hierarchy has one fairly significant drawback, you may not query all the data. For example some records may only be A > B and not have any C records. Or just A, with no B or C records. These A and B records would be totally missed in this type of query.

Here is what I ended up doing. It is by no means perfect...and frankly it is pretty ugly and complicated. It does make use of a wrapper class to structure the data. If first queries the top two layers of the hierarhcy and then queries the third layer.

 

List<RecordHierarchy> RecordHierarchys;

public List<RecordHierarchy> getRecordHierarchy(){
	if(RecordHierarchys == null){
		RecordHierarchys = new List<RecordHierarchy>();
		Map<Id,RecordHierarchy> RecordHierarchyMap = new Map<Id,RecordHierarchy>();
		
		//Query As and Bs, add to RecordHierarchy wrapper
		for(A__c obj : [SELECT Id, Name (SELECT Name from Bs__r) FROM Learning_Path__c]){
			//Create RecordHierchy object, add to map
			RecordHierarchy rh = new RecordHierarchy(obj);
			RecordHierarchyMap.put(obj.Id,rh);
		}
		
		//Query the Cs
		for(C__c obj : [SELECT Name B__c, B__r.A__c FROM C__c where B__r.A__c IN :RecordHierarchyMap.keySet()]){
			//Find the correct A record
			RecordHierarchy rec = RecordHierarchyMap.get(obj.B__r.A__c);
            
			//Get the correct B map and the C to a list in a map.
			rec.B_Cs.get(la.B__c).add(obj);
		}
		
		RecordHierarchys = RecordHierarchyMap.values();
	}
	return RecordHierarchys;
}

public class RecordHierarchy{
	A__c A;
	List<B__c> Bs;
	Map<Id,List<C__c>> B_Cs;
	
	public LearningPath(A__c a){
		this.A = a;
		this.Bs = a.B__r;
		
		//Create maps for Cs
		B_Cs = new Map<Id,List<C__c>>();
		for(B__c b : Bs){
			B_Cs.put(b.Id,new List<C__c>());  
		}
	}    
}

 
TehNrdTehNrd

And here is what the Visualforce would look like:

<apex:repeat value="{!RecordHierarchy}" var="rec">
	A: {!rec.A.Name}
	
	Bs: 
	<apex:repeat value="{!rec.Bs}" var="b">
		{!b.name}
		
		Cs:
		<apex:repeat value="{!rec.B_Cs[b.Id]}" var="c"> 
			{!c.Name} 
		</apex:repeat>
	</apex:repeat>
</apex:repeat>