[SalesForce] SOQL joining multiple tables to get account info from record owner

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

Best Answer

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)]);

Related Solutions

[SalesForce] Semi join sub-selects are not allowed with the ‘OR’ operator

As you state, normally you need to do the subquery separately and store the IDs in a set you can then use in the main query:

set<Id> AssetSet = new set<Id>();

for(Customer_Asset__c asset : [Select Asset__c from Customer_Asset__c where isDeleted = false and Company__c =: CompanyId and (Asset__r.Title__c like : SearchText or Asset__r.Name like : SearchText)])
{
  AssetSet.add(asset.Asset__c);
} 

List<AggregateResult> lstaggResult = [Select count(id) RecCount  FROM Asset__c where  Id IN 
: AssetSet or (Group__c IN : SearchgroupassetidSet and ((Title__c like : SearchText) or (Name like : SearchText)))];

However, if you're sure you can't do this then it would suggest that you may need to use batch apex to do the first (sub) query, leveraging database.Stateful to help with managing the data.

Alternatively look for a way to leverage other fields to reduce the scope of the query.

Important note

In your code you've got a lot of hard coded IDs, e.g. Id CompanyId = '001Z000000CF2PtIAL'; and SearchgroupassetidSet.add('a7aZ00000004CHhIAM');

You should be aware that this is considered bad practice, because once you try to move this code to a different org, say a production environment, these IDs will no longer be valid and as such your code will fail to run.

[SalesForce] SOQL join between two standard objects

You can try querying the Case object and using dot notation to get to the Account info:

SELECT Account.Id, Account.Name, Id, Description from Case

Best Answer

Related Solutions

[SalesForce] Semi join sub-selects are not allowed with the ‘OR’ operator

[SalesForce] SOQL join between two standard objects

Related Topic