SOQL joining multiple tables to get account info from record owner

Question

So I have a custom object standard controller and I want to auto-populate a field on a form from the users account record.

In preparation to build my controller I need a valid SOQL statement and I'm getting nested semi-join sub selects error on the Workbench.

I'm getting errors on the SOQL query:

   select agency_acronym__C from account 
   where id in (SELECT AccountId FROM Contact 
   where id in (SELECT ContactId FROM User 
   where id in (SELECT OwnerId FROM Application__c)))

if this where in SQL Syntax I'd just use joins. I'm new to SOQL

select agency_acronym__c from 
account inner join contact on account.accountID = contact.accountID
inner join user on contact.contactid = user.contactid
inner join application__c on user.id = application__c.ownerid

Answer 1

SOQL does not allow you to nest more than one level down in sub-selects, but you can branch several (I think up to 5?) levels upward instead. Restructuring your query, it would normally be valid as follows:
[SELECT Agency_Acronym__c FROM Account WHERE Id IN (SELECT Owner.Contact.AccountId FROM Application__c)]

BUT, since Owner is a polymorphic field (could refer to a User, Group, or other object), we need to do something a bit trickier(link) to filter on it. I have tested the following and it seems to work:

Set<Id> contactsIds = new Map<Id,User>([select ContactId FROM User WHERE Id IN (SELECT OwnerId FROM Application__c WHERE Owner.Type = 'User')]).keyset();
Account[] accounts = new List<Account>([select Agency_Acronym__c from Account where Id in (select AccountId FROM Contact WHERE Id IN :contactsIds)]);