Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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

Junction__c joins Accounts to 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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

I've scratched my head on a similar use case for long. You can workaround the limitation of SOSL returning top 200 results by following the wokaround mentioned on this thread - Apex SOSL Paging Implementations

As for the SOSL limitation itself, it's been there as an idea for some time now.

http://success.salesforce.com/ideaView?id=08730000000BqzpAAC

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.