query opportunities whose field has been changed in the past 24 hours

Question

I'm trying to fetch opportunity records whose field(StageName) has been changed in the past 24 hours and whose child record(Say="Some X OBject", it's a masterdetail relationship) has field called 'status'=approved?

How can I fetch these records in the start method of a batch class?

I've tried:

List<Id> OppIds= new List<Id>(); 
for (Invoice__C Inv:[select id,name from Invoice__C where status__C='Approved']) {
    OppIds.add(Inv.Opportunity__c); 
} 

DateTime Past24 = system.now().AddDays(-1); 
return Database.getQueryLocator([select id from Opportunity where LastModifiedDate > :Past24 and Sent_to_ShareDo__c=false and Id IN :OppIds ]);

Answer 1

You need to first create a rollup summary field to count how many detail records have the requisite status. There is ample documentation out there about how to set that up, so you should be able to figure out that step.

Let's assume you call this rollup summary field Approved_Child_Count__c. Then you should be able to do something like:

public Database.QueryLocator start(Database.BatchableContext context)
{
    return [
        SELECT ... FROM Opportunity
        WHERE Approved_Child_Count__c > 0
        AND Id IN (
            SELECT OpportunityId FROM OpportunityFieldHistory
            WHERE Field = 'Stage' AND CreatedDate >= YESTERDAY
        )
    ];
}

It's not exactly the last 24 hours, but you can tweak the date literal in your Left Inner Join as needed.