[SalesForce] Implementing a Keyword Search

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?

Best Answer

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

Related Solutions

[SalesForce] Using SOSL with where clause

As per the Developer Guide on SOSL WHERE clause, it is used to filter the search results:

By default, a SOSL query on an object retrieves all rows that are visible to the user. To limit the search, you can filter the search result by specific field values.

Furthermore, the syntax of the SOSL also suggests that the WHERE clause is applied to search results, yielded by the text search:

FIND {search_query} RETURNING sObject(fieldName WHERE fieldExpression)

Consider the following example:

FIND {John} IN NAME FIELDS RETURNING Account(Id, Name WHERE IsImportant__c=true), Contact(Id, FirstName, LastName WHERE IsPrimary__c=true)

In the example above, one would expect the search to be performed on all Accounts and Contacts, and the filter subsequently applied to the resulting record set (all Accounts and Contacts where term 'John' is found in Name fields), thus eventually returning only the records meeting the expression criteria (all Important Accounts and Primary Contacts where term 'John' is found), rather than initially filtering all accounts and contacts to important/primary ones and then doing a text search on those.

I think in your case using SOSL rather than SOQL might indeed yield performance gains (digression: are all of the searchable fields textual and are they indexed?). If I understood you correctly, your users will be able to enter search terms freely, meaning your search will check against fuzzy matches, meaning you would have to be using wildcards in SOQL conditions. Furthermore, as you mentioned that users will be able to select fields on which to search against, it means you would end up with multiple conditions in WHERE clause. Consider following remarks from the Best Practices for LDV handbook:

Considerations when using SOQL or SOSL:

Both SOSL search queries and SOQL WHERE filters can specify text to look for. When a given search can use either language, SOSL is generally faster than SOQL if the search expression uses leading wildcards or a CONTAINS term.

In some cases, when multiple WHERE filters are being used in SOQL, indexes cannot be used even though the fields in the WHERE clause may be indexed. In this situation, decompose the single query into multiple queries each with one WHERE filter and then combine the results.

Given the problem presented (users being able to select multiple fields and enter search text against each field), I assume you considered translating the search conditions into the WHERE condition in SOSL. Does not sound like a good idea, as you might end up with the following:

...RETURNING YourTargetObject(Id WHERE selectedField1 LIKE %textInput1% OR selectedField2 LIKE %textInput2%)

Apart from the above, the question arises what would you use as the primary search query in such case. Instead of such WHERE filtering, it would seem wiser to have all the user's textual inputs bundled in the SOSL's FIND search term, especially having the following performance consideration from the Force.com SOQL and SOSL Reference in mind:

SOSL can tokenize multiple terms within a field (for example, multiple words separated by spaces) and builds a search index off this. If you’re searching for a specific distinct term that you know exists within a field, you might find SOSL is faster than SOQL for these searches. For example, you might use SOSL if you were searching for “John” against fields that contained values like “Paul and John Company”.

So you might find it that the latter could be more efficient than the former:

FIND {textInput1* OR textInput2*} RETURNING YourTargetObject

But to give you a definite answer whether SOQL or SOSL would be more efficient is kind of an ungrateful task, as important details are missing (are the searchable fields only searchable by text, how many fields can the search check against, how complex can the custom search logic become etc). Your best bet would be to pull in some typicall searches users might make and compare both SOQL and SOSL performance.

Best Answer

Related Solutions

[SalesForce] Using SOSL with where clause

Related Topic