4

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.

2 Answers 2

5

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.

4
  • I guess this probably consumes fewer query rows, eh? It's so hard to develop the instinct for inner joins!
    – Adrian Larson
    May 16, 2016 at 18:55
  • @AdrianLarson Actually, I'd be more concerned about AggregateResult's 2000 row limit (e.g. it can only return 2000 account IDs), which is significantly less than inner joins can produce. For smaller queries, though, there's not really any significant difference between the two in terms of performance.
    – sfdcfox
    May 16, 2016 at 19:33
  • Good point also. Welp. Can't change my answer now. Still nice to see both approaches, but yours is the clear choice between them. If someone tried to extend this to an object that doesn't support inner joins, then aggregates are useful to know.
    – Adrian Larson
    May 16, 2016 at 19:36
  • i guess select AccountId from contact will retrieve more rows... i have used just 2 plain List<Account> 1 with in id and 1 without not in.
    – user5558
    May 20, 2016 at 4:05
3

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.

4
  • Alternative code: Set<Id> withContact = new Map<Id, AggregateResult>([SELECT AccountId Id FROM Contact GROUP BY AccountId]).keySet();...
    – sfdcfox
    May 16, 2016 at 19:36
  • Haha didn't realize you can alias the Id field itself. Makes sense though, I like it.
    – Adrian Larson
    May 16, 2016 at 19:36
  • I gpt the below error: Field must be grouped or aggregated: Id
    – user5558
    May 20, 2016 at 4:16
  • @user5558 Did you put a , between AccountId and Id? It is intended to be an alias, there should be no comma.
    – Adrian Larson
    May 20, 2016 at 4:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .