function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Jonathan A FoxJonathan A Fox 

Traversing SOQL on Trigger - Related objects with Junction Object

Hi all, 

I want to create a trigger which will update the name of a record:
 
trigger UpdateAssetWeek on Request__c (after update) {
    List<Asset_Weeks__c> a = new List<Asset_Weeks__c>();
    for (Request__c req : Trigger.new){
        if (req.Allocated_Week__c != NULL){
           Asset_Weeks__c asswk = new Asset_Weeks__c();
           asswk = [SELECT Id, Name FROM Asset_Weeks__c WHERE Week__c = 'Request__c.Allocated_Week__r.Asset_Week__c'];
           asswk.Name = asswk.Name + ' ' + req.Primary_Contact__c.Firstname + ' ' + req.Primary_Contact__c.Firstname;
           a.add(asswk);
        }
    }
    update a;
}

//Every time the related contact 'Primary Contact' is added to a Request:
//Change the name of the Asset_Weeks which is related to the Week__c and Asset__c of the Request

Every time the related contact 'Primary Contact' is added to a Request:
Change the name of the Asset_Weeks which is related to the Week__c and Asset__c of the Request

The schema fort a bit of conext is :

User-added image
Best Answer chosen by Jonathan A Fox
Jonathan A FoxJonathan A Fox
For anyone who may view this thread, here is my refined and corrected version:
 
public with sharing class RequestTriggerHandler {
    
    public static void handleAfterUpdate(List<Request__c> newRequests, Map<Id, Request__c> oldRequestMap)
    {
        // new list for the for loop
        List<Holiday_Booking__c> a = new List<Holiday_Booking__c>();
        //gather information required to query
        Set<Id> contactIds = new Set<Id>();
        Set<Id> weekIds = new Set<Id>();
        Set<Id> assetIds = new Set<Id>();
        //Create map for holding holidayBookingMap
        //Key = assetId+'-'+weekId
        //the map contains the SQOL at the bottom of this file
        Map<String, Holiday_Booking__c> holidayBookingMap;

        //For allocating a booking
        for (Request__c req : newRequests){
            if (req.Allocated_Week__c != null && 
                req.Allocated_Caravan__c != null){
                if(req.Primary_Contact__c != null)
                {
                    //add the primary contact to the Set for later use
                    contactIds.add(req.Primary_Contact__c);
                }
                //add the Allocated Week & Caravan to the Set for later use
                weekIds.add(req.Allocated_Week__c);
                assetIds.add(req.Allocated_Caravan__c);
            }
        }

        //Key = assetId+'-'+weekId
        holidayBookingMap = getHolidayBookingByKey(weekIds,assetIds);
        //Getting the contacts that are in the newRequests
        Map<Id, Contact> contactMap = new Map<Id, Contact>([SELECT Id, Firstname, Lastname FROM Contact WHERE Id IN :contactIds]);
        
        //for every new request 
        for (Request__c req : newRequests){
            //get the reqest ids that we are working on
            Request__c oldRequest = (Request__c)oldRequestMap.get(req.Id);
            //if the request has an allocated week, caravan and is not 'cancelled'
            if (req.Allocated_Week__c != null && 
                req.Allocated_Caravan__c != null &&
                req.Request_Status__c != 'Cancelled'){
                if(req.Primary_Contact__c != null)
                {
                    Contact con = ContactMap.get(req.Primary_Contact__c);
                    Holiday_Booking__c holidayBooking = holidayBookingMap.get(req.Allocated_Caravan__c+'-'+req.Allocated_Week__c);

                    holidayBooking.Name = holidayBooking.Week__r.Name + ' *** ' + con.Firstname + '' + con.Lastname + ' *** ';
                    holidayBooking.Status__c = 'Booked';
                    holidayBooking.Contact__c = req.Primary_Contact__c;
                    a.add(holidayBooking);
                }
            }
            //To prevent other changes firing the triger
            if (req.Request_Status__c == 'Cancelled' && oldRequest.Request_Status__c != 'Cancelled'){

                Holiday_Booking__c holidayBooking = holidayBookingMap.get(req.Allocated_Caravan__c+'-'+req.Allocated_Week__c);
                if (req.Primary_Contact__c == holidayBooking.Contact__c){

                    holidayBooking.Name = holidayBooking.Week__r.Name + ' Available';
                    holidayBooking.Status__c = 'Available';
                    holidayBooking.Contact__c = null;
                    a.add(holidayBooking);
                }
            }
        }
        
        update a;
    }


    private static Map<String, Holiday_Booking__c> getHolidayBookingByKey(Set<Id> weekIds, Set<Id> caravanIds)
    {
        Map<String,Holiday_Booking__c> recordsToReturn = new Map<String,Holiday_Booking__c>();
        //Key = assetId+'-'+weekId
        for(Holiday_Booking__c assetWeek : [SELECT 
                                            Id,
                                            Name, 
                                            Asset__c,
                                            Week__c,
                                            Week__r.Name,
                                            Status__c,
                                            Contact__c
                                        FROM 
                                            Holiday_Booking__c
                                        WHERE 
                                            Asset__c IN :caravanIds
                                        AND
                                            Week__c IN :weekIds])
        {
            String key = assetWeek.Asset__c+'-'+assetWeek.Week__c;
            recordsToReturn.put(key, assetWeek);
        }
        return recordsToReturn;
    }


}

Asset_Weeks__c is changed to Holiday_Booking__c for better UX.

All Answers

jprichterjprichter
Take a read through the Apex Best Practices (https://developer.salesforce.com/page/Apex_Code_Best_Practices). You'll want to avoid doing SOQL inside of a for loop, like you're doing on line 06 in your code snippet. To do that, consider creating a Map<String, Asset_Weeks__c>.
Jonathan A FoxJonathan A Fox
For anyone who may view this thread, here is my refined and corrected version:
 
public with sharing class RequestTriggerHandler {
    
    public static void handleAfterUpdate(List<Request__c> newRequests, Map<Id, Request__c> oldRequestMap)
    {
        // new list for the for loop
        List<Holiday_Booking__c> a = new List<Holiday_Booking__c>();
        //gather information required to query
        Set<Id> contactIds = new Set<Id>();
        Set<Id> weekIds = new Set<Id>();
        Set<Id> assetIds = new Set<Id>();
        //Create map for holding holidayBookingMap
        //Key = assetId+'-'+weekId
        //the map contains the SQOL at the bottom of this file
        Map<String, Holiday_Booking__c> holidayBookingMap;

        //For allocating a booking
        for (Request__c req : newRequests){
            if (req.Allocated_Week__c != null && 
                req.Allocated_Caravan__c != null){
                if(req.Primary_Contact__c != null)
                {
                    //add the primary contact to the Set for later use
                    contactIds.add(req.Primary_Contact__c);
                }
                //add the Allocated Week & Caravan to the Set for later use
                weekIds.add(req.Allocated_Week__c);
                assetIds.add(req.Allocated_Caravan__c);
            }
        }

        //Key = assetId+'-'+weekId
        holidayBookingMap = getHolidayBookingByKey(weekIds,assetIds);
        //Getting the contacts that are in the newRequests
        Map<Id, Contact> contactMap = new Map<Id, Contact>([SELECT Id, Firstname, Lastname FROM Contact WHERE Id IN :contactIds]);
        
        //for every new request 
        for (Request__c req : newRequests){
            //get the reqest ids that we are working on
            Request__c oldRequest = (Request__c)oldRequestMap.get(req.Id);
            //if the request has an allocated week, caravan and is not 'cancelled'
            if (req.Allocated_Week__c != null && 
                req.Allocated_Caravan__c != null &&
                req.Request_Status__c != 'Cancelled'){
                if(req.Primary_Contact__c != null)
                {
                    Contact con = ContactMap.get(req.Primary_Contact__c);
                    Holiday_Booking__c holidayBooking = holidayBookingMap.get(req.Allocated_Caravan__c+'-'+req.Allocated_Week__c);

                    holidayBooking.Name = holidayBooking.Week__r.Name + ' *** ' + con.Firstname + '' + con.Lastname + ' *** ';
                    holidayBooking.Status__c = 'Booked';
                    holidayBooking.Contact__c = req.Primary_Contact__c;
                    a.add(holidayBooking);
                }
            }
            //To prevent other changes firing the triger
            if (req.Request_Status__c == 'Cancelled' && oldRequest.Request_Status__c != 'Cancelled'){

                Holiday_Booking__c holidayBooking = holidayBookingMap.get(req.Allocated_Caravan__c+'-'+req.Allocated_Week__c);
                if (req.Primary_Contact__c == holidayBooking.Contact__c){

                    holidayBooking.Name = holidayBooking.Week__r.Name + ' Available';
                    holidayBooking.Status__c = 'Available';
                    holidayBooking.Contact__c = null;
                    a.add(holidayBooking);
                }
            }
        }
        
        update a;
    }


    private static Map<String, Holiday_Booking__c> getHolidayBookingByKey(Set<Id> weekIds, Set<Id> caravanIds)
    {
        Map<String,Holiday_Booking__c> recordsToReturn = new Map<String,Holiday_Booking__c>();
        //Key = assetId+'-'+weekId
        for(Holiday_Booking__c assetWeek : [SELECT 
                                            Id,
                                            Name, 
                                            Asset__c,
                                            Week__c,
                                            Week__r.Name,
                                            Status__c,
                                            Contact__c
                                        FROM 
                                            Holiday_Booking__c
                                        WHERE 
                                            Asset__c IN :caravanIds
                                        AND
                                            Week__c IN :weekIds])
        {
            String key = assetWeek.Asset__c+'-'+assetWeek.Week__c;
            recordsToReturn.put(key, assetWeek);
        }
        return recordsToReturn;
    }


}

Asset_Weeks__c is changed to Holiday_Booking__c for better UX.
This was selected as the best answer