Why am I getting this error? If I remove the Addressess__r references in the WHERE clause it works fine.

Error: Didn't understand relationship 'Addresses_r' in field path. If you are attempting to use a custom relationship, be sure to append the '_r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

             SELECT ParentId, 
                (SELECT Id, 
                    FROM Position__r 
                    WHERE RecordType.Name = 'Collegiate Ministry' 
                    AND Position_Title__c = 'Campus Minister'),  
                (SELECT Address_Line1__c, 
                    FROM Addresses__r 
                    WHERE RecordTypeId IN (:rtLoc.Id, :rtMail.Id)) 
            FROM Account 
                Name LIKE :term + '%'
                Position__r.Contact__r.Name LIKE :term + '%'
                Addresses__r.City__c LIKE :term + '%' 
                Addresses__r.State__c LIKE :term + '%'

based on the select part of your query it appears that addresses is a child of account, in which case, your where clause don't make any sense. you probably want some kind of semi-join type query, e.g.

select .... from account where id in (select accountId__c from address where city__c like :term)
|improve this answer|||||
  • Why would Position__r.Contact__r.Name work in the WHERE clause and not Addresses__r? They are both have the same kind of relationships with account. – fourq Dec 6 '12 at 23:30
  • 2
    Also are semi join sub-selects allowed when using 'OR'? – fourq Dec 6 '12 at 23:34
  • they didn't used to be, but i can't remember off the top of my head if that restriction has subsequently been lifted, you'd have to try it. – superfell Dec 7 '12 at 22:31
  • You can't. From the soql ref: You cannot use subqueries in conjunction with OR. Sometimes you just get strange error messages when you try it. ;) – Peter Knolle Jan 8 '13 at 0:06

In your schema, it looks like you have one Account, lookup to position and related list (many) Addresses.

In your final WHERE clause you are mentioning a related list of addresses ( Addresses_r ) , since this syntax (_r) represents a list, you are building an invalid field path. Basically, this list does not contain a field, it contains records which each may have a City field.

That is why you got the error.

take a look at the semi-join syntax: http://developer.force.com/cookbook/recipe/writing-shorter-queries-using-outer-joins

|improve this answer|||||

If the relationship name is Addresses__r, then in your Where clause it should probably read

Id in (select Account__c from Address__c where City__c LIKE :term +'%')
Id in (select Account__c from Address__c where  State__c LIKE :term + '%')
|improve this answer|||||
  • no, that doesn't work. =( – fourq Dec 6 '12 at 23:11
  • If you click on the Address__c field on Account, what does it say the relationship name is? – techtrekker Dec 6 '12 at 23:15
  • The name is Addresses – fourq Dec 6 '12 at 23:28
  • 2
    Are semi join sub-selects allowed when using 'OR'? – fourq Dec 6 '12 at 23:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.