SOQL: Comparing two custom relationship fields in a nested query

Question

I think I may be hitting some sort of SOQL limitation in the WHERE clause. I am trying to return fields from two parent objects, after comparing two date fields on both Objects.

The goal is to have a list of contacts with associated opportunity fields where the opportunity date field is equal to some contact field.

NOTE: We have a custom junction between Opportunity and Contact named "Opportunity_Contact__c" due to the limits on the standard OCR sObj.

Specifically:

SELECT NAME, Con_Custom_Date__c,
  (SELECT Opportunity__r.Name FROM Opportunity_Contacts__r 
   WHERE Contact__r.Con_Custom_Date__c = Opportunity__r.Opp_Custom_Date__c)
FROM Contact

This returns a parsing error. However, if I try switching the query to something like:

SELECT NAME, Con_Custom_Date__c,
  (SELECT Opportunity__r.Name FROM Opportunity_Contacts__r 
   WHERE Contact__r.Con_Custom_Date__c = TODAY and Opportunity__r.Opp_Custom_Date__c = TODAY)
FROM Contact

This works. So the date fields are working and I am able to use them in the WHERE clause. However, when I compare one field directly to the other, I get a parsing error..

Why would

WHERE Contact__r.Con_Custom_Date__c = TODAY and Opportunity__r.Opp_Custom_Date__c = TODAY

work while

WHERE Contact__r.Con_Custom_Date__c = Opportunity__r.Opp_Custom_Date__c

triggers a parsing error?

Thanks!

Answer 1

Ahh, Salesforce doesn't allow direct field to field comparisons in a query. Source:

Field to field comparison in WHERE clause of SOQL

Knowledge Article Number

000187460

Description

I want to execute a query by comparing two fields of the same object in WHERE clause, but I am not able to use a field in the right hand side of a condition in a WHERE clause:

 List<user> users = [SELECT Id,name FROM User WHERE (FirstName != Lastname)];

The above query returns: "System.QueryException: unexpected token: 'Lastname' "

Resolution

Salesforce does not allow direct field to field comparison in SOQL query.

To achieve this you may create a formula field that will compare fields and return a value (like true or false) which you may use in a WHERE clause.

So for the above query, you could create a formula field on User object with return type Checkbox e.g. NameCompare, with the formula

User.FirstName != User.LastName

Now our query will be:

List<User> Users = [SELECT id, name FROM User WHERE NameCompare = TRUE]; 

Following idea has been posted on Ideaexchange portal for allowing field-to-field comparison in SOQL: https://success.salesforce.com/ideaView?id=08730000000BrHAAA0