2

In our org there are more than 5 Millon CaseComment record exists. User will navigate from Account record and opens a page upon a button click. User enters the keyword and it should search on the caseComment records related to the cases under that particular account.

Refer entire code base at my blog Keyword Search Series - Display paginated data using Visualforce and Datatable for CaseComments with server side search

CaseComment Search

We are seeing issues that keyword exists in casecomments but SOSL query doesn't return those records.

SOSL

List<CaseComment> lstComment = [FIND :searchString
                                    IN ALL FIELDS RETURNING CaseComment (id,ParentId,
                                    CommentBody
                                    WHERE Parent.AccountId = :accountId
                                    ORDER BY CreatedDate DESC)                         
                                    LIMIT 1000][0];

Even if that Account has 10 cases and each case has max 10 case comments and we can see keyword exists in comments, but unable to show the record.

We have logged a case on premier support channel and here is the response received.

When running SOSL, you cannot be guaranteed to have consistent results. Within SOSL there are multiple conditions resulting in different limits and also this can be affected by the order of execution of SOSL. Below we will go through known conditions, limitations that could result in inconsistent results returned.

a) When you use SOSL or the Global Search features, you are using a specific search engine implemented at Salesforce.com. SOQL operates at the db level instead.

b) The way the operations work in SOSL is first perform a search across all the indexers, then filter (using the provided criteria), then apply sharing.

This could be explained in a better way if we see the order of execution of SOSL.The order of executuion is as follows:-

  1. The whole table is split in partitions of 50K records
  2. Using each partition’s SOSL index we find the Record ID for the first 10k records in each partition that match the words being searched for.
  3. The Record IDs are sent to the database for processing, which includes tasks like:

==> Retrieving record details

==> Sharing access check

==> Applying WHERE filters.

==> The records that are remaining are sent to the user.

If you see above,you will find that where filter is applied at the last,so it causes the inconsistency. The best way is to use distinct keywords.

Please guide how to resolve this limitation when we have large volume of CaseComments.

  • 1
    I've hit similar and the cause was that SOSL first returns the most relevant 2k records then applies your filters. It could be the records you want have already been excluded in that initial 2k. – Girbot Apr 13 '18 at 20:00
  • Did you find any resolution? – Santanu Boral Apr 13 '18 at 20:00
  • Trained users to use less generic and longer search terms plus we added some logic so if a single word was used we actually use SOQL instead, as the filters behaved as you expect there. This documents the order of filtering: developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/… – Girbot Apr 13 '18 at 20:03
  • I got into similar issue with SOSL. Actually I have less than 100 records on my Sandbox, still SOSL couldn't pull any one from that list. I am about to reach out to Salesforce support to see what's causing this issue. – Austin Evans Apr 13 '18 at 20:06
  • See, if we have custom object then we can create a new field and index that and add that in the filter condition. But here we are searching on CaseComment where new field cannot be added. – Santanu Boral Apr 13 '18 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.