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
Sunny NarulaSunny Narula 

Need to Reduce SOQL queries

I m having a senerio where it is need to make a SOQL querry inside the FOR loop, but is increasing number of SOQL hits..
I m trying to use MAP but enable produce the required code... requesting to please guide/help me out for the same..

thanks

 

below is the code snippet...

public list<cNodes> getmainnodes()
   hierarchy = new list<cNodes>();
   List<Territory2> tempparent = [SELECT  Id, Name, ParentTerritory2Id, ParentTerritory2.Name from Territory2 order by ParentTerritory2.Name, Name];
   for (Integer i =0; i< tempparent.size() ; i++)
     {
         List<UserTerritory2Association> tempchildren = [SELECT  Id, Territory2.Name, User.Name, UserId from UserTerritory2Association where Territory2Id= :tempparent[i].Id order by Territory2.Name];
         hierarchy.add(new cNodes(tempparent[i],tempchildren));
     }   
   return hierarchy;
}

Best Answer chosen by Sunny Narula
SKolakanSKolakan
@Sunny
You can use this pseudo code


public list<cNodes> getmainnodes(){
   hierarchy = new list<cNodes>();

   //get parentId, object map
   Map<Id,Territory2> tempparentMap = [SELECT  Id, Name, ParentTerritory2Id, ParentTerritory2.Name from Territory2 ];

//get all children list
   List<UserTerritory2Association> tempchildren = [SELECT  Id, Territory2Id,Territory2.Name, User.Name, UserId from UserTerritory2Association where Territory2Id=:tempparentMap.keyset()];

//Create parentId children list map
 Map<String,List<UserTerritory2Association>> parentIdChildrenMap = new Map<String,List<UserTerritory2Association>>();
        for(UserTerritory2Association child:tempchildren ){
            if(!parentIdChildrenMap.containsKey(child.Territory2Id)){
                List<UserTerritory2Association> newObjectList = new List<UserTerritory2Association>();
                  parentIdChildrenMap.put(child.Territory2Id, newObjectList );
                }
               
           List<UserTerritory2Association> objectList =  parentIdChildrenMap.get(child.Territory2Id);
            objectList.add(child);             
        }

//Create hierarchy list
for(Id parentId:tempparentMap.keyset()){
      hierarchy.add(new cNodes(tempparentMap.get(parentId),parentChildrenMap.get(parentId)));
}
   return hierarchy;
}


 

All Answers

Sunny NarulaSunny Narula

I tried using List Collection... as below is the code for that
but this is giving error as System.LimitException: Apex CPU time limit exceeded.

Requesting to please help me out with the best approcah for this...
thanks
 

List<Territory2> tempparent = [SELECT  Id, Name, ParentTerritory2Id, ParentTerritory2.Name from Territory2 order by ParentTerritory2.Name, Name];
List<UserTerritory2Association> potentialUsers = [SELECT  Id, Territory2Id, Territory2.Name, User.Name, UserId from UserTerritory2Association ];
for (Territory2 t : tempparent) {
    List<UserTerritory2Association> tUser = new List<UserTerritory2Association>();
    for (UserTerritory2Association u  : potentialUsers) {
        if (t.Id.equals(u.Territory2Id)) {         
              tUser.add(u);
        }
    hierarchy.add(new cNodes(t,tUser));
  }    
}

 

 
Nayana KNayana K
for(UserTerritory2Association objUTA : [SELECT  Id, Territory2Id, Territory2.Name, Territory2.ParentTerritory2Id, Territory2.ParentTerritory2.Name, User.Name, UserId from UserTerritory2Association ])
{
    hierarchy.add(new cNodes(new Territory2(Id = objUTA.Territory2Id, Name = objUTA.Territory2.Name,
                                            ParentTerritory2Id = objUTA.Territory2.ParentTerritory2Id,
                                            ParentTerritory2.Name = objUTA.Territory2.ParentTerritory2.Name),objUTA
                                ));
}

Just try whether this will make any difference
Sunny NarulaSunny Narula

@Nayana 

its giving an error as second parameter to cNodes() is a list collection that will hold all records for "UserTerritory2Association"  related to current "Territory2" in for loop... using the above code you metioned it giving error as :  Constructor not defined: [cNodes].<Constructor>(Territory2, UserTerritory2Association)

 
SKolakanSKolakan
@Sunny
You can use this pseudo code


public list<cNodes> getmainnodes(){
   hierarchy = new list<cNodes>();

   //get parentId, object map
   Map<Id,Territory2> tempparentMap = [SELECT  Id, Name, ParentTerritory2Id, ParentTerritory2.Name from Territory2 ];

//get all children list
   List<UserTerritory2Association> tempchildren = [SELECT  Id, Territory2Id,Territory2.Name, User.Name, UserId from UserTerritory2Association where Territory2Id=:tempparentMap.keyset()];

//Create parentId children list map
 Map<String,List<UserTerritory2Association>> parentIdChildrenMap = new Map<String,List<UserTerritory2Association>>();
        for(UserTerritory2Association child:tempchildren ){
            if(!parentIdChildrenMap.containsKey(child.Territory2Id)){
                List<UserTerritory2Association> newObjectList = new List<UserTerritory2Association>();
                  parentIdChildrenMap.put(child.Territory2Id, newObjectList );
                }
               
           List<UserTerritory2Association> objectList =  parentIdChildrenMap.get(child.Territory2Id);
            objectList.add(child);             
        }

//Create hierarchy list
for(Id parentId:tempparentMap.keyset()){
      hierarchy.add(new cNodes(tempparentMap.get(parentId),parentChildrenMap.get(parentId)));
}
   return hierarchy;
}


 
This was selected as the best answer
Sunny NarulaSunny Narula

thanks @Nayana K

thanks @Sreeni Kolakan