[SalesForce] Semi join sub-selects are not allowed with the ‘OR’ operator

This error is killing me 🙁

Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions

(or)

Semi join sub-selects are not allowed with the 'OR' operator

Here is my code. Can someone help?

Note :- I can't convert the select query that I have written in my where clause to a seperate query (Where Id in (select…. ) and get the data in an id set and substitute in the below query, because my id set has about 35000 records and when I try to query with that set it throws a Time limit exceeded error as it is unable to handle even more than a 1000 or 2000 records in a set if it is in an SOQL IN clause.

And I have just given a sample code here by getting the count, I need to get the count as well as the actual data..



Id CompanyId = '001Z000000CF2PtIAL';
 String SearchText = '%test%';
 Set<Id> SearchgroupassetidSet = new Set<ID>();
 SearchgroupassetidSet.add('a7aZ00000004CHhIAM');
 SearchgroupassetidSet.add('a7aZ00000004CHiIAM');
 SearchgroupassetidSet.add('a7aZ00000004CHjIAM');
 SearchgroupassetidSet.add('a7aZ00000004CHkIAM');
 Integer RecCount=0;
  List<AggregateResult> lstaggResult = [Select count(id) RecCount  FROM Asset__c where  Id IN (Select Asset__c from Customer_Asset__c where isDeleted =false and Company__c =: CompanyId and (Asset__r.Title__c like : SearchText or Asset__r.Name like : SearchText)) 
 or (Group__c IN : SearchgroupassetidSet and ((Title__c like : SearchText) or (Name like : SearchText)))];

  if (lstaggResult.size() > 0)
    {
        RecCount = (Integer)lstaggResult[0].get('RecCount');                        
    }
    System.debug('Record Count : ' + RecCount);

Best Answer

As you state, normally you need to do the subquery separately and store the IDs in a set you can then use in the main query:

set<Id> AssetSet = new set<Id>();

for(Customer_Asset__c asset : [Select Asset__c from Customer_Asset__c where isDeleted = false and Company__c =: CompanyId and (Asset__r.Title__c like : SearchText or Asset__r.Name like : SearchText)])
{
  AssetSet.add(asset.Asset__c);
} 

List<AggregateResult> lstaggResult = [Select count(id) RecCount  FROM Asset__c where  Id IN 
: AssetSet or (Group__c IN : SearchgroupassetidSet and ((Title__c like : SearchText) or (Name like : SearchText)))];

However, if you're sure you can't do this then it would suggest that you may need to use batch apex to do the first (sub) query, leveraging database.Stateful to help with managing the data.

Alternatively look for a way to leverage other fields to reduce the scope of the query.

Important note

In your code you've got a lot of hard coded IDs, e.g. Id CompanyId = '001Z000000CF2PtIAL'; and SearchgroupassetidSet.add('a7aZ00000004CHhIAM');

You should be aware that this is considered bad practice, because once you try to move this code to a different org, say a production environment, these IDs will no longer be valid and as such your code will fail to run.

Related Topic