Accessing Parent Field through Child relationship

Question

I am running a SOQL query against the Contact object and also querying a custom child object (Job_History__c) under the Contact that has a relationship back up to the Account object. My goal is to be able to output the value of a field on the Account object through the Job History object, but I get an error:

"Variable does not exist: Account"

I can run the same query and see the field and value I want to retrieve in the Workbench tool after turning on "Allows SOQL Parent Relationship Queries" in the Workbench settings menu, but I can't access it in Apex for some reason. I am aware that I am trying to access the Account object through a child of the Contact object, but there is a valid reason for this for my use case.

The list of contact records ("contactList") I am working with has a filtered subquery against the Job_History__c custom object which is also related (master-detail) to the Account. I am trying to access the "Name" field on the Account record through this relationship, but I get the above error.

Below is the code. How can I access the "Name" field on the Account record through the Job_History object in my for loop.

List<Contact> contactList = [ SELECT Id, Name, (SELECT Id, Account__r.Name FROM Job_History__r WHERE Status__c = 'Active' LIMIT 1) FROM Contact];

for (Contact con : contactList ) {
  String accountName = con.Job_History__r.Account.Name;
 }

Answer 1

Your custom object Job_History__c must necessarily have a custom relationship to the Account object. This means that the relationship field cannot be Account - it must end in __c/__r, as all custom relationships do.

Your SOQL shows that it is actually Account__r, which means that in Apex you would have to do

String accountName = con.Job_History__r[0].Account__r.Name;

where 0 is the index of the desired Job History within the returned list of child objects. In this case, you're querying for a maximum of one record, so you just need to check if the list is empty before you attempt this access.

Answer 2

con.Job_History__r 

Above will return list of job histories for a con. You need to iterate this list and get account name for each job history.

String accountName = con.Job_History__r[0].Account.Name;