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