1

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, 
                Id, 
                Name, 
                (SELECT Id, 
                        Name, 
                        Contact_Name__c, 
                        Contact__r.Name, 
                        Contact__r.Email 
                    FROM Position__r 
                    WHERE RecordType.Name = 'Collegiate Ministry' 
                    AND Position_Title__c = 'Campus Minister'),  
                (SELECT Address_Line1__c, 
                        Address_Line2__c, 
                        City__c, 
                        State__c, 
                        Zip_Postal_Code__c, 
                        Country__c, 
                        URL__c, 
                        Email__c 
                    FROM Addresses__r 
                    WHERE RecordTypeId IN (:rtLoc.Id, :rtMail.Id)) 
            FROM Account 
            WHERE 
                Name LIKE :term + '%'
                OR
                Position__r.Contact__r.Name LIKE :term + '%'
                OR
                Addresses__r.City__c LIKE :term + '%' 
                OR
                Addresses__r.State__c LIKE :term + '%'
Improve this question

3 Answers 3

Reset to default
4

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
4
  • 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, 2012 at 23:30
  • 2
    Also are semi join sub-selects allowed when using 'OR'?
    – fourq
    Dec 6, 2012 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, 2012 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, 2013 at 0:06
2

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
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .