0

I'm trying to avoid too many SOQL queries error by writing 1 SOQL in the code to populate the no. of total reviews and no.of Open reviews on Safetyevent__C which is lookup on Root_cause_Analysis__C. I get error Cannot follow the same aggregate relationship twice: Root_Cause_Analysis__r when i write following query

List<Patient_Event__c> listSE = [Select id,name,No_of_Commitee_Event_Reviews__c ,(Select id from Root_Cause_Analysis__r),(Select id from Root_Cause_Analysis__r where Status__c ='New' OR Status__c ='Reopened' OR Status__c ='Investigation') from Patient_Event__c where Id in : setSEIds];

If i write 2 query, i end up getting system.LimitException: Too many SOQL queries. How do i avoid these error?

Trigger CommitteeEventReviewTrigger on Root_Cause_Analysis__c(After insert,After Delete,After Undelete)
{
    Set<Id> setSEIds = new Set<Id>();
    if(Trigger.isInsert || Trigger.isUndelete || Trigger.isUpdate){
        for(Root_Cause_Analysis__c CER : Trigger.new){
            setSEIds.add(CER.Patient_Safety_Incident__c);
        }
    }

    if(Trigger.isDelete){
        for(Root_Cause_Analysis__c CER : Trigger.old){
            setSEIds.add(CER.Patient_Safety_Incident__c);
        }
    }

    List<Patient_Event__c> listSE = [Select id,name,No_of_Commitee_Event_Reviews__c ,(Select id from Root_Cause_Analysis__r) from Patient_Event__c where Id in : setSEIds];
    for(Patient_Event__c SE :listSE){
        SE.No_of_Commitee_Event_Reviews__c = SE.Root_Cause_Analysis__r.size();
    }
    update listSE;

    List<Patient_Event__c> listSEStatusOpen = [Select id,name,No_of_Open_Committee_event_Review__c, (Select id from Root_Cause_Analysis__r where Status__c ='New' OR Status__c ='Reopened' OR Status__c ='Investigation') from Patient_Event__c where Id in : setSEIds];
    for(Patient_Event__c SEOpen :listSEStatusOpen){
        SEOpen.No_of_Open_Committee_event_Review__c = SEOpen.Root_Cause_Analysis__r.size();   
    }
    update listSEStatusOpen;
}

//Updated Code// This shows me same number on No_of_Open_Committee_event_Review__c and No_of_Commitee_Event_Reviews__c. How do i get No. of total No. of commitee event reviews and no. of open Reviews?

List<Patient_Event__c> listSE = [Select id,name,No_of_Commitee_Event_Reviews__c ,(Select id, status__c, Patient_Safety_Incident__c from Root_Cause_Analysis__r) from Patient_Event__c where Id in : setSEIds];
for(Patient_Event__c patientEvent: listSE){
List<Root_Cause_Analysis__c> rootCauses = new List<Root_Cause_Analysis__c>();
System.debug(rootCauses.size());
for(Root_Cause_Analysis__c rootCause: patientEvent.Root_Cause_Analysis__r){
    if((rootCause.Status__c=='New' || rootCause.Status__c=='Reopened' || rootCause.Status__c=='Investigation') && rootCause.Patient_Safety_Incident__c!=Null){
      patientEvent.No_of_Open_Committee_event_Review__c= patientEvent.Root_Cause_Analysis__r.size();
    }
}
update rootCauses;
 patientEvent.No_of_Commitee_Event_Reviews__c=patientEvent.Root_Cause_Analysis__r.size();
}
update listSE;
1
  • 1
    Hi Salmaan, Please update your code with proper formatting and indentation. Dec 5, 2020 at 6:44

1 Answer 1

0

In the following SOQL query (taken from your post), there are 2 sub queries for the same child object (Root_Cause_Analysis__r).

[Select id, name, No_of_Commitee_Event_Reviews__c , 
    (Select id from Root_Cause_Analysis__r), 
    (Select id 
     from Root_Cause_Analysis__r 
     where Status__c ='New' OR Status__c ='Reopened' OR Status__c ='Investigation') 
from Patient_Event__c 
where Id in : setSEIds]

In a parent-to-child relationship queries, you cannot specify the same child object in 2 different sub queries. Hence, the error.

Looks like you want to list down all the related child records as well as the ones that have a specific status. If my assumption is correct, in order to achieve this, you need to write the SOQL as given below and loop thru the result set to create required collections.

[Select id, name, No_of_Commitee_Event_Reviews__c , 
    (Select id, Status from Root_Cause_Analysis__r)
from Patient_Event__c 
where Id in : setSEIds]

You can loop thru the result set using the sample code shown below. Implement your logic (follow the best practices) to get the final update list collections.

List<Patient_Event__c> patientEvents = 
    [Select id, name, No_of_Commitee_Event_Reviews__c , 
         (Select id, Status from Root_Cause_Analysis__r)
     from Patient_Event__c 
     where Id in : setSEIds];
     
for(Patient_Event__c patientEvent: patientEvents){
    List<Root_Cause_Analysis__r> rootCauses = patientEvent.Root_Cause_Analysis__r;
    System.debug(rootCauses.size());
    for(Root_Cause_Analysis__r rootCause: rootCauses){
        // Perform your filtering logic here by checking the value of rootCause.Status 
        // and tracking the count to be stored against the parent record.
    }
}

Note: Too many SOQL queries is not because these 2 queries alone. There must be some other code getting executed in the same transaction leading to the limit exceeded exception. You might want to check out if there are other redundant triggers, additional process builders, flow or invocable apex code running along with this code.

6
  • Thanks, how do i filter required collections? Yes i want all related child records as well as Specific records based on status?
    – Saaki
    Dec 5, 2020 at 7:05
  • I have no idea about the real business requirement you are trying to solve, but I've given a sample code that you should be able to adapt for your requirement and explore further. You might want to check out apex developer guide or trailhead or other questions in this site regarding the best coding practices, bulkification etc.
    – arut
    Dec 5, 2020 at 7:23
  • I loop through the Result as described. It shows me error " Loop must iterate over collection: Patient_Event__c" Not sure i'm working correctly.
    – Saaki
    Dec 5, 2020 at 8:33
  • Unless you provide the updated code and where you are running into err, I might not be able to help. Try to run the piece of code in execute anonymous window and place some system.debug statements to troubleshoot further.
    – arut
    Dec 5, 2020 at 8:45
  • I updated the initial code at the bottom. It gives me same no. of total reviews and same no.of open reviews. How do i get no. of open reviews? Thanks!
    – Saaki
    Dec 5, 2020 at 19:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .