4

I'd like to find out a way to query only Account records owned by a Partner user.

If I try this SOQL query, I get an error:

List<Account> allPartnerAccounts = [Select Id From Account WHERE Owner.AccountId != null];

No such column 'AccountId' on entity 'Name'.

Is there a query parameter that can do this? Note that:

  • I don't want to first collect all account owners and then do a separate query to find out if they're partners.
  • I'd also like to avoid nested queries, as the numbers will be huge and I will likely run into governor limits
  • I know SOQL polymorphism might resolve this, but it's only available in developer preview and I need a solution urgently
1

This is the perfect use case for a Left Inner Join. In this case:

SELECT Id FROM Account WHERE OwnerId IN (SELECT Id FROM User WHERE AccountId != null)

The joined rows do not appear to count against your limits, either. I tested against a sandbox where I had ~7K Account records owned by just 4 users.

Execute Anonymous Script

system.debug([SELECT Id FROM User WHERE Id IN (SELECT OwnerId FROM Account)].size());
system.debug(Limits.getQueryRows());
system.debug([SELECT count() FROM Account]);

Resulting Debug Log

USER_DEBUG [1]|DEBUG|4
USER_DEBUG [2]|DEBUG|4
USER_DEBUG [3]|DEBUG|7091

  • That's interesting and very good to know. I'm sure I ran into some limitations on inner joins in the past, but it must have been something different (or salesforce removed the limit). In the mean time they've seemed to resolve the original quirk as well so while your answer is good (you get my vote), I'm accepting my own which is the simplest solution. – Guy Clairbois Dec 7 '16 at 8:01
1

Thanks for reminding me of this question and providing some possible solutions. I thought I'd go and give them a try. While trying I found out that, to my surprise, this is working on the current API version.

So the quirk seems to have been resolved by Salesforce. The solution for anyone running into this problem now is to move to a recent API (38.0 aka Winter '17 works for sure).

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.