Issue while looping through child records

Question

I have a requirement to check for Role_Name__c on Account_Role__c object which is a child of Policy__c object. Now, if any child records have Original or Duplicate as value in Role_Name__c, a new Alert__c record should be created.

Below is my apex class:

public with sharing class Alertforpolicy {
    public static void UpdateFields() {
        List<Alert__c> CustAlertList = new List<Alert__c>();
        List<Policy__c> pollist = [SELECT id, Close_Date__c FROM Policy__c WHERE Close_Date__c = LAST_N_DAYS:30];
        for(Policy__c pol: pollist){
            for (Account_Role__c child : pol.Account_Roles__r)
            {
                if(child.Role_Name__c.contains('Duplicate') || child.Role_Name__c.contains('Original')){
                    Alert__c custalert = new Alert__c(
                        Contact__c = child.Contact__c,
                        Status__c = 'Active'
                    CustAlertList.add(custalert);
                }
            }
        }
        insert CustAlertList;    
    }
}

Now, this isn't working as expected. If there are 3 child account role records with given role names to a parent Policy__c record, 3 alert records should be created. That isn't happening here. No alert record is created.

Can anyone please suggest changes to the above code so that I can get this done.

Thanks!

Answer 1

If you don't query something, you don't get it from the database. You need to change your query:

    list<policy__c> pollist = [
        SELECT Close_Date__c,
        (SELECT Role_Name__c, Contact__c FROM Account_Roles__r)
        FROM policy__c 
        WHERE Close_Date__c = LAST_N_DAYS:30
    ];