0

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?

2
  • 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, 2016 at 6:04
  • @Peeyush, limit is 50,000 per execution context, not per query
    – Lance Shi
    Jul 26, 2016 at 6:07

3 Answers 3

3

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

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

1
  • 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, 2016 at 6:16
1

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

You must log in to answer this question.

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