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
OnDem DevOnDem Dev 

Too many SOQL queries : 101

Hi,

 

I have a Visualforce page and a controller class.

Using "With Sharing" keyword, i am able to retrieve the records from the Custom object just as a Standard Salesforce functionality does.

Now my requirement is to retrieve all the Subordinate Roles for logged in User that should be displayed in drop-down.

There are more than 400 Roles in my org.

My Role stands 5th or 6th from the top.

 

Even if i use SOQL For loop to get all the Subordinate Roles, i am running in to Too many SOQL queries : 101.

Below is my code.

 

public method1()

{ 

         //get Subordinate Roles for the CurrentRole

        for(UserRole[] roleList : [Select id, name, parentroleid from UserRole ur Where ur.ParentRoleId :UserInfo.getUserRoleId() order by name])

        {
            for(UserRole role: roleList)
            {
                currentUserSubDetails.add(role);//Add to list
            }

         }

        

        //Retrieve Subordinate roles for each Role

        for(Integer i=0; i<currentUserSubDetails.size(); i++)
        {
            getRolesAtDeeperLevel(currentUserSubDetails[i]);
        }


 }

 

   private void getRolesAtDeeperLevel(UserRole ur)
    {
        for(UserRole[] roleList : [select id, name, ParentRoleId from UserRole where ParentRoleId = :ur.id order by name])
            for(UserRole role: roleList)
            {
                currentUserSubDetails.add(role);
            }
    }

Can anyone suggest me a solution?

 

Regards OnDem

Message Edited by OnDem Dev on 07-09-2009 10:28 AM
Best Answer chosen by Admin (Salesforce Developers) 
BritishBoyinDCBritishBoyinDC
You're calling the getRolesAtDeeperLevel function each time you loop, so it is executing the SOQL each time - put the first query into a map, and then execute a single query for the Subordinate roles using the .keyset() method of the map - then you can loop through the second list and add them to the list...

All Answers

BritishBoyinDCBritishBoyinDC
You're calling the getRolesAtDeeperLevel function each time you loop, so it is executing the SOQL each time - put the first query into a map, and then execute a single query for the Subordinate roles using the .keyset() method of the map - then you can loop through the second list and add them to the list...
This was selected as the best answer
BibhuBibhu

}

 Hi I have following trigger and test class. However when I try to migrate it to production I get an error saying Too many SOQL Queries:101. I would request any one of you to spare your kind time and help me out , what mistake I am doing here. Being a beginer I am not able to judge what could be the mistake. Any help would really be appreciated.

trigger update_baxter_only_for_uk on Account (after insert,after update)
 {
    String locale_user=Userinfo.getLocale();
    if(locale_user=='en_GB')
    {
        for( Account a :Trigger.new)
            {
            
            List <Contact> cons=[select BXUK_Baxter_Only__c from contact where accountid in :Trigger.new];
                   
                   Integer i=cons.size();
                   i=i-1;
                   System.debug('Debuggggglentgh'+i);
                   if(i>=0)
                 {
                   While(i>=0)
                   {
                    cons[i].BXUK_Baxter_Only__c=a.BXUK_Baxter_Only__c;
                    i=i-1;
                    
                   }
                     update cons;
                 }    
            }
 }

 

@IsTest private class cls_test_Update_Baxter_Only_Triggers_Classes

{ 

@IsTest Public static void test_baxter_only_for_uk() 

  {   

List<Contact> l=[select BXUK_Baxter_Only__C,accountid,id from Contact where accountid !=Null limit 1]; 

List<Account> act=[select BXUK_Baxter_Only__C from Account where id=:l[0].accountid]; 

act[0].BXUK_Baxter_Only__C=TRUE; 

update act; 

}
BritishBoyinDCBritishBoyinDC

Your problem is that you are querying for contacts in the Account Loop

 

You should be able do something like this though I think

 

 

List<Contact> cons = [Select BXUK_Baxter_Only__C, Account.BXUK_Baxter_Only__C from Contact where AccountId in : Trigger.new];

for (contact c: cons) {
c.BXUK_Baxter_Only__C = c.Account.BXUK_Baxter_Only__C;
}

update cons;

Also, in your test class, you are assuming a record will exist you can use to test with. I would create the test data as part of the test class to avoid any issues in deployment. 
lancewlancew

I'm having the same error.  I'm doing 2 queries for each updated record.

 

trigger UpdateReferal on Lead (after update) {
    List<Lead_Referral__c> referalsToUpdate = new List<Lead_Referral__c>{};

    for (Lead tempLead: Trigger.new){
        User o = [select name,Phone,Email from user where id=:tempLead.OwnerId];

 

        List<Lead_Referral__c> reflist=[
            Select lr.Lead_Description__c, lr.Lead_owner_Name__c,

            lr.Lead_Owner_Phone__c, lr.Lead_Owner_Email__c,            

            lr.ProductsAndCounts__c
            from Lead_Referral__c lr
            where lr.Lead__c = :tempLead.Id ];

        for(Lead_Referral__c leadRef:reflist ){
            leadRef.Lead_Description__c = tempLead.Description;
            leadRef.Lead_owner_Name__c = o.Name;
            leadRef.Lead_Owner_Phone__c = o.Phone;
            leadRef.Lead_Owner_Email__c = o.Email;
            leadRef.ProductsAndCounts__c = tempLead.ProductsAndCounts__c;

            referalsToUpdate.add(leadRef);
        }
    }
    update referalsToUpdate;
}

 

 
eugene_bmeugene_bm

Don't do query within a loop c:
lancewlancew

I get updated objects in the list Trigger.new.  How do I not loop through them?