A more detailed explanation follows, but the core of my question is: what are best practices for implementing a general keyword search in SOQL/Apex?
In an app that I'm building, we're using a data structure where we have a custom junction object (Junction__c) reflecting a many-to-many relationship between Accounts and another custom object (Custom__c):
In the app, we're displaying Junction__c records that match a variety of criteria on the related Account and Custom__c records. There are actually a bunch of criteria, but this gets the idea across:
SELECT Id, Other__c, Fields__c
Account__r.Field1__c, Account__r.Field2__c,
Custom__r.Field3__c, Custom__r.Field4__c,
FROM Junction__c
WHERE Account__r.Field1__c = :inputVar1 AND Custom__r.Field3 = :inputVar2
We just got a new requirement from our product owner, that we need to support a general keyword search on about 10 fields from the Account and Custom__c records. That basically means adding another WHERE clause like this, which seems really inefficient, particularly because I'll be searching against long text fields:
AND (Account__r.Field1 LIKE '%keyword%'
OR Account__r.Field2 LIKE '%keyword%'
OR Custom__r.Field3 LIKE '%keyword%'
OR Custom__r.Field4 LIKE '%keyword%'
OR [etc.])
After poking around a bit, I got the idea of doing the keyword search in SOSL and using those results in my SOQL:
List<List<sObject>> keywordResults = [FIND 'keyword' IN ALL FIELDS
RETURNING Account (Id)
Custom__c (Id)];
List<Account> keywordAccounts = keywordResults.get(0);
List<Custom__c> keywordCustom = keywordResults.get(1);
// later...
String keywordWhereClause = 'AND (Account__r IN :keywordAccounts
OR Custom__r IN :keywordCustom)';
This approach seems OK in theory, but I don't know how to gauge performance implications of these multiple queries, and I'm concerned about the documented restriction that SOSL is limited to the top 200 results, since our data set will be bigger than that.
So back to my original question. With this set of requirements and constraints, how can I effectively perform a keyword search?
