0

I have read the documentation for SOSL and have a usecase which is not explicitly mentioned anywhere.

I can not use SOQL as it takes over 15 seconds. We have some non-index fields and wild cards comparison which makes SOQL not useful

So, I want to use SOSL for faster search results

FIND {%%} IN ALL FIELDS Returning Product2(Id, Details__c, Name   WHERE Show_on_Screen__c = true (Name LIKE: sWildCardText OR  Search_Word__c LIKE: sWildCardText OR  Item_Number__c LIKE: sWildCardText )  LIMIT 50 OFFSET 0 )

I want to do search for all product2 records and hence searchTerm {%%} is just not required. I did see this link and did not work for me How do I SOSL across objects, matching all records with FIND clause?

Can someone suggest something to search all product records using SOSL?

5
  • FIND {wildCardText} IN NAME FIELDS...
    – identigral
    Sep 15, 2023 at 17:02
  • 1
    @identigral I want to get all records. I updated the query, was giving an example. It is not only for Name fields, there may be more fields. But the data table initially loads without any search word. If user wants to filter, they put some search word and then we filter the records
    – Dave
    Sep 15, 2023 at 17:11
  • You're swimming against the current here. The right solution is to use the built-in capability of SOSL and throw as many search terms/filters in there as you can based on user input.
    – identigral
    Sep 15, 2023 at 17:15
  • There is no user input initially. That is the issue here. Is there a way to use SOSL without giving search word?
    – Dave
    Sep 15, 2023 at 17:48
  • You should consider if you can optimize your SOQL, perhaps getting Salesforce to add custom indexes (if you cannot do that for yourself) or even skinny tables. Do the analysis then raise a support case for help in getting your query to run appropriately.
    – Phil W
    Sep 15, 2023 at 20:04

1 Answer 1

0

Instead of an empty search term, you can ask for every string that starts with a single letter and/or a number and OR the wildcard the search terms:

FIND {a* OR b* OR ...} ...`

On paper, this might appear to be enough but there's a catch.

The SOSL doc says this about the wildcard behavior:

Asterisks match zero or more characters at the middle or end of your search term. For example, a search for john* finds items that start with john, such as john, johnson, or johnny. A search for mi* meyers finds items with mike meyers or michael meyers.

The terms middle or end are ambiguous. From the doc's very simple example, it might appear that the wildcard is non-greedy. That is, given a corpus consisting of two words foobar and awful, searching with f* will return only foobar.

This would be somewhat unusual in a world of regular-ish expressions even as simple as these. The default wildcard behavior is typically greedy': f* should match both foobar and awful. And indeed the wildcard behavior in SOSL is greedy - FIND {a* OR b* OR ...} will return strings that have a or b in the middle but do not start with a or b. If we cover the entire alphabet, perhaps the additional results generated by greediness of the search won't break our search but we can do better.

Fortunately SOSL welded SOQL-like predicates to scope down the result set and wildcards in these SOQL-like terms are non-greedy. For each wildcarded segment of SOSL search term, adding a corresponding predicate to the WHERE clause does yield the desired result:

FIND {a* OR b* OR ...} IN EMAIL FIELDS 
RETURNING User (Email WHERE (Email LIKE 'a%' OR Email LIKE 'b%' OR...) ORDER BY EMAIL) 
WITH HIGHLIGHT

The FIND query above is a simplified rather than a general solution because of many to one relationship between search term in FIND versus the fields in the RETURNING clause. In this example IN EMAIL FIELDS looks at multiple fields of type Email whereas the WHERE clause only considers User.Email. As long as there aren't too many fields that need to be locked on via the WHERE clause, this hack works.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .