The left operand cannot have more than one level of relationships

Question

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

Answer 1

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.