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
Salesforce BlitzSalesforce Blitz 

move nested SOQL out of for loop

Public void mymethod(set<id> subord1,map<id,user> usersMap)
    {
for(id subid :subord1)
           {
             Id roleId=usersMap.get(subid).userRoleId; 
             user u1=usersMap.get(subid);
              List<user> users;
             
             if(roleId!=null)
             {
             users = [select Id from User where UserRoleId IN (select Id from UserRole where ParentRoleId = :roleId)];
             }
}
}

I want to move the SOQL out of for loop---Or change it into single SOQL.


Thanks in adv.
Laxman 
Marek Kosar_Marek Kosar_
Hello,
if I understand correctly, this could be solution to your problem (without nested soql): 
Public void mymethod(set<id> subord1,map<id,user> usersMap){

	//first you create list of Ids of all relevant user Roles
	Set<Id> allRolesIds = new Set<Id>();
	for(User u : usersMap.values()){
		allRolesIds.add(u.userRoleId);
	}
	
	//then list of Ids of all relevant user roles, where parent role is from previous list
	List<UserRole> parentUserRoles = [select Id from UserRole where ParentRoleId IN: allRolesIds];
	List<Id> parentUserRoleIds =  new List<Id>();
	for(UserRole ur : parentUserRoles){
		parentUserRoleIds.add(ur.Id);
	}
	
	//then you need list of all relevant users
	List<User> allRelevantUsers = [select Id, UserRoleId from User where UserRoleId IN: parentUserRoleIds];
	
	Map<Id,List<User>> roleIdToListOfUsersWithThatRole = new Map<Id,List<User>>();
	for(User u:allRelevantUsers){
		if( roleIdToListOfUsersWithThatRole.get(u.UserRoleId) == null )
			roleIdToListOfUsersWithThatRole.put(u.UserRoleId, new List<User>() );

		roleIdToListOfUsersWithThatRole.get(u.UserRoleId).add(u);
	}
	
	//get list of all User with some userrole:
	List<User> finalList = roleIdToListOfUsersWithThatRole.get(u.UserRoleId);
}

 
Sumit Kumar Singh 9Sumit Kumar Singh 9
Hello Laxman, 

You can try something like -  
Public void mymethod(set<id> subord1,map<id,user> usersMap) {
set<id> parentRoleIDs = new set<id>();
List<user> users = new List<user>();
for(id subid :subord1) {
             Id roleId=usersMap.get(subid).userRoleId;      
             if(roleId!=null) {
                    parentRoleIDs.add(roleId);
             }
}
users = [select Id from User where UserRoleId IN (select Id from UserRole where ParentRoleId IN : parentRoleIDs)];
}

Hope, it will help you.

Thanks, 
Sumit Kumar Singh
 
Salesforce BlitzSalesforce Blitz
HI Sumit:
The following is my actual method:


Public void mymethod(set<id> subord1,map<id,user> usersMap1)
    {
           List<id> unediteduseridlist1 = new List<id>();
           for(id subid :subord1)
           {
             Id roleId=usersMap.get(subid).userRoleId; 
             user u1=usersMap.get(subid);
                         
            List<user> users = [select Id from User where UserRoleId IN (select Id from UserRole where ParentRoleId = :roleId)];
            system.debug('Number of Subordinate Users :' + users.size());
            Set<Id> resultIds = (new Map<Id,SObject>(users)).keySet();
            system.debug('size of set-------------'+resultIds.size());
            List<user> lst_TeamLead1 = new List<user>();
            
            If(resultIds .size()>0 && resultIds !=NULL)
            {
                for(Id Id2 : resultIds ){
                    
                     user uu1=usersMap.get(id2);
                
                    If((uu1.Last_Edited_Object_Date__c < d || uu1.Last_Edited_Object_Date__c==NULL)  && (uu1.Last_Activity_Date__c < d ||uu1.Last_Activity_Date__c==NULL)) 
                    {
                        unediteduseridlist1.add(Id2);
                        lst_TeamLead1.add(u1);
                    }
                    
                    
                 }
            If(unediteduseridlist1.size()>0 && unediteduseridlist1 !=NULL)  
            sendmail(unediteduseridlist1,lst_TeamLead1);
            unediteduseridlist1.clear();
            }
    }
    }

I am not ablr to use the u1 which i need to pass to other method in this case..

This method takes subord1 and checks if the users have users below them in role hierarchy and then verified if they modified any records in last n days.  If not modifeid they will be added to list and sent to sendmail method which sends mail to owners(u1).

 
Temoc MunozTemoc Munoz
public void mymethod(set<id> subord1,map<id,user> usersMap1)
{
     List<id> unediteduseridlist1 = new List<id>();     
     List<user> lst_TeamLead1 = new List<user>();
       
     Set<Id> roleIds = new Set<Id>();
     for(id subid :subord1)
     {
          User usr = userMap1.get(subId);
          if(usr != null)
          {
             roleIds.add(usr.userRoleId);
          }
     }

     Map<Id, User> users = new Map([select Id, ParentRoleId from User where UserRoleId IN (select Id from UserRole where ParentRoleId in :roleIds]);
 
     for(User topUser: userMap1.values())
     {
        for(User usr : users.values())
        {
           if(usr.ParentRoleId == topUser.Id)
           {
              User uu1 = userMap1.get(usr.Id);
              if(uu1 != null)
              {
                 if(uu1.Last_edited_Object_Date__c < d ....) 
                 { 
                   unediteduseridlist1.add(uu1.Id); 
                   ist_TeamLead1.add(topUser); 
                 }
              }
           }
        }
     }
 }
Salesforce BlitzSalesforce Blitz
Hi  Temoc Munoz,

Getting an error saying there is no field named ParentRoleId on user object.


Map<Id, User> users = new Map([select Id, ParentRoleId from User where UserRoleId IN (select Id from UserRole where ParentRoleId in :roleIds]);

Thanks.
Laxman



 
Temoc MunozTemoc Munoz
Hi Laxman.

I just wanted to give you an idea of what you need. And you're correct, ParentRoleId does not exist for User, so you will need to somehow create that relationship. I suggest you take a cleaner approach using recursion:

http://salesforce.stackexchange.com/questions/924/is-there-a-way-to-query-role-hierarchy

Once you get that code snippet in place, you can associate your Parent Roles with their respective suboordinates:
  
List<id> unediteduseridlist1 = new List<id>(); 
List<user> lst_TeamLead1 = new List<user>(); 

Set<Id> roleIds = new Set<Id>(); 
for(id subid :subord1) 
{ 
   User usr = userMap1.get(subId); 
   if(usr != null) { roleIds.add(usr.userRoleId); } 
}

// from example in URL
Set<Id> allSubRoleIds = getAllSubRoleIds(new Set<ID>{roleIds});

for(Id subordinateId : allSubRoleIds)
{
    for(User usr : [select Id from User where UserRoleId IN (select Id from UserRole where ParentRoleId = :roleId)])
    {
         if(usr.Last_Edited__Object_Date__c ......)
         {
              unediteduseridlist1.add(usr.id);
              lst_TeamLead1.add(usr);
         }
    }
}

What do you think?