How to filter through junction object in a many-to-many relationship via SOQL query

Question

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) {
    related_contact_list.add(junction.Contact__c);
}
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?

Answer 1

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);

Answer 2

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).

Answer 3

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];