How can i Query a Child's Parent's Parent in SOQL

Question

I have Objects in the following heirarchy:

Account
   -> Opportunity
      ->Project Manager__c
        ->Project Services__c

Project Services object is tied to Project Manager by Project_Manager_ID__c (Master-Detail(Project Manager))

Project Manager is tied to Opportunity by Opp_ID__c (Master-Detail(Opportunity))

I am trying to write an SOQL query that gets data from each object on the way up.

This gets me 1 level up:

SELECT Id, Project_Manager_ID__r.Name from Project_Services__c where CMS_Service_Id__c='19361'

Gets me the Id of the project service, and the name of the project manager. How can i further up and get the project manager's Opportunity Name, and then even a step further, the opportunity's account name?

if i try:

SELECT Id, Project_Manager_ID__r.Name, Project_Manager_ID__r.Opp_ID__r.Name from Project_Services__c where CMS_Service_Id__c='19361'

I get

In the end I am looking for:

Project Service Id Project Manager Name Opportunity Name Account Name

Answer 1

Your query is formed just fine. The Developer Console simply won't render more than one level up. However this SOQL will work perfectly in your code base. If you wish for such queries to work in the Query Editor, you will have to add some cross object formulas so you can look up to a field that is at most one level of separation.