function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
jordanmjordanm 

Help with Loop Construction

Hi,


I need a hand building this loop for the following scenario. I have a grandparent that is passed to a page via query string, this grandparent refers to multiple parents, and those multiple parents each have multiple children

 

I want to yield this:

grandparent 1

+++parent 1

++++++child a

++++++child b

+++parent 2

++++++child c

++++++child d

 

The loop in the controller is confounding me.

parents = [SELECT id,Name  
                FROM Parent__c
                WHERE Grandparent__c=:grandparentId];        
        
        for(Parent__c parents1:parents)
            {
                // fetch the values by SOQL on the basis of parent id
                children=[select id,Name 
                            FROM Child__c
                            WHERE Parent__c=:parents1.id]; 
                            system.debug('size'+ parents.size());
                            system.debug('>>>>>>>>>>>>>>>>>');  
                children1.addall(children);
            }

 This is clearly incorrect. It is going to build a list of ALL the parents of the grandparent and then take ALL of their children and store them in children1 variable. This won't accomplish the tree-type view I want, it will instead show this:

grandparent 1

+++parent 1

++++++child a

++++++child b

++++++child c

++++++child d

+++parent 2

++++++child a

++++++child b

++++++child c

++++++child d

 

How should I build this loop?

 

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

That post is from 2009. Since then, they've made a couple of improvements. I just made a working mockup, which I will now copy directly here:

 

<apex:page standardController="Grandparent__c" extensions="treeview">
<form>
<pre>
    <apex:repeat value="{!tree}" var="grandparentid">
Grandparent: {!grandparents[grandparentid].name}<br/>
        <apex:repeat value="{!tree[grandparentid]}" var="parentid">
    Parent: {!parents[parentid].name}<br/>
            <apex:repeat value="{!tree[grandparentid][parentid]}" var="childid">
        Child: {!children[childid].name}
            </apex:repeat>
        </apex:repeat>
    </apex:repeat>
</pre>
</form>
</apex:page>
public with sharing class treeview {

    public map<id,map<id,id[]>> tree { get; set; }
    private grandparent__c grandparent;
    public map<id,grandparent__c> grandparents { get; set; }
    public map<id,parent__c> parents { get; set; }
    public map<id,child__c> children { get; set; }

    public treeview(ApexPages.StandardController controller) {
        Grandparent = (Grandparent__c)controller.getrecord();
        grandparents = new map<id,grandparent__c>();
        parents = new map<id,parent__c>();
        children = new map<id,child__c>();
        tree = new map<id,map<id,id[]>>();
        for(child__c child:[select id,name,parent__c,parent__r.grandparent__c from child__c where parent__r.grandparent__c = :grandparent.id]) {
            if(!tree.containskey(child.parent__r.grandparent__c)) {
                tree.put(child.parent__r.grandparent__c,new map<id,id[]>());
            }
            if(!tree.get(child.parent__r.grandparent__c).containskey(child.parent__c)) {
                tree.get(child.parent__r.grandparent__c).put(child.parent__c,new id[0]);
            }
            tree.get(child.parent__r.grandparent__c).get(child.parent__c).add(child.id);
            grandparents.put(child.parent__r.grandparent__c,null);
            parents.put(child.parent__r.id,null);
            children.put(child.id,null);
        }
        grandparents.putall([select id,name from grandparent__c where id in :grandparents.keyset()]);
        parents.putall([select id,name from parent__c where id in :parents.keyset()]);
        children.putall([select id,name from child__c where id in :children.keyset()]);
    }

}

Here is a sample output:

 

Grandparent: GP A

    Parent: P A

        Child: C A
    Parent: P B

        Child: C B
        Child: C B2

 

All Answers

sfdcfoxsfdcfox

SOQL inside loop. Bad design. You need to start from the other direction. Try this:

 

map<id,child__c> children = new map<id,child__c>();
map<id,parent__c> parents = new map<id,parent__c>();
map<id,grandparent__c> grandparents = new map<id,grandparent__c>();
map<id,map<id,id[]>> tree = new map<id,map<id,id[]>>();

for(child__c child:[SELECT Id,Name,Parent__c,Parent__r.GrandParent__c
                    FROM Child__c
                    WHERE Parent__r.GrandParent__c = :grandParentId]) {
    if(!tree.contains(child.Parent__r.GrandParent__c)) {
       tree.put(child.Parent__r.GrandParent__c, new map<id,list<id>>());
    }
    if(!tree.get(child.Parent__r.GrandParent__c).containsKey(child.Parent__c)) {
       tree.get(child.Parent__r.GrandParent__c).put(child.Parent__c,new Id[0]);
    }
    tree.get(child.Parent__r.GrandParent__c).get(child.Parent__c).add(child.Id);
    children.put(child.id,null);
    parents.put(child.parent__c,null);
    grandparents.put(child.parent__r.grandparent__c,null);
}
children.putAll([select id,name from child__c where id in :children.keyset()]);
parents.putAll([select id,name from parent__c where id in :parents.keyset()]);
grandparents.putAll([select id,name from grandparent__c where id in :grandparents.keyset()]);

All of this code lets you do the following:

 

<apex:repeat value="{!tree}" var="grandparentid">
    {!grandparents[grandparentid].name}
    <apex:repeat value="{!tree[grandparentid]}" value="parentid">
        {!parents[parentid].name}
        <apex:repeat value="{!tree[grandparentid][parentid]}" var="childid">
            {!children[childid].name}
        </apex:repeat>
    </apex:repeat>
</apex:repeat>

Basically, we create a named keyset of grandparents and their associated parents, which in turn has a named keyset of each parent and their children. The three maps are used to pull out additional information as you desire. This isn't necessary the "most efficient" method (you could do this in one really big query and just map the sobjects directly), but is probably marginally easier to read and understand.

 

Edit: Corrected some typos.

jordanmjordanm

Thank you sfdcfox, you are a true guru!

 

I have two questions as I try this.

 

1) c.Parent__c below should be child.Parent__c, correct?

if(!tree.get(child.Parent__r.GrandParent__c).containsKey(child.Parent__c)) {
       tree.get(child.Parent__r.GrandParent__c).put(c.Parent__c,new Id[0]);
}

 

2) This block returns:

"Save error: Method does not exist or incorrect signature: [MAP<Id,MAP<Id,LIST<Id>>>].put(Id)"

if(!tree.contains(child.Parent__r.GrandParent__c)) {
       tree.put(child.Parent__r.GrandParent__c);
}
thanks so much for your help, i've been stuck on this one for days and I finally feel like a solution makes logical sense
sfdcfoxsfdcfox

1) Right. I was just typing on the fly, but you spotted it. Good catch.

 

2) Should have ",new map<id,id>()" after the "__c" on the middle line.

 

I updated my prior reply with the suggested changes.

jordanmjordanm

Hm,

"Save error: Incompatible value type MAP<Id,Id> for MAP<Id,MAP<Id,LIST<Id>>>" with the new change here:

		    if(!tree.contains(child.Parent__r.GrandParent__c)) {
		       tree.put(child.Parent__r.GrandParent__c, new map<id,id>());
		    }

 

sfdcfoxsfdcfox

Sorry, should have been map<id,list<id>> instead of just map<id,id>. Try that (edited above).

jordanmjordanm

Maybe I'm declaring it incorrectly, but now I get this error:

"Save error: Method does not exist or incorrect signature: [MAP<Id,MAP<Id,LIST<Id>>>].contains(Id)"

 

Note: http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_methods_system_map.htm doesn't list "contains" as a method for maps, did you mean to put ContainsKey instead?

 

Assuming that is all the controller needs, my page is not displaying any of the data eventhough I am fetching the values in the controller.

 

I found many threads like this: http://boards.developerforce.com/t5/Visualforce-Development/Can-Map-collection-be-Apex-Repeat-tag-s-value/td-p/155442 saying that you can't directly display maps in repeat tags? Do I have to build a list?

sfdcfoxsfdcfox

That post is from 2009. Since then, they've made a couple of improvements. I just made a working mockup, which I will now copy directly here:

 

<apex:page standardController="Grandparent__c" extensions="treeview">
<form>
<pre>
    <apex:repeat value="{!tree}" var="grandparentid">
Grandparent: {!grandparents[grandparentid].name}<br/>
        <apex:repeat value="{!tree[grandparentid]}" var="parentid">
    Parent: {!parents[parentid].name}<br/>
            <apex:repeat value="{!tree[grandparentid][parentid]}" var="childid">
        Child: {!children[childid].name}
            </apex:repeat>
        </apex:repeat>
    </apex:repeat>
</pre>
</form>
</apex:page>
public with sharing class treeview {

    public map<id,map<id,id[]>> tree { get; set; }
    private grandparent__c grandparent;
    public map<id,grandparent__c> grandparents { get; set; }
    public map<id,parent__c> parents { get; set; }
    public map<id,child__c> children { get; set; }

    public treeview(ApexPages.StandardController controller) {
        Grandparent = (Grandparent__c)controller.getrecord();
        grandparents = new map<id,grandparent__c>();
        parents = new map<id,parent__c>();
        children = new map<id,child__c>();
        tree = new map<id,map<id,id[]>>();
        for(child__c child:[select id,name,parent__c,parent__r.grandparent__c from child__c where parent__r.grandparent__c = :grandparent.id]) {
            if(!tree.containskey(child.parent__r.grandparent__c)) {
                tree.put(child.parent__r.grandparent__c,new map<id,id[]>());
            }
            if(!tree.get(child.parent__r.grandparent__c).containskey(child.parent__c)) {
                tree.get(child.parent__r.grandparent__c).put(child.parent__c,new id[0]);
            }
            tree.get(child.parent__r.grandparent__c).get(child.parent__c).add(child.id);
            grandparents.put(child.parent__r.grandparent__c,null);
            parents.put(child.parent__r.id,null);
            children.put(child.id,null);
        }
        grandparents.putall([select id,name from grandparent__c where id in :grandparents.keyset()]);
        parents.putall([select id,name from parent__c where id in :parents.keyset()]);
        children.putall([select id,name from child__c where id in :children.keyset()]);
    }

}

Here is a sample output:

 

Grandparent: GP A

    Parent: P A

        Child: C A
    Parent: P B

        Child: C B
        Child: C B2

 

This was selected as the best answer
jordanmjordanm

Greatly appreciated!!! Neat trick with the pre formatted text tag, I hadn't seen that used before.

sfdcfoxsfdcfox

I was just too lazy to use real tags, so I faked it for appearances. I don't think I've ever seen "pre" used in any actual Visualforce page since it is trivially difficult to accurately control the output of whitespace in a Visualforce page (even my list is screwed up, as an example).

jordanmjordanm

It works wonderfully. You are my hero right now, sfdcfox

 

How would you most elegantly expand this case to cover a second type of children, say, foster children? 

Not every parent would necessarily have foster children, just as every parent wouldnt necessarily have regular children, so the parent map would have to be ALL the parents related to the grandparent instead and then only display children when they exist?

 

 This controller works. I'm sure there are ways to condense and clean this up but its pretty easy to understand this way. I'm filling in two trees for my two child types, incomes and contacts with the SAME parents and grandparent maps

 

The only maps that differ are the child maps

public with sharing class IntakeEXT {
	
	private Family__c family;
	public map<id,Participant_Income__c> incomes {get;set;}
	public map<id,Participant_Contact__c> contacts {get;set;}
	public map<id,Participant__c> participants {get;set;}
	public map<id,Family__c> families {get;set;}
    public map<id,map<id,id[]>> treeInc {get;set;}
    public map<id,map<id,id[]>> treeCon {get;set;}
	
	public IntakeEXT(ApexPages.StandardController controller){
		family = (Family__c)controller.getrecord();
		
		incomes = new map<id,Participant_Income__c>();
		contacts = new map<id,Participant_Contact__c>();
		participants = new map<id,Participant__c>();
		families = new map<id,Family__c>();
		treeInc = new map<id,map<id,id[]>>();
		treeCon = new map<id,map<id,id[]>>();
		
		for(Participant__c participant:[SELECT Id,Name,Family__c FROM Participant__c
							WHERE Family__c = :family.id]) {
			//populate parent and grandparent levels of Incomes tree					
			if(!treeInc.containsKey(participant.Family__c)) {
		       treeInc.put(participant.Family__c, new map<id,id[]>());
		    }
		    if(!treeInc.get(participant.Family__c).containsKey(participant.id)) {
		       treeInc.get(participant.Family__c).put(participant.id,new Id[0]);
		    }
		    //populate parent and grandparent levels of Contacts tree
		    if(!treeCon.containsKey(participant.Family__c)) {
		       treeCon.put(participant.Family__c, new map<id,id[]>());
		    }
		    if(!treeCon.get(participant.Family__c).containsKey(participant.id)) {
		       treeCon.get(participant.Family__c).put(participant.id,new Id[0]);
		    }
			families.put(participant.Family__c,null);
			participants.put(participant.id,null);
		}
		
		for(Participant_Income__c income:[SELECT Id,Name,Participant__c,Participant__r.Family__c
		                    FROM Participant_Income__c
		                    WHERE Participant__r.Family__c = :family.Id]) {
		    treeInc.get(income.Participant__r.Family__c).get(income.Participant__c).add(income.Id);
		    incomes.put(income.id,null);
		}
		
		for(Participant_Contact__c contact:[SELECT Id,Name,Participant__c,Participant__r.Family__c
		                    FROM Participant_Contact__c
		                    WHERE Participant__r.Family__c = :family.Id]) {
		    treeCon.get(contact.Participant__r.Family__c).get(contact.Participant__c).add(contact.Id);
		    contacts.put(contact.id,null);
		}
		
		incomes.putAll([select id,name,Income_Type__c,Income_Frequency__c,
					   Verified__c,Annual_Calculation__c
					   from Participant_Income__c where id in :incomes.keyset()]);
		
		contacts.putAll([select id,name,Contact_Type__c,Contact_Information__c
					   from Participant_Contact__c where id in :contacts.keyset()]);	
					   						   				   
		participants.putAll([select id,name,First_Name__c, Last_Name__c,
                			Middle_Name__c, Gender__c, Total_Income__c,
                			Social_Security_Number__c, SSN_Status__c,
                			Age__c, Disabled__c, Education__c,
                			Ethnicity__c, Food_Stamps__c,
                			Health_Insurance__c, Marital_Status__c,
                			Preferred_Contact__c, Primary_Language__c,
                			Race__c, Veteran_Code__c, WIC__c 
                			from Participant__c where id in :participants.keyset()]);
                			
		families.putAll([select id,name from Family__c where id in :families.keyset()]);		 
	}
}