0

I am running into the infamous System.LimitException: Too many SOQL queries: 201 error, but I am really not sure why. In this class I am attempting to get a JSON object from a URL and then use it to create new contacts in our system. In my class I need to continue to call makeHttpCall until next_url is returned null to get the entire JSON object. Once that is done I want to call processData to insert the new contacts.

I am attempting to query a list from a map and am hitting the limit when I do so.

Line 102 - Contact[] conList = [SELECT ID, Email, Zenefits_Id__c FROM Contact WHERE Zenefits_Id__c IN : newEmployeeMap.keySet() LIMIT 200];

But I am also receiving the error from...

line line 12 where I call processData();

Here is my code for reference. I have removed the initial URL as well as the Bearer token for security purposes.

public class zenefitsEmployeeSync implements Schedulable, Database.AllowsCallouts {
  public void execute(SchedulableContext SC) {
      makeCallout();
  }
  
  @future(callout = true)
  public static void makeCallout() {
      string url = 'xxxxxxxxxxxxxxxxxxx';
      do {
          url = makeHttpCall(url);
      } while(url != null);
    processData();
  }
  
  static list<object> newEmployees = new list<object>();
  static map<String,Id> dataAttributes = new Map<string,Id>();
  static List<Object> account = new List<Object>();
  static {
      for (account a : [SELECT Id, Name FROM Account WHERE Name Like 'LLC%' LIMIT 1]) {
          dataAttributes.put('accountId',a.Id);
      }
  }
  
  static List<Object> Calendar = new List<Object>();
  static {
      for (pse__Work_Calendar__c workCal : [SELECT name, ID FROM pse__Work_Calendar__c WHERE Name Like 'US Full-Time 8x5%' LIMIT 1]) {
          dataAttributes.put('workcalId',workCal.Id);
      }
  }
  
      public static String makeHttpCall(String url) {
      string nextUrl;
      Http http = new Http();
      HttpRequest request = new HttpRequest();
      request.setHeader('Authorization', 'Bearer xxxxxxxxxx');
      request.setEndpoint(url);
      request.setMethod('GET');
      HttpResponse response = http.send(request);
      // If the request is successful, parse the JSON response.
      if (response.getStatusCode() == 200) { 
          // Deserializes the JSON string into collections of posts.
          Map<String, Object> wrapper = (Map<String, Object>) JSON.deserializeUntyped(response.getBody());
          if (wrapper.containsKey('data')) {
              Map<String, Object> wrapper2 = (Map<String, Object>) wrapper.get('data');
              if (wrapper2.containsKey('data')) {
                  nextUrl = (String) wrapper2.get('next_url');
                  newEmployees.addAll((List<Object>)wrapper2.get('data'));
                  system.debug('New Employees Sze ' + newEmployees.size());
              }
          }
      }
      return nextUrl;
  }
  
  public static void processData() {
      List<contact> employeesToInsert = new List<contact> ();
      Map<String,map<String, Object>> employeeMap = new Map<String,map<String, Object>>();
      
      for (Object LevvelEmployee : newEmployees) {
          Map<string,object> employees = (Map<string,object>)LevvelEmployee ;
          Map<String,map<String, Object>> newEmployeeMap  = new Map<String,map<String, Object>>(); 
          Map<String, Object> managerWrapper = (Map<String, Object>) Employees.get('manager');
          Map<String, Object> DepartmentWrapper = (Map<String, Object>) Employees.get('department');
          Map<String,Object> empValues = new Map<String,Object>();
                          
          if( (Employees.get('status') == 'active')&&( managerWrapper.get('url') != NULL)){

          String ZenefitsLastName = (String) Employees.get('last_name');
          empValues.put('LastName',Employees.get('last_name'));
          String ZenefitsFirstName = (String) Employees.get('first_name');
          empValues.put('FirstName',Employees.get('first_name'));
          String ZenefitseEmployeeId = (String) Employees.get('id');
          empValues.put('ZenefitseEmployeeId',Employees.get('id'));
          String ZenefitsEmail = (String) Employees.get('work_email');
          empValues.put('ZenefitsEmail',Employees.get('work_email'));
          String Status = (String) Employees.get('status');
          empValues.put('Status',Employees.get('status'));
          String title = (String) Employees.get('title');
          empValues.put('title',Employees.get('title'));
          String phoneNumber = (string) employees.get('work_phone');
          empValues.put('phoneNumber',Employees.get('work_phone'));
          string managerUrl = (string) managerWrapper.get('url');
          empValues.put('managerUrl',managerWrapper.get('url')); 
          system.debug('managerURL ' + managerWrapper.get('url')); 
          string managerId = managerUrl.substring(managerUrl.length() - 7, managerUrl.length() - 0);
          empValues.put('managerId',managerId);
          string alias = ZenefitsFirstName.substring(0,1) + ZenefitsLastName.substring(0,3);
          system.debug('alias ' + alias);
          empValues.put('alias', alias);
                          
          string department = (string) departmentWrapper.get('url');
          empValues.put('department',departmentWrapper.get('url'));
                        
          String departmentURL = department.substring(department.length() - 6, department.length() - 0);
          empValues.put('departmentURL',departmentURL);
          system.debug('department url ' + departmentURL);
                          
          newEmployeeMap.put(ZenefitseEmployeeId, empValues);
          system.debug('newEmployeeMap ' + newEmployeeMap);
                          
          //now query the list of contacts in the system so we can remove them and only insert new Employees
          Contact[] conList = [SELECT ID, Email, Zenefits_Id__c FROM Contact WHERE Zenefits_Id__c IN : newEmployeeMap.keySet() LIMIT 200];
          system.debug('conList ' + conList);
          for(Contact con1 : conList) 
          {
            //remove the Employees that already exist in the system
              newEmployeeMap.remove(con1.Zenefits_Id__c); //remove existing contacts
              system.debug('newEmployeeMap ' + newEmployeeMap);
          }
                  
          for(String zId: newEmployeeMap.keyset()) 
          {
            map<String,Object> currMap = newEmployeeMap.get(zId);
              system.debug('currMap' + currMap); 

              //create a contact record for the new resource
              Contact con = new Contact();
              con.Zenefits_Id__c = (string)currMap.get('ZenefitseEmployeeId');
              con.Lastname  = (string)currMap.get('LastName');
              con.FirstName = (string)currMap.get('FirstName');
              con.Email = (string)currMap.get('ZenefitsEmail');
              con.Title = (string)currMap.get('title');
              con.Manager_Zenefits_ID__c = (string)currMap.get('managerId');
              con.Phone = (string)currMap.get('phoneNumber');
              con.pse__Start_Date__c = system.today();
              con.pse__Is_Resource__c = true; 
              con.pse__Is_Resource_Active__c = true;
              con.AccountId = (String)dataAttributes.get('accountId');
              con.pse__Work_Calendar__c = (String)dataAttributes.get('workcalId');
              con.pse__Level__c = 'Consultant';
              con.pse__Resource_Role__c = 'Analyst';
              con.recordTypeId = '0121N0000012es9QAA';
              con.zenefits_department_id__c = departmentURL;
              con.alias__c = (String)currmap.get('alias');

              employeesToInsert.add(con);
              system.debug('contacts to update ' + employeesToInsert.size());
              }      
          }
      upsert employeesToInsert;
      }
  }
}
1
  • You have SOQL in for loop Contact[] conList = [SELECT ID, Email, Zenefits_Id__c FROM Contact WHERE Zenefits_Id__c IN : newEmployeeMap.keySet() LIMIT 200];.May be your newEmployees is more. You can query this on top and use it inside. Dec 4, 2020 at 16:40

1 Answer 1

2

You have SOQL in for loop.

Contact[] conList = [SELECT ID, Email, Zenefits_Id__c FROM Contact WHERE Zenefits_Id__c IN : newEmployeeMap.keySet() LIMIT 200];

May be your newEmployees is more. You can query this on top and use it inside.

As you are using this IN : newEmployeeMap.keySet() as your filter criteria and that can be done outside loop.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.