Deleting records from custom object based on Standard object

Question

Need help in identifying the records that needs to be deleted in a custom object: Opportunity_LGT

Background: Opportunity_LGT is a custom object that was created to store few fields of Opportunity standard object for different purposes. When standard Opportunity is created/updated , trigger will create/update Opportunity_LGT. Opportunity_LGT has lookup to Account object and it holds ID of the standard Opportunity record as text field – Opportunity_id_txt.

We recently implemented the delete logic, when opportunity is deleted , corresponding Opportunity_LGT must get deleted.

What is required: I need to delete Opportunity_LGT records which doesn’t have corresponding standard Opportunity record. Oppportunity standard object has 55,000 records and Opportunity_LGT has 55,283 records. I need to delete additional 283 records in Opportunity_LGT records.

Issue:

I have written below code to get those 283 Opportunity_LGT records in an CSV file and send email to me.. I can delete those records via DATALOADER.

When I execute the code on "Open execute anonymous window".. It give error messages – "System.LimitException: Too many query rows: 50001". Because I have more than 50,000 records.. I have spend lot of time on looking at other options.. Looking for a direction here and different approaches.

public class TempDeleteOpplgtwithnoOpp {
Public void getalltodeleterecords()
{

    //*Map<ID, Opportunity> Oppmap = new map<id, Opportunity>([SELECT ID, NAME FROM Opportunity LIMIT 10]);
    Set<id> oppidlist = new Set<id>();

    for(Opportunity opp : [SELECT ID FROM Opportunity])
    {
        oppidlist.add(opp.id);   
    }

   string OpplgtDelNoOppStringFinal = 

    'ID'+','+'Name'+','+'CreatedDate'+','+'LastModifiedDate'+','+'Opportunity_Lgt_Name'+','+' Opportunity_id_txt __c'+'\n';


       List<id> opplgttoDelIdSet = new List<ID>();

       for(Opportunity_LGT__C opplgt : [SELECT ID, Name, CreatedDate, LastModifiedDate, Account_Code__c,
                  Opportunity_Lgt_Name__c, Opportunity_id_txt __c FROM Opportunity_LGT__C] )
       {
           IF (!Oppidlist.contains(opplgt.id)) 
           {                  
               opplgttoDelIdSet.add(opplgt.id);
               String OpplgtDelNoOppString = opplgt.id +','+opplgt.name+','+opplgt.CreatedDate+','+opplgt.LastModifiedDate+','+opplgt.Opportunity_Lgt_Name__c+','+opplgt.Opportunity_Record_Id__c +'\n';
            OpplgtDelNoOppStringFinal = OpplgtDelNoOppStringFinal+ OpplgtDelNoOppString ;   
           }
       }

        system.debug('Opplgtlist.size: ' + Oppidlist.size()); 
        system.debug('opplgttoDelIdSet.size:' + opplgttoDelIdSet.size());

           Messaging.EmailFileAttachment DelOLNoOpp = new Messaging.EmailFileAttachment();
    Blob DelOLNoOppcsvBlob = Blob.valueof(OpplgtDelNoOppStringFinal);
            String DelOLNoOppcsvname = 'DelOLNoOppOL.csv';
            DelOLNoOpp.setFileName(DelOLNoOppcsvname);
            DelOLNoOpp.setBody(DelOLNoOppcsvBlob);


            Messaging.singleEmailMessage ademail = new Messaging.singleEmailMessage();
            List<String> toAddress = new List<String>{'raghavendra.shettigar@inventivHealth.com'};
            ademail.setSubject('OppLightDeleteFiles');
            ademail.setToAddresses(toAddress);
            ademail.setPlainTextBody('Opportunity Light Delete');
            ademail.setFileAttachments( new messaging.emailFileAttachment[]{DelOLNoOpp });
            Messaging.SendEmailResult[] r = Messaging.sendEmail(new Messaging.singleEmailMessage[]{ademail});

Answer 1

As you said - (The exception is being thrown because the maximum number of rows you can return in SOQL calls in Apex is 50000).

I think you should write all of your this (finding your 283 records) logic/code into "Batch Apex Class" and call this batch class from here (in your getalltodeleterecords() method).

In addition your code should respect system limits. Use Limits Class to access limits dynamically in apex.

Answer 2

Your first mistake was not using a lookup field to link it to the opportunity.

You best bet:

1. Create a checkbox on the LGT record called Matched Opportunity
2. Write a batch that will cycle through the opportunities
3. In the batch query for LGT records than have an matching Opportunity ID in the txt field (Since you are operating on opportunity the IDs will be available)
4. Mark the checkbox on those LGT records
5. Update the LGT Records

Then once it is run you should be left with the 283 records that are not matched.

Then you can use dev console to simple delete them.

Answer 3

You may like to filter your query on the Opportunity_LGT object .Since you have no relationship directly to opportunity and you have relationship only with Account ,you may first collect set of AccountIds from the opportunity if its a trigger context .

set<Id> accIds=new set<Id>();
for(Opportunity opp:[Select AccountId from Opportunity where ID in :setoppIds]){
 accIds.add(opp.AccountId);
}

//Filter your opportunity_LGT__c with this set .