You need to sign in to do that
Don't have an account?
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
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).
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 } } }
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
@XXX Did ever you figure out how to solve this?
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.