How to get around "Semi join sub-selects are not allowed with the 'OR' operator"

Question

I have an object called Child__c, which has two lookups to Account called Account_Lookup_1__c and Account_Lookup_2__c.

I have the following SOQL query, which works as you'd expect it would

select Id
from Account
where Id in (select Account_Lookup_1__c from Child__c) and
      Id in (select Account_Lookup_2__c from Child__c) 

But I cannot do it with an OR, which is what I really want:

select Id
from Account
where Id in (select Account_Lookup_1__c from Child__c) or
      Id in (select Account_Lookup_2__c from Child__c) 

Thanks to this error:

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

Are there any hacky SOQL workarounds to this, or do I need to look at selecting them separately and manually merging?

Answer 1

You have to do pre-processing to get this one working. You don't need a separate Set for each lookup, though, since you're filtering them all on the same field.

Set<Id> accountIds = new Set<Id>();
for (AggregateResult aggregate : [
    SELECT Account_Lookup_1__c a1, Account_Lookup_2__c a2
    FROM Child__c
    GROUP BY Account_Lookup_1__c, Account_Lookup_2__c
]){
    accountIds.add((Id)aggregate.get('a1'));
    accountIds.add((Id)aggregate.get('a2'));
}
List<Account> records = [SELECT Id FROM Account WHERE Id IN :accountIds];

However, if your goal is simply to find Account records that have Child__c records associated with them, I highly recommend implementing declarative-lookup-rollup-summaries to implement a Child_Count__c field. Then you can just do:

List<Account> records = [SELECT Id FROM Account WHERE Child_Count__c > 0];