Subquery limit handling

Question

Can someone help me in the below scenario. Scenario: I have a list of account id's. In that list of id's i want to separate the list of id's which have contacts associated to an account and the list of id's which don't have associated contacts. --> Accids is the ids of the account. Now i want to separate the id's of account which are having associated contacts and which are not having associated contacts. For that i have written the below query.

List<Contact> co;
Set<Id> yesacc = new Set<Id>();
Set<Id> noacc = new Set<Id>();
for(Account acc : [select (select id from contacts) where id in :accids];
co = new List<Contact>();
co.addAll(acc.contacts);
if(!co.isEmpty()){
yesacc.add(acc.id);
}

else{
noacc.add(acc.id);
}

For few records it is working fine for me, as we have a limitation of subquery for fetching only 200 records. Is there any other way in achieving this without hitting the governor limit.

Answer 1

To simply find all accounts with or without contacts, you can use a sub-query in the filter:

Map<Id, Account> accountsWithContacts = new Map<Id, Account>(
    [SELECT Id FROM Account WHERE Id IN (SELECT AccountId FROM Contact)]);
Map<Id, Account> accountWithoutContacts = new Map<Id, Account>(
    [SELECT Id FROM Account WHERE Id NOT IN (SELECT AccountId FROM Contact)]);

You could also choose to use just plain List<Account> variables if you wanted to.

Answer 2

You can just do an aggregate query to get the positive values, and whatever remains will be the negative set.

Set<Id> withContact = new Map<Id, AggregateResult>([
    SELECT AccountId Id FROM Contact GROUP BY AccountId
]).keySet();
Set<Id> withoutContact = new Map<Id, Account>([
    SELECT Id FROM Account WHERE Id NOT IN :withContact
]).keySet();

Note that you will likely still struggle with query rows governor limits with this approach. If you can limit by an initial set of Account ids, it will be much more efficient.