[SalesForce] SOSL FIND verses SOQL WHERE

Is there any way to convert SOSL Find clause into SOQL where clause ?
For example following SOSL and SOQL both have same filter criteria.

SOSL: FIND {'ABC' and 'XYZ'} IN ALL FIELDS Returning ACCOUNT(fieldList)

SOQL: Select fields from Account where (Name ='ABC' and Name = 'XYZ')

I have a situation where I come to know at run time, which field to search, hence some time SOSL and some time SOQL will fit into my requirement. But problem is that While providing input, user can input searchText which can have 'operators/phrases' from UI which will directly fit into FIND clause of SOSL, whereas we need to convert that searchText into Where clause to fire SOQL properly.

Hence looking for a way to convert FIND clause into SOQL where clause.

Thanks in Advance.

Best Answer

Perhaps you can explain why you can't just execute the SOSL; why do you need to convert it into SOQL?

If you do need to use SOQL sometimes, it sounds like your Apex code will have to start by parsing the user's input to identify the tokens used and then decide whether to execute Dynamic SOSL or Dynamic SOQL.

The documentation for SOSL's FIND term is several pages long. Parsing something that complicated and then mapping it to roughly equivalent SOQL would be a lot of work and require a lot of tests to be written.

But as you are in control, it is up to you how much of SOSL's syntax you allow your users access to use. So I suggest you create your own "mini-language" (simpler than FIND) for the allowed user input that supports the main things you know your users need to do. That should allow you to write simpler code and fewer tests.

(Or you might provide UI somewhat like Salesforce's view filtering to make it easier for your users to create their queries. That avoids the parsing part of the problem: you just create the Dynamic SOSL or Dynamic SOQL based on the selections.)

Take care to guard against SOQL injection.

Related Solutions

[SalesForce] Sanitize a string before injecting it into SOSL

From Dynamic SOSL - SOSL Injection:

SOSL injection is a technique by which a user causes your application to execute database methods you did not intend by passing SOSL statements into your code. This can occur in Apex code whenever your application relies on end user input to construct a dynamic SOSL statement and you do not handle the input properly.

To prevent SOSL injection, use the escapeSingleQuotes method. This method adds the escape character (\) to all single quotation marks in a string that is passed in from a user. The method ensures that all single quotation marks are treated as enclosing strings, instead of database commands.

Based on that is should be sufficient to escape any single quote characters in the user based input before merging it with your dynamic SOSL.

[SalesForce] SOQL: Bulk Fuzzy Searching

As mentioned in this post, you can perform LIKE on a list of matches. You might want to split on spaces and create a pool of candidates. It may be worthwhile to consume an extra query to try to find exact matches first. You will also probably get a better candidate pool if you use pair-wise neighbor combinations instead of individual split results.

public static List<Account> findCandidates(String potentialName)
{
    if (String.isBlank(potentialName)) return null;
    List<Account> candidates = [SELECT Id FROM Account WHERE Name = :potentialName];
    if (!candidates.isEmpty()) return candidates;

    List<String> partialMatches = new List<String>();
    for (String fragment : potentialName.split(' '))
    {
        partialMatches.add('%' + fragment + '%');
    }
    return [SELECT Id FROM Account WHERE Name LIKE :partialMatches];
}

To get pairwise neighbors would look something like:

static List<String> getFragments(String potentialName)
{
    if (String.isBlank(potentialName)) return null;

    List<String> fragments = new List<String>();
    List<String> chunks = potentialName.split(' ');

    while (chunks.size() >= 2) // starting with one word should yield an empty result
    {
        String fragment = '';
        if (!fragments.isEmpty()) fragment += '%'; //No leading wildcard
        fragment += chunks.remove(0);
        fragment += chunks.get(0);
        if (chunks.size() > 1) fragment += '%'; // No trailing wildcard
        fragments.add(fragment);
    }

    return fragments;
}

Best Answer

Related Solutions

[SalesForce] Sanitize a string before injecting it into SOSL

[SalesForce] SOQL: Bulk Fuzzy Searching

Related Topic