After update Trigger to avoid hitting governor limits

Question

We have a custom object(Request__c) with lookup to PersonAccount(AssigneeAccount__c) and Account(BusinessAccount__c) similar to AccountContactRelation(ACR) standard object. There is a requirement where, on After Update of Request__c record, I need to query and check if there is any ACR record having same PersonAccount and Account lookup as that of Request__c. If there is any matching existing ACR found, I need to update that ACR with a custom field RoleName__c from Request__c object. If there is no matching ACR found, then I need to insert ACR with the PersonAccount and Account lookup as that of Request__c.

I tried it but as the 2 objects do not have direct parent-child relationship I am ending up in querying in for loop which may hit governor limits. Is there any possible way to avoid this please. The following is the code:

trigger RequestTrigger on Request__c (after update) 
{
    if(Trigger.isAfter && Trigger.isUpdate)
    {
        List<AccountContactRelation> acrListToInsert = new List<AccountContactRelation>();
        List<AccountContactRelation> acrListToUpdate = new List<AccountContactRelation>();
        

        List<Request__c> requests = [Select Id, BusinessAccount__c , Status__c, RoleName__c, AssigneeAccount__c,AssigneeAccount__r.PersonContactId from Request__c Where AssigneeAccount__c!=null AND BusinessAccount__c!=null AND Status__c='Active' AND Id IN : Trigger.new];  
        for(Request__c request : requests)
        {
            List<AccountContactRelation> accountContactRelations = [ SELECT Id, AccountId, ContactId, Roles
                                                                     FROM AccountContactRelation WHERE
                                                                     Contact.Id =:request.AssigneeAccount__r.PersonContactId
                                                                     AND AccountId = :request.BusinessAccount__c ];
            if (accountContactRelations.isEmpty()) 
            {  
                //Create new ACR
                AccountContactRelation acr = new AccountContactRelation();
                acr.AccountId = request.BusinessAccount__c;
                acr.ContactId = request.AssigneeAccount__r.PersonContactId;
                acr.Roles = request.RoleName__c;
                acr.IsActive = true;
                acr.StartDate = Date.today();
                acrListToInsert.add(acr);                  
            }
            else
            {
                //Update ACR
                for( AccountContactRelation acrRecord :  accountContactRelations )
                {
                    String existingRoles = acrRecord.Roles;
                    List<String> existingRolesList = existingRoles.split(';');
                    existingRolesList.add(request.RoleName__c);
                    String newRoles = String.join(existingRolesList, ';');
                    acrRecord.Roles = newRoles;
                    acrListToUpdate.add(acrRecord);
                }
            }
        }
        if(acrListToInsert.size()>0)
        {
            Database.insert(acrListToInsert);
        }
        if(acrListToUpdate.size()>0)
        {
            Database.update(acrListToUpdate);
        }
    }
}

Answer 1

I would suggest create two Lists for requestBusinessAccounts and for requestsAssigneeAccountPersonContactIds .
Also use IN in query and add one more "for" loop So it would look like this :

trigger RequestTrigger on Request__c (after update) {
if(Trigger.isAfter && Trigger.isUpdate)
{
    List<AccountContactRelation> acrListToInsert = new List<AccountContactRelation>();
    List<AccountContactRelation> acrListToUpdate = new List<AccountContactRelation>();
    
    List<String> requestBusinessAccounts  = new List<String>();
    List<String> requestsAssigneeAccountPersonContactIds  = new List<String>();
    
    List<Request__c> requests = [Select Id, BusinessAccount__c , Status__c, RoleName__c, AssigneeAccount__c,AssigneeAccount__r.PersonContactId from Request__c Where AssigneeAccount__c!=null AND BusinessAccount__c!=null AND Status__c='Active' AND Id IN : Trigger.new];  
    for(Request__c request : requests) {
        requestBusinessAccounts.add(request.BusinessAccount__c);
        requestsAssigneeAccountPersonContactIds.add(request.AssigneeAccount__r.PersonContactId);
    }
    
    List<AccountContactRelation> accountContactRelations = [ SELECT Id, AccountId, ContactId, Roles
                                                                 FROM AccountContactRelation WHERE
                                                                 ContactId IN : requestsAssigneeAccountPersonContactIds
                                                                 AND AccountId IN :requestBusinessAccounts ];
    
    
    
    for(Request__c request : requests) {
       Boolean accountContactRelationExists = false;
       for(AccountContactRelation accountContactRela : accountContactRelations)  { 
            if (request.BusinessAccount__c == accountContactRela.AccountId && request.AssigneeAccount__r.PersonContactId == accountContactRela.ContactId) 
            {  
                //Update ACR
                String existingRoles = accountContactRela.Roles;
                List<String> existingRolesList = existingRoles.split(';');
                existingRolesList.add(request.RoleName__c);
                String newRoles = String.join(existingRolesList, ';');
                accountContactRela.Roles = newRoles;
                acrListToUpdate.add(accountContactRela);
                accountContactRelationExists = true;              
            }
       }
        if(!accountContactRelationExists) {
            //Create new ACR
            AccountContactRelation acr = new AccountContactRelation();
            acr.AccountId = request.BusinessAccount__c;
            acr.ContactId = request.AssigneeAccount__r.PersonContactId;
            acr.Roles = request.RoleName__c;
            acr.IsActive = true;
            acr.StartDate = Date.today();
            acrListToInsert.add(acr);   
        }
    }
    if(acrListToInsert.size()>0)
    {
        Database.insert(acrListToInsert);
    }
    if(acrListToUpdate.size()>0)
    {
        Database.update(acrListToUpdate);
    }
  }
}