function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
XXXXXX 

SOQL: How to Implement WHERE NOT IN <list>

The application requirement is to send a list of things to a subscriber, that the subscriber has not already received. (The subscriber cannot receive the same thing twice.) A list of things sent is stored in a transaction table, and a list of things that can be sent is stored in a thing table. In regular SQL, this is a simple query:

 

 

SELECT thingId 
  FROM available_things
 WHERE thingId NOT IN 
       (SELECT thingId FROM transactions WHERE subscriberId = 'XXX')
This is not possible in SOQL. Apprently, however, we can do something similar:
// An IN-bind with an Id list. Note that a list of sObjects
// can also be used--the Ids of the objects are used for 
// the bind
Contact[] cc = [select id from contact limit 2];
Task[] tt = [select id from task where whoId in :cc];

(Taken from the Apex Language Reference here.)
 
Unfortunately, it appears that lists, maps, and sets are all limited to 1000 items. (Contact[] is shorthand for List<Contact>.)
 
Since any of my subscribers may have tens of thousands of things sent to them, I cannot do this:
 
Transaction__c[] thingsSent = [select thingId from transaction__c where subscriber__c = :subscriberId];
Thing__c[] things = [select thingId from thing__c where thingId not in :thingsSent];

 
 
I can set an arbitrary upper limit on the number of thiings to send at 1000, but I cannot set any such limit on the number of things that have already been sent. 
 
Can this be done in SOQL?
 
TIA,
 
John


HarmpieHarmpie
Transaction__c[] thingsSent = [select thingId from transaction__c where subscriber__c = :subscriberId];
for(Thing__c[] things : [select thingId from thing__c where thingId not in :thingsSent]) {
	for(Thing__c thing : things) {
		// Do Something
	}
}
 
Something similar to this will probably work, iterating over the list(s).


XXXXXX
But what if the thingsSent list is > 1000 elements? (The documentation says that lists have an intrinsic limitation of 1000 elements.)


HarmpieHarmpie
Similar solution:
for(Transaction__c[] thingsSent : [select thingId from transaction__c where subscriber__c = :subscriberId]) {
	for(Thing__c[] things : [select thingId from thing__c where thingId not in :thingsSent]) {
		for(Thing__c thing : things) {
			// Do Something
		}
	}
}
 


kerwintangkerwintang
This is a little tricky. 2 solutions i can think of:
 
1. use 2 dimensional array.. so the limit would be 1000* 1000 = 1000000 records. 
 
2. Since Transaction has a lookup relationship to Thing, you might try something like this:
 for(Thing__c thing : [select Id, (select Id from Transactions__r where subscriber__c=:subscriberId) from Thing__c]){
   //pseudocode
  if(thing.Transactions__r.size() == 0){ //means this thing is not yet sent to the subscriber
    // do something 
  } 
} 
 
Not sure of the syntax above, but maybe just a starting point for a possible solution. :)
 
Best Regards,
Kerwin 
 


Mats ErikssonMats Eriksson
@XXX Did ever you figure out how to solve this?


Ranjan SinghRanjan Singh
set<Id> recordsetId=new set<Id>();



recordsetId.add();//add some Ids by which you will filter.
list<Object__c> objlist=[select id,name,field__c from Object__c where id not in:recordsetId];
This will work for sure.