2

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
  • forcetree.com/2009/07/… – EricSSH Jan 27 '16 at 19:15
  • perfect. So I need to use a controller. I think I would need SOQL as well in order to pull from the account object. – tjcinnamon Jan 27 '16 at 19:25
2

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)]);  
  • Hmm. I tried it and got this error: FROM Account WHERE Id IN (SELECT Owner.Contact.AccountId FROM Application__c) ^ ERROR at Row:1:Column:59 Didn't understand relationship 'Contact' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. – tjcinnamon Jan 27 '16 at 21:10
  • This is so foreign to me. SELECT Agency_Acronym__c FROM Account WHERE Id IN (SELECT ownerid.user.Contact.AccountId FROM Application__c) apparently I need to go to record owner to user to contact to account. – tjcinnamon Jan 28 '16 at 16:03
  • I'm trying to get the interior query to work – tjcinnamon Jan 28 '16 at 16:04
  • 1
    Normally this format works, but I forgot that there's something special about the User object when it comes to relationship queries... Sometimes the system doesn't like you to query subselect User info, so you may need to do multiple queries to get your result. I'll play around a little bit more and update my answer – RenegadeCoder Jan 28 '16 at 17:16
  • 1
    Because I was only going 2 levels I could afford to use an un-nested IN. From there I created a trigger. I'm still gonna try the method you outlined after my presentation but until then a quick fix was on the docket. I need to learn SOQL and have multi-object joins otherwise the developers who are gonna be adopting the platform are gonna be frusterated. I appreciate your help and I'm gonna post an update when I successfully do this the correct way and not luck out that the user has an account field attached to it. Ironically I figured that out via an ODBC driver :) – tjcinnamon Jan 28 '16 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.