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

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?

Best Answer

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