I have a search query in my component that shortly will need to support more than one lookup field where only one would be filled on the certain object. For now the query looks as follows:
SELECT Name, ... FROM Object WHERE First_Field__c =: value
[AND... many other conditions]
Where First_Field__c
is a lookup to FirstObject__c
.
I am wondering what would be more reasonable to do considering efficiency and performance of the query.
I can think of two solutions:
1. Add an OR to the query
SELECT Name, ... FROM Object WHERE First_Field__c =: value
OR Second_Field__c =: value
Where Second_Field__c
is a lookup to SecondObject__c
.
2. Create a formula field Formula__c like ISBLANK(First_Field__c, Second_Field__c ,First_Field__c )
and use it in the query without an OR.
SOSL cannot be leveraged in this scenario as I am using multiple cross-reference fields and conditions. As we know, lookup fields are indexed while formulas are not but ORs also slow down queries. It seems there is no optimal solution, is there?