confused while querying junction object

Question

I am working on a batch and am writing a query but am getting confused because of the relationship... The batch is written on a junction object, Account_BOB__c, between 2 parents Account & Book_Of_Business__c. When an Account_BOB__c is created, a field on Account is updated, based upon the fields on Book_Of_Business. I'm currently working through the logic of execute method but I have confused myself. Here is my code thus far:

global class Batch implements Database.Batchable<sObject>, Database.Stateful, Schedulable {
global String query = 'SELECT Id, Account__c, Book_of_Business__c, Status__c, Book_of_Business__r.Level__c, Book_of_Business__r.Relationship_Owner__c FROM Account_BOB__c ' +
   'WHERE Status__c = Active' +
   'AND Effective_Date__c = TODAY';

public Database.QueryLocator start(Database.BatchableContext BC) {       
    return Database.getQueryLocator(query);
}

public void execute(Database.BatchableContext BC, List<Account_BOB__c> scope) { 
    Set<Id> accBOBIdSet = new Set<Id>();
    Map<Id, Account> accByAccBOBIdMap = new Map<Id, Account>();
    
     for(Account_BOB__c accBOB : scope) { 
         accBOBIdSet.add(accBOB.Id);
    }
    
    for(Book_of_Business__c bob : [SELECT Id FROM Book_of_Business__c WHERE Account_BOB__c IN :accBOBIdSet])

The error is coming on the last line. Because there is no Account_BOB__c on Book_Of_Business__c.

I am trying to get the Book_Of_Business fields associated with each Account BOB in my scope. But not sure how to do that in my WHERE because this object being on the child side of the relationship.

I'm sorry if I explained this incorrectly, but does anyone have any ideas?

Thanks!

Answer 1

It looks like you are over complicating the steps involved.

This example of the execute method iterates over the items in scope and builds Account records to be updated using data found on the related (and queried for) Book_of_Business__c records.

public void execute(Database.BatchableContext BC, List<Account_BOB__c> scope) {
    
    // these are the accounts which we're going to populate data on
    Map<Id, Account> accountsToUpdate = new Map<Id, Account>();
            
    for (Account_BOB__c accBOB : scope) {
        // assuming you have queried for the data
        // so that it exists on the accBOB record in
        // this for loop

        // sobject for the account record which will be updated            
        Account acct;

        // check to see if this account has already been added to the map in this scope
        if (accountsToUpdate.containsKey(accBOB.Account__c)) {
            // get the sobject from the map 
            acct = accountsToUpdate.get(accBOB.Account__c);
        } else {
            // create a new one
            acct = new Account(Id = accBOB.Account__c);
        }
        
        // populate your fields with concrete references
        // acct.your_field_name__c = accBOB.Book_of_Business__r.Relationship_Owner__c;
        
        // or dynamic references by string API name
        // acct.put('your_field_name__c') = accBOB.Book_of_Business__r.Relationship_Owner__c;

        // put the account record into the map for updating
        accountsToUpdate.put(acct.Id, acct);
    }

    update accountsToUpdate.values(); // get the list of Account from the map and update
}