[SalesForce] The left operand cannot have more than one level of relationships

I'm trying to do the query :

 List<Scholars__c> scholList = [SELECT Id, Name, Balance_Amount__c, 
 ContactId__c, Student_Name__r.Id, Student_Name__r.Name 
 FROM Scholars__c 
 WHERE Student_Name__r.Id 
 IN (SELECT Recipient__c 
 FROM Report_Item__c 
 WHERE Donation_name__c =: recordId AND Report_Document__c=:reportDocId)];

I getting the error :

The left operand cannot have more than one level of relationships

I tried to use a custom field named "ContactId__c" which I fill the Id of the Student, but then I get the error :

The left operand field in the where expression for outer query should
be an id field, cannot use: 'ContactId__c'

What else can I try to achieve that?

Thanks

Best Answer

Instead of Student_Name__r.Id, just use Student_Name__c. They technically mean the same thing, but doesn't fall foul of the relationship rule. In general, you should actively avoid the use of __r.Id instead of simply using __c, because it is more efficient in terms of memory use, amount of code you need to type, and to avoid certain classes of errors such as the one you received. The other error about ContactId__c suggests it is not a relationship field, but instead a normal text field. If this is the case, you won't be able to use it in a sub-query filter that way, since only relationship fields can be filtered this way.

Related Solutions

[SalesForce] NOT IN based on another query

A simpler way would be to form a set or list of strings which correspond to the Ids for of Me_MyCustomObject__c and then use it in the where clause of the query:

for example:

List<Me_MyCustomobject__c> myCustomObjects = [SELECT Id FROM Me_MyCustomObject__c];
Set<String> setIds = new Set<String>();

for (Me_MyCustomobject__c customObject:myCustomObjects) {
    setIds.add(String.valueOf(customObject.Id));
}

List<Task> filteredTasks = [SELECT Id, ME__ThirdPartyId__c, WhatId FROM Task WHERE Me_ThirdParty__c NOT IN :setIds];

[SalesForce] SOQL error “cannot have more than one level of relationships”

Try reworking the query to start from Access_object__c and then group the resulting facility ids and names.

select Department__r.Facility__c, Department__r.Facility__r.Name 
from Access_object__c 
where User__c = :UserInfo.getUserId()
group by Department__r.Facility__c, Department__r.Facility__c.Name

Best Answer

Related Solutions

[SalesForce] NOT IN based on another query

[SalesForce] SOQL error “cannot have more than one level of relationships”

Related Topic