Compare OldValue and NewValue in a Batch Class

Question

I am trying to create a batch that send an Email anytime that status (Known_Error_Status__c) on a article is changed and the Article is 'Online'

I have enable field history tracking on the object (Knowledge__kav) which gives me access to the History object (KnowledgeArticleVersionHistory)

When querying the KnowledgeArticleVersionHistory with 'Query Editor' I am able to retrieve the data

But when trying to make an inner query, I get an error: Didn't understand relationship 'Histories' in FROM part of query call.

List<Knowledge__kav> kavList = [SELECT KnowledgeArticleId, 
       (SELECT NewValue, Oldvalue FROM Histories WHERE FieldName = 'Knowledge__kav.Known_Error_Status__c') FROM Knowledge__kav];

Is there a way to query oldValue and newValue in a Batch? So that an email can be sent via the batch anytime the Article has been published and the status has been updated Thanks

here is the batch

global class BatchArticleEmail implements Schedulable, Database.Batchable<SObject> {

global string query;

// To schedule batch 
global void execute(SchedulableContext sc) {
    Id batchProcessId = Database.executeBatch(this);
}

// Constructor to query articles
global BatchArticleEmail() {
    query = 'Select Id, KnowledgeArticleId, Known_Error_Status__c FROM Knowledge__kav WHERE PublishStatus=\'Online\'';
}

global Database.QueryLocator start(Database.BatchableContext BC){
     return Database.getQueryLocator(Query);
}

global void execute(Database.BatchableContext BC, List<Knowledge__kav> scope) {
    System.debug('#### Scope BatchArticleEmail ' + scope.size());
    
    //Check if Status has been updated
    Set<Id> kavIds = new Set<Id>();
    List<Knowledge__kav> kavList = [SELECT KnowledgeArticleId, 
           (SELECT NewValue, Oldvalue FROM Histories WHERE FieldName = 'Knowledge__kav.Known_Error_Status__c') FROM Knowledge__kav];
    
    for(Knowledge__kav kv : kavList) {
        kavIds.add(kv.KnowledgeArticleId);
    }
    System.debug('BatchArticleEmail kavList KnowledgeArticleId ' +kavIds);
    
    Map<Id, Known_Error_Subscription__c> kesMap = new Map<Id, Known_Error_Subscription__c>(
        [SELECT KnowledgeKA__c FROM Known_Error_Subscription__c WHERE KnowledgeKA__c IN :kavIds]);
    System.debug('BatchArticleEmail Known_Error_Subscription__c Map ' +kesMap);

    for (Knowledge__kav kav : scope) {
        for(Known_Error_Subscription__c kes : kesMap.Values()) {
            kes.Last_Known_Error_Status__c = kav.Known_Error_Status__c;
            System.debug('***Last_Known_Error_Status__c ' + kes.Last_Known_Error_Status__c);
        }
        EmailManager.sendEmailToKnownErrorSubscribers(scope);   
    }
    update kesMap.values();   
}

global void finish(Database.BatchableContext info) {
    
}

}

Answer 1

If you look at the schema, you can see that KnowledgeArticleVersionHistory is not related to the Knowledge__kav. Instead it is related (via ParentId lookup field) to the Knowledge__ka object (which is the parent of all Knowledge Article versions.

Answer 2

Do you have a reason why you want to use a batch to send emails when the articles are published and their status changes? Why a batch and not a Process Builder or a Record-triggered Flow or an Apex Trigger? If you are very sure you want to send your emails from a batch, then maybe you can combine these mechanisms, e.g. use a Record triggered Flow to detect a change in status and publish status and set a checkbox field on the Article and then use the batch to send an email for every Article that has the checkbox field set (don't forget to reset the checkbox after sending the email then though). It is easy to detect a change in a value in Process Builder (see ISCHANGED), Record triggered Flow (with Record Prior values) and Apex Triggers (via data in Trigger.new/Trigger.old).