I'm new to Apex development, and struggling to get SOQL to do what I need. I have a custom object SalesHistory that is a child of Account, and I need to filter the results of a SOQL query on Account to include only SalesHistory objects whose LastModifiedDate is greater than a custom datetime field on the Account object named TotalSales_LastRefreshTime__c.
Like so:
SELECT Id, (SELECT Id FROM SalesHistory__r WHERE LastModifiedDate > TotalSales_LastRefreshTime__c)
FROM Account
TotalSales_LastRefreshTime__c being the custom field on Account. This does not work however. It gives me a MALFORMED_QUERY with "unexpected token TotalSales_LastRefreshTime__c". I've also tried prefixing theTotalSales_LastRefreshTime__c with "Account__r." and "Account.", but it all gives me the same error back.
What am I missing here? Is it even possible to filter child subqueries based on comparisons with parent object fields? Or in more general terms, is it even possible to include fields from the outer query in the WHERE clause of a subquery?
I should also mention that breaking this up into several queries and storing intermediate results in collection variables is not an option for me, because the query is intended to be the basis of a Batch Apex job and will hence be returned as a Database.QueryLocator for execution by the platform.
I'm new to Apex development, and struggling to get SOQL to do what I need. I have a custom object SalesHistory that is a child of Account, and I need to filter the results of a SOQL query on Account to include only SalesHistory objects whose LastModifiedDate is greater than a custom datetime field on the Account object named TotalSales_LastRefreshTime__c.
Like so:
SELECT Id, (SELECT Id FROM SalesHistory__r WHERE LastModifiedDate > TotalSales_LastRefreshTime__c)
FROM Account
TotalSales_LastRefreshTime__c being the custom field on Account. This does not work however. It gives me a MALFORMED_QUERY with "unexpected token TotalSales_LastRefreshTime__c". I've also tried prefixing theTotalSales_LastRefreshTime__c with "Account__r." and "Account.", but it all gives me the same error back.
What am I missing here? Is it even possible to filter child subqueries based on comparisons with parent object fields? Or in more general terms, is it even possible to include fields from the outer query in the WHERE clause of a subquery?
I should also mention that breaking this up into several queries and storing intermediate results in collection variables is not an option for me, because the query is intended to be the basis of a Batch Apex job and will hence be returned as a Database.QueryLocator for execution by the platform.
