+ Start a Discussion
huskerwendyhuskerwendy 

Too many SOQL queries: 101 - No queries in For loop

I modified an existing trigger on assets to also update the account team if a custom field on the asset has a value of 1. The trigger works as expected when I test it in the Sandbox however, it fails with too many SOQL queries when I execute the Test Class. The line it's failing on isn't inside of a for loop and should only be returning one record.  Can someone please explain what I've done wrong?

 

The trigger is failing on this line:

        // get the assignment group id for the VersaDoc Project Managers group   
        list<Assignment_Group_Name__c> asgnGroupNameIDs = [SELECT Id FROM Assignment_Group_Name__c WHERE Name = 'Support - VersaDoc Project Managers' limit 1];

Here's my trigger:

trigger trgAsset on Asset (after insert, after update, after delete) {
	if (trigger.isInsert || trigger.isUpdate){
		/*Trigger will update the Client_Since__c field on the account with the first purchase date for all assets.
		  If the Asset has VersaDoc and the account doesn't have a VersaDoc Project Account Team Member, 
		  it will assign the next VersaDoc Project manager from the assignment groups to the Account Team.*/
		// create a set of all the unique accountIds
		Set<id> accountIds= new Set<id>();
		Set<id> accountTeamAccountIds = new Set<id>(); // set of accountIDs that have VersaDoc in the asset
	    for (Asset a : Trigger.new){
	    	accountIds.add(a.AccountId); 
	    	if (a.HasVersadoc__c == 1 || a.HasVersadocStudio__c == 1 ) {
        		accountTeamAccountIds.add(a.AccountId); 
       	 	} 	
	    }
		   
		// create map of accountids and minimum purchase date
		map<ID, Date> acctSinceDate = new map<ID, Date>();	
	    AggregateResult[] groupedResults = [Select AccountId, MIN(PurchaseDate) MinPurchaseDate From Asset where AccountID in :accountIds GROUP BY AccountId];
		for (AggregateResult ar : groupedResults)  {
		    acctSinceDate.put(String.valueof(ar.get('AccountId')), Date.valueof(ar.get('MinPurchaseDate')) );
		}   	
		// create a map of accounts to update
		map<String, Account> accts = new map<String, Account>([Select Id, Client_Since__c from Account Where Id in :acctSinceDate.keySet()]);
		// update Account ClientSince field with MinimumPurchaseDate value
		//iterate over the list of accounts and assign the ClientSince date
		for (Account acct : accts.values()){
			acct.Client_Since__c = acctSinceDate.get(acct.Id);
		} 
		 if (accts.size()>0)
	        update(accts.values());
	        
		// get the assignment group id for the VersaDoc Project Managers group   
		list<Assignment_Group_Name__c> asgnGroupNameIDs = [SELECT Id FROM Assignment_Group_Name__c WHERE Name = 'Support - VersaDoc Project Managers' limit 1];
		
		Assignment_Groups__c[] asgnGroups = new List<Assignment_Groups__c>([SELECT Group_Name__c, User__c, Last_Assignment__c, Millisecond__c 
	                                   FROM Assignment_Groups__c 
	                                   WHERE Group_Name__c in :asgnGroupNameIds
	                                   AND Active__c = 'True' AND User_Active__c = 'True'
	                                   ORDER BY Last_Assignment__c, Millisecond__c] );                                    		
		Integer groupCount = asgnGroups.size();		
	    if (asgnGroups.isEmpty()) return;      

		//loop through list of accounts and get the accounts w/o VersaDoc Project Manager
		AccountTeamMember[] NewMembers = new AccountTeamMember[]{};  //list of new team members to add
	    AccountShare[] newShare = new AccountShare[]{};  //list of new shares to add
		Map<id, Account> acctsToUpdate = new Map<id, Account>([Select a.Id, (Select Id, AccountId From AccountTeamMembers WHERE TeamMemberRole = 'VersaDoc Project Manager' limit 1) From Account a Where a.Id in :accountTeamAccountIds]);
		Integer cnt = 0;
	    for (Account a : acctsToUpdate.values()){
	    	if (a.AccountTeamMembers == null || a.AccountTeamMembers.isEmpty()) {
	    		AccountTeamMember TeamMemberAd=new AccountTeamMember();
		        TeamMemberAd.AccountId=a.id;
		        TeamMemberAd.UserId=asgnGroups[cnt].User__c;
		       	TeamMemberAd.TeamMemberRole = 'VersaDoc Project Manager';
		        NewMembers.add(TeamMemberAd);	
		        datetime now = datetime.now();
		        asgnGroups.get(cnt).Last_Assignment__c=now;
		        asgnGroups.get(cnt).Millisecond__c = now.millisecondGMT(); 
	    		cnt ++;
	    		if (cnt == groupCount){ cnt = 0;}    	
	    	}
	    } 

		//insert any valid members then add their share entry if they were successfully added
	    Database.SaveResult[] lsr = Database.insert(NewMembers,false);
		Integer newcnt=0;
	    for(Database.SaveResult sr:lsr){
			if(!sr.isSuccess()){
				Database.Error emsg = sr.getErrors()[0];
				system.debug('\n\nERROR ADDING TEAM MEMBER:'+emsg);
			}else{
				newShare.add(new AccountShare(UserOrGroupId=NewMembers[newcnt].UserId, AccountId=NewMembers[newcnt].Accountid, AccountAccessLevel='Edit',OpportunityAccessLevel='Edit'));
			}
				newcnt++;			
	    }
	    //insert the new shares
	    Database.SaveResult[] lsr0 =Database.insert(newShare,false); 
	    Integer newcnt0=0;
	    for(Database.SaveResult sr0:lsr0){
			if(!sr0.isSuccess()){
				Database.Error emsg0=sr0.getErrors()[0];
			    system.debug('\n\nERROR ADDING SHARING:'+newShare[newcnt0]+'::'+emsg0);
			}
				newcnt0++;
	    } 
	    // update assignment groups with their LastAssignmentDate
	    update asgnGroups; 	                   	                     
	} else if (trigger.isDelete){
		// create a set of all the unique accountIds
		Set<id> accountIds = new Set<id>();
	    for (Asset a : Trigger.old)
	    	accountIds.add(a.AccountId); 	
		   
		// create map of accountids and minimum purchase date
		map<ID, Date> acctSinceDate = new map<ID, Date>();
		
	    AggregateResult[] groupedResults = [Select AccountId, MIN(PurchaseDate) MinPurchaseDate From Asset where AccountID in :accountIds GROUP BY AccountId];
		for (AggregateResult ar : groupedResults)  {
		    acctSinceDate.put(String.valueof(ar.get('AccountId')), Date.valueof(ar.get('MinPurchaseDate')) );
		} 
		// create a map of accounts to update 
		map<string, Account> accts = new map <string, Account>([Select Id, Client_Since__c from Account Where Id in :accountIds]);
		for (Account acct : accts.values()){
			// if there's a value in the acctSinceDate map, update the Client_Since__c date to that date, otherwise set to null.
			if(acctSinceDate.ContainsKey(acct.Id)){
				acct.Client_Since__c = acctSinceDate.get(acct.Id); 
			}else {
				acct.Client_Since__c = null;
			} 
		}
		update(accts.values());
	}
}

 Here's my test class:

 

@isTest
private class testTrgAsset {
	static testMethod void myUnitTest() {
		// create user to run the test as
	    Profile p = [select Id from profile where name = 'Standard User'];
	    UserRole r = [select Id from userrole where name = 'Sales Assistant'];
	       
	    User u = new User(lastname='testing', alias='test123', email='testing123@noemail.com',
	    	username='testing123@noemail.com', profileid = p.Id, userroleid = r.id,
	       	emailencodingkey='utf-8', Office_Code__c='US0000', LanguageLocaleKey='en_US',
	       	localesidkey='en_US', timezonesidkey='America/Los_Angeles');
	    insert u;	
	    
	    System.runAs(u) {  
		   	// Switch to the runtime 
		   	Test.StartTest(); 
		   		performCreateAssignmentGroups(u.Id, p.Id, r.id);
		       	performCreateAccounts(u.Id);   		   		
				performCreateAssets(u.Id);  
				performCreateAdditionalAssets(u.Id);
				performUpdateOriginalPurchaseDate(u.Id);
				performDeleteAssets(u.Id);
				performCreateVersaDocAssets(u.Id);	
		  	Test.StopTest();   
	     }
	}
	
	public static void performCreateAssignmentGroups(string createdby, string profileid, string userrole){
		integer i=0;
		List<User> users = new list<User>();
		for (i=0; i<3; i++){
			User u = new User(
			lastname = 'testing'+i,
			alias='test'+i,
			email='testing'+i+'@noemail.com',
			username='testing'+i+'@noemail.com',
			emailencodingkey='utf-8',
			Office_Code__c = 'US000',
			profileid = profileid,
			userroleid = userrole,
			LanguageLocaleKey='en_US',
			localesidkey='en_US', 
			timezonesidkey='America/Los_Angeles',
			IsActive=true);
			users.add(u);
		}
		insert users;
		
		//create an assignment group name
		Assignment_Group_Name__c agn = new Assignment_Group_Name__c(Name = 'Support - VersaDoc Project Managers');
		insert agn;
		
		Assignment_Groups__c[] NewMembers = new Assignment_Groups__c[]{};  //list of new assignmentgroup members to add
			
		for (User u : users){
			datetime now = datetime.now(); 
			Assignment_Groups__c a = new Assignment_Groups__c(
				Group_Name__c=agn.Id,
				User__c = u.Id,
				Active__c = 'true',
				Last_Assignment__c= now);
			NewMembers.add(a);
		}
		insert NewMembers;			
	}
	
	public static void performCreateAccounts(string createdby){
		list<Account> accts = new list<Account>();	
		User u = [select Id from User where Id =: createdby];
		integer i = 0;
	    // create 200 accounts
	    for (i=0; i < 200; i++ ){
	    	Account acc = new Account(
	    		Name='Testing Account ' +i, 
	    		Industry='Printers', 
	    		Type = 'Client',
	    		CreatedBy = u);
	    	accts.add(acc);
	    }	
	    insert accts;
	}

	public static void performCreateAssets(string createdby){
		list<Asset> assts = new list<Asset>();
		list<Account> accts = new list<Account>([Select id from Account where CreatedById =: createdby]);
		Product2 prod = [select Id from Product2 where Name = 'PrinterPresence Silver Level'];
		date now = date.today();
	   
	    // create an asset for each account
		for (Account acct : accts ){
			Asset a = new Asset(
	   			Product2 = prod,
	   			Name = 'PrinterPresence Silver Level',
	   			AccountID = acct.Id,
	   			Status = 'Purchased',
	   			PurchaseDate = now,
	   			RMR__c = 160.00, 
	   			Price = 2000.00);
	   		assts.add(a);	 
		}
	    insert assts;
	    
	      //check results of accounts to be sure ClientSince date was updated to the PurchaseDate
		 list <Account> accs = [Select Id, Client_Since__c from Account Where CreatedById =: createdby];
		 for (Account a : accs){
		 	System.assertEquals(now, a.Client_Since__c);   
		 }  
	}
	
	public static void performCreateAdditionalAssets(string createdby){
		list<Asset> assts = new list<Asset>();
		list<Account> accts = new list<Account>([Select id from Account where CreatedById =: createdby]);		
	 	list <Asset> insertedAsst = [Select Id from Asset where CreatedbyId =: createdby];		
		Product2 prod = [select Id from Product2 where Name = 'PrinterPresence Gold Level'];
		date now = date.today();
		
		//update previous asset to Attritioned-Upgrade
		for (Asset ass : insertedAsst){
			ass.Status = 'Attritioned-Upgrade';
			ass.UsageEndDate = System.today();
		}		
		update insertedAsst;
			   
	    // create an asset for each account
		for (Account acct : accts ){
			Asset a = new Asset(
	   			Product2 = prod,
	   			Name = 'PrinterPresence Gold Level',
	   			AccountID = acct.Id,
	   			Status = 'Purchased-Upgrade',
	   			PurchaseDate = now,
	   			RMR__c = 210.00, 
	   			Price = 500.00);
	   		assts.add(a);	 
		}
	    insert assts;
	    
	      //check results of accounts to be sure ClientSince date was updated to the PurchaseDate
		 list <Account> accs = [Select Id, Client_Since__c from Account Where CreatedById =: createdby];
		 for (Account a : accs){
		 	System.assertEquals(now, a.Client_Since__c);   
		 }  
	}
	
	public static void performUpdateOriginalPurchaseDate(string createdby){
		date now = date.today();
		// get a list of the original assets	
	 	list <Asset> insertedAsst = [Select Id from Asset where CreatedbyId =: createdby and Status = 'Attritioned-Upgrade'];
	 			
		//update original asset purchase date
		for (Asset ass : insertedAsst){
			ass.PurchaseDate = now;
		}		
		update insertedAsst;
			   
	     //check results of accounts to be sure ClientSince date was updated to the new PurchaseDate
		 list <Account> accs = [Select Id, Client_Since__c from Account Where CreatedById =: createdby];
		 for (Account a : accs){
		 	System.assertEquals(now, a.Client_Since__c);   
		 }  
	}
	
	public static void performDeleteAssets(string createdby){
		list<Account> accts = new list<Account>();	
		User u = [select Id from User where Id =: createdby];
		integer i = 0;
	    // create 100 accounts
	    for (i=0; i < 100; i++ ){
	    	Account acc = new Account(
	    		Name='Testing Account ' +i, 
	    		Industry='Nonprofit', 
	    		Type = 'Client',
	    		CreatedBy = u);
	    	accts.add(acc);
	    }	
	    insert accts;
	    
	    // insert one asset for each of the new accounts
	    list<Asset> assts = new list<Asset>();
		list<Account> insertedAccts = new list<Account>([Select id from Account where CreatedById =: createdby and Industry='Nonprofit']);
		Product2 prod = [select Id from Product2 where Name = 'Nonprofit Silver Level'];
	   	Product2 prod2 = [select Id from Product2 where Name = 'PrinterPresence Silver Level'];
	   
	    // create an asset for each account
		for (Account acct : accts ){
			Asset a = new Asset(
	   			Product2 = prod,
	   			Name = 'Nonprofit Silver Level',
	   			AccountID = acct.Id,
	   			Status = 'Purchased',
	   			PurchaseDate = date.newinstance(2012, 01, 01),
	   			RMR__c = 110.00, 
	   			Price = 3000.00);
	   		assts.add(a);	 
		}
	    insert assts;
	    
	    // delete assets for all accounts
	    list <Asset> AssetToDelete = [Select Id from Asset where CreatedbyId =: createdby and (Name = 'Nonprofit Silver Level' or name = 'PrinterPresence Silver Level')];
	    
		delete AssetToDelete;
		
		//check results of accounts to be sure ClientSince date was updated to empty for the newly created accounts
		 list <Account> accs = [Select Id, Client_Since__c from Account Where CreatedById =: createdby and Industry='Nonprofit'];
		 for (Account a : accs){
		 	System.assertEquals(Null, a.Client_Since__c);   
		 } 
		//check results of accounts to be sure ClientSince date was updated to the new PurchaseDate
		 list <Account> accs1 = [Select Id, Client_Since__c from Account Where CreatedById =: createdby and Industry='Printers'];
		 for (Account a1 : accs1){
		 	System.assertEquals(System.today(), a1.Client_Since__c);   
		 }  
	}
	
	public static void performCreateVersaDocAssets(string createdby){
	    // add PrinterPresence Platinum level Asset (Has VersaDoc)
	    date now = date.today(); 
	    list<Account> accts = new list<Account>([Select id from Account where CreatedById =: createdby]);	
	    Product2 product = [Select p.Name, p.Id From Product2 p where p.name = 'PrinterPresence Platinum Level'];
	    list<Asset> assets = new list<Asset>();
	   
	    for(Account a : accts){
	    	Asset ass = new Asset(
	    		Name=product.Name,
	    		Product2Id=product.Id,
	    		Status='Purchased',
	    		Price=7500.00,
	    		RMR__c=310.00,
	    		AccountId=a.Id,
	    		PurchaseDate=now);
	    	assets.add(ass);
	    }
	    insert assets;
	    
	    //check results of inserted accounts to be sure a VersaDoc Project Manager was assigned	
		list <Account> accs = [Select Id, AccountNumber from Account Where CreatedById =: createdby and Type = 'Client' and Name like 'Testing Account%'];
		
		Map<id, Account> acctTeams = new map<id, Account>([Select a.Id, (Select Id, AccountId From AccountTeamMembers WHERE TeamMemberRole = 'VersaDoc Project Manager' limit 1) From Account a Where a.Id in :accts]);
		for (Account a : acctTeams.values()){
			System.assertNotEquals(a.AccountTeamMembers, Null);
		}  
	}
}

 Thanks!

Yoganand GadekarYoganand Gadekar

Could be bacuase of sub Query In following statement. Not Quite sure but for every record sub Query could be considered as fired. You can actually debug this.

 

Map<id, Account> acctsToUpdate = new Map<id, Account>([Select a.Id, (Select Id, AccountId From AccountTeamMembers WHERE TeamMemberRole = 'VersaDoc Project Manager' limit 1) From Account a Where a.Id in :accountTeamAccountIds]);

 

 

mark this as answer if this helps you so that this can help others too.

 

Thanks,

Yoganand

amitashtekaramitashtekar

 

As you are updating account, have you verified that any trigger written on account object.

 

It will also consider those queries.

huskerwendyhuskerwendy

How can I debug this to see where the queries are being generated? I do have a trigger on the accounts as well. How do you prevent the too many SOQL queries in general. I know you shouldn't put queries inside of for loops and I haven't done that. What other solutions are there?

 

Thanks for the help.

RocketRocket

You can try to deactivate another trigger on the account object and then run this trigger to see what happens.

amitashtekaramitashtekar

Let me know if you are able to find it out or not. Every account update will also call trigger on the account object.

Try inactivating triggers on account object.

 

if still your code is crossing governor limits in that case just check out the developer console to find out which method is getting called.

huskerwendyhuskerwendy

Thanks for the responses. I've put this project on hold for the week and will try again next week.