I am having three objects - Contact, List and Contact_List_Junction. As the name implies, Contact and List are having a many-to-many relationship linked by the junction object. Now I want to have a SOQL query to retrieve the list of contacts which is not connected to current list.

Apparently I can achieve this using two SOQL queries, as in below:

List<Contact_List_Junction__c> junction_list = [Select Id, Name, Contact__c From Contact_List_Junction__c Where List__c != :list_id];
List<Id> related_contact_list = new List<Id>();
for(Contact_List_Junction junction : junction_list) {
List<Contact> contact_list = [Select Id, Name From Contact where Id not in : related_contact_list];

The problem with above code is the records limit. Suppose I am having 10000 records in Contact table and 9900 of them are actually connected with current list it will be a big retrieving too many records limit issue here.

Is there a way to better filter the query?

  • contact_list will only have those records which do not have child object. Limit of SOQL is 50,000. So in contact_list you should put that limit.
    – Peeyush
    Jul 26 '16 at 6:04
  • @Peeyush, limit is 50,000 per execution context, not per query
    – Lance Shi
    Jul 26 '16 at 6:07

There is no need to create additional fields when working with Junction. Here is the example, Invoice__c -> Line_Item__c <- Merchandise__c. This query will retrieve all Invoices which is not linked to particular Merchandise__c.

List<String> merchandiseIds = new List<String> {'a04280000078hANAAY'};
List<Invoice__c> inv = [SELECT Id, Name 
    FROM Invoice__c
    WHERE Id IN 
        (SELECT Invoice__c 
        FROM Line_Item__c 
        WHERE Merchandise__c NOT IN :merchandiseIds

System.debug('AAAA \\n ' + inv);

Using Subqueries will not solve your problem as rows fetched in inner query also be counted in the Limits. So preferable solution is:

Is your Contact_List_Junction__c has a master detail relationship with contact?

If yes, you can create a rollup summary field in contact to store the count of child Contact_List_Junction__c. Then you can simply write 1 SOQL, which will fetch only the records you want. like:

List<Contact> contact_list = [Select Id, Name From Contact where Junction_Count__c > 0];

Here Junction_Count__c is a rollup summary (count of Junction records).

  • Thank you for your answer and plus 1 for this. But unfortunately this doesn't resolve my issue. Your query will get the contacts which has linked to no list. But what I need is getting the contacts which is not linked to current list.
    – Lance Shi
    Jul 26 '16 at 6:16

Answer of Denis is perfect, meaning there will be no problem of limits if one uses subquery in "WHERE Clause" like following

List<Contact> listContacts = [SELECT Id, Name FROM Contact 
                              WHERE Id NOT IN (SELECT Contact__c 
                                  FROM  Contact_List_Junction__c 
                                  WHERE List__c NOT IN :list_id)];

Harting's point is also valid but it applies only if query is from Parent to Child object like following

List<Account> listAccounts = [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account];

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.