[SalesForce] Return all records owned by Partner user in 1 SOQL query

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

Best Answer

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

Related Topic