Apex Code Development You need to sign in to do that
Don't have an account?
Decorum How to compare across relationships is SOQL queries?
Hi all!
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.
Thankful for any assistance!
BR,
Daniel Stolt
Looks like you are trying to perform a Right Inner Join. Try something like:
select id from Account where SalesHistory__r.LastModiedDate > TotalSales_LastRefreshTime__c
You can find more examples here: http://wiki.developerforce.com/index.php/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com
Hi Quinton,
If I try to run the exact query you suggested, it gives me the same error message:
MALFORMED_QUERY
ERROR at row:1:column:62
Unexpected token: 'TotalSales_LastRefreshTime__c'
The field name is correct, because this query works:
SELECT TotalSales_LastRefreshTime__c FROM Account
I am running the queries inside the salesforce.schema window in the Force.com IDE - could that affect things?
BR,
Daniel Stolt
as per my current understanding , I don't think so you can write such query. You can only use the variable or the contact values in comparison.
SELECT TotalSales_LastRefreshTime__c FROM Account
:This query is correct , b'coz you ar eonly fetching the data nothing else.
select id from Account where SalesHistory__r.LastModiedDate > TotalSales_LastRefreshTime__c
: But in case of this "SalesHistory__r.LastModiedDate > TotalSales_LastRefreshTime__c" this not seem as a valid clause.because both are the objects field , how would the salesforce know what is the value of "TotalSales_LastRefreshTime__c".
Even you can write the same query using the variable or fixed valus like :
[select id from Account where SalesHistory__r.LastModiedDate > Date.today()];
for your current question you need to get all the child for a records and need to put a manual check id Parent.Date > child.date.
Well, the platform should be able to know the value of "TotalSales_LastRefreshTime__c" because this is in the context of a subquery, and the parent Account object has a specific (and known) value for this field. This is perfectly possible in normal SQL, so why not in SOQL? Making a field comparison across a relationship boundary when performing in inner JOIN seems like a very fundamental and trivial thing that's very often necessary for filtering the child rows based on some data in the parent row.
Same problem here. Not even something like this works:
very frustrating ...
You can not do this, but there is an alternate:
Create a formula field on Contact having condition :
If(Account.LastModifiedDate > LastModifiedDate, True, false)
Now Query will be like this:
[Select Id from Contact where My_Formula_Field__c = 'True'];
Hope this make sense.