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

Question

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

Answer 1

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.

Answer 2

1. apex:page's readOnly="true" probably won't help you.
2. All the LIKE look as if they are the reason of timeouts, not only the size of Set of Ids.. A SOSL query might be better in your case and you can still use WHERE clauses in it to further filter down the results. The question is whether you're fine with displaying "over 200 results" on your Site or does it have to be 100% accurate?
3. Maybe some kind of Analytic Snapshot can save you. Either a real one (based on a scheduled report) or code solution. Batch job or trigger (even with @future) to recalculate some total and insert aggregated helper data somewhere?
4. Bit more hardcore - try to apply small limit on it (say LIMIT 10) and if it works - consider using a standardSetController, queryMore(), maybe even paginated solution?