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
Steve BerleySteve Berley 

Batch Apex workaround for - Semi join sub-selects are not allowed with the 'OR' operator

I'm trying to do a query such as below.  Granted the SOQL itself is silly but it gets the point across
  
SELECT id, name FROM Account
WHERE name = 'whatever' OR id IN ( SELECT accountID FROM contact WHERE lastname = 'something' )
No surprise that it yields the error:   
MALFORMED_QUERY: Semi join sub-selects are not allowed with the 'OR' operator

The challenge is, how do you accomplish this type of goal in batch apex?
The only idea I've come up with is to split the query into two parts 
Query A: 
SELECT id FROM Account
WHERE id IN ( SELECT accountID FROM contact WHERE lastname = 'something' )
Query B:
SELECT id, name FROM Account WHERE name = 'whatever'
  1. Make the batch class stateful and in the init store the results of Query B to a map.
  2. Make Query A the one returned by the batch start() method.  
  3. In the execute() method, process the scope (from query A) and as all records from the map of query B as they're processed.
  4. In the finish() method process records that remain in the map of query B after all query A results handled in the batches.
While I think this would work, I'm hoping there's a better/more elegant solution.

How have you all solved this problem?

Thanks,
Steve BerleySteve Berley
With the help of some nice wine, I've come come up with a better approach.
  1. Still make the class stateful
  2. A is still the interated query the batch loops through, but this time, as each record is processed in the execute() method its id is recorded in a set of ids.
  3. Query B is still processed in the finish() method, but it's modified to be  
    SELECT id, name FROM Account WHERE name = 'whatever' AND id not in (the set of IDs)