How to replicate a "My Account Team and My Accounts" SOQL query?

Question

When running a report and if Account Teams are enabled you have an option to search "My Account Team and My Accounts". This returns accounts in which you are listed on the sales team or you are the account owner. Below is the SOQL and error I am recieving:

List<Account> acc = [SELECT Id FROM account WHERE Id IN (SELECT accountId FROM accountteammember WHERE UserId = :UserInfo.getUserId()) OR ownerid = :UserInfo.getUserId()];

Error: Semi join sub-selects are not allowed with the 'OR' operator

Is this a dead end? Any ideas how this could be accomplished with SOQL....or maybe even without SOQL.

Answer 1

You will have to do this as 2 separate queries.

Id userId = UserInfo.getUserId();
//First query the AccountTeamMember records
List<AccountTeamMember> members = [
    SELECT
        Id,
        AccountId
    FROM
        AccountTeamMember
    WHERE
        UserId = :userId];
//now get the Ids of all the accounts
Set<Id> accountIds = new Set<Id>();
for(AccountTeamMember member : members) {
    accountIds.add(member.AccountId);
}
//Now you can query the accounts themselves
List<Account> acc = [
    SELECT
        Id
    FROM
        Account
    WHERE
        Id IN :accountIds
        OR OwnerId = :userId];

This does affect your governor limits because using a sub-select query only counts as one SOQL query. As well as the heap space the accountsIds Set uses. But because of the Salesforce restrictions I don't think there is another way.