Run SOSL without any search term

Question

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?

Answer 1

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.