Unsupported Object Types for SOSL Query

 

Although SOSL (Salesforce Object Search Language) is less commonly used than SOQL (Salesforce Object Query Language), it does provide some powerful features.

Main SOSL advantages are the ability to search multiple objects in a single query and the ability to search for a keyword in all the text fields in the queried objects (this includes long text fields!).

You can read more about the basics and considerations for SOSL queries in Salesforce guide. In this article, I will focus on a specific consideration - supported and unsupported object types for SOSL.

Consider the following apex code that provides a function for searching any object type:

public with sharing class QueryServices{
    public static List<sObject> searchRecords(String objectType, List<String> fields, String searchKeyword){
        List<SObject> records = new List<SObject>();    
 
	searchKeyword += '*';
        String returningQuery = objectType + ' (' + String.join(fields,',') + ')';
        String query = 'FIND :searchKeyword IN ALL FIELDS RETURNING '+returningQuery+' LIMIT 20';
        
        records = Search.Query(Query)[0];
        
        return records;
    }
}

The method searchRecords receive as parameters:

• Object name to search
• List of fields to query
• Keyword

The method return a list of records that have at least one text field with the keyword. 

Therefore, with this statement:

List<sObject> result = 

    QueryServices.searchRecords(

        'Account', new List<String>{'Name'}, 'test');

We will receive a list of account records that have at least one text field that starts with the word 'test'.

The content of the class is generic, so theoretically we should be able to use it for any object type. 

However, running the following code:

List<sObject> result = QueryServices.searchRecords(

    'AccountHistory', new List<String>{'OldValue', 'NewValue'}, 'test');

Will result with an exception:

    System.QueryException: entity type AccountHistory does not            support search

The exception stated that the AccountHistory object doesn't support search, and hence it cannot be used for SOSL query.

In fact, there is a long list of standard object types that don't support search. 

First, let's reduce the concern level. All the common standard objects do support search, plus custom objects can be searched if we mark the property Allow Search in the object setup page.

In the list of unsupported we can find, for example: 

• History objects
• Share objects 
• Some setup objects like: RecordType, ObjectPermission, SetupAuditTrail
• Custom Metadata Types

In order to find which object types support search, we can use the object describe and the method isSearchable

for(Schema.SObjectType objectType : Schema.getGlobalDescribe().values()){
	Schema.DescribeSObjectResult dsr = objectType.getDescribe();
	
	if(dsr.isSearchable()){
		System.debug('cannot search:: ' + dsr.getName());
	}
}

After understanding this limitation, the main question is: what are the alternatives when we want to build a generic solution and support all the object types? 

We can convert the entire logic to SOQL, but it will cause some pain, as we will need an alternative for the advantages of SOSL (for example, searching in long text fields). 

public with sharing class QueryServices{
  
    public static List<sObject> searchRecords(String objectType, List<String> fields, String searchKeyword){
        List<sObject> records = new List<sObject>();
        
        Schema.SObjectType objType = Schema.getGlobalDescribe().get(objectType);
        
        //Retrieve the fields on the object
        Map<String, Schema.SObjectField> fieldsMap = objType.getDescribe().fields.getMap();
        List<String> textFields = new List<String>();
        
        //Find text fields on the object
        for (String fieldName : fieldsMap.keySet()) {
            Schema.DescribeFieldResult fieldResult = fieldsMap.get(fieldName).getDescribe();
            if (fieldResult.getType() == Schema.DisplayType.String && fieldResult.isFilterable()) {
                textFields.add(fieldName);
            }
        }
                
        // Construct SOQL query dynamically
        String query = 'SELECT ';
        for (String field : fields) {
            query += field + ', ';
        }
        query = query.removeEnd(', ') + ' FROM ' + objectType + ' WHERE ';
        
        for (String field : textFields) {
            query += field + ' LIKE \'' + searchKeyword + '%\' OR ';
        }
        query = query.removeEnd(' OR ');
        System.debug('query::' + query);
        
        return Database.query(query);
    }
}

The code dynamically constructs SOQL query and searches for keyword in each text field. If we would like to filter with long text fields, we will have to do it after the query.

Also note that some setup objects might have other special considerations. For example, there are setup object types that don't have a single field that can be used as a filter, or when querying Custom Metadata Types you might get an Disjunctions not supported error. For that reason, I would suggest to dividing the search engine into three areas:

1. Special object types
2. Support for search
3. Unsupported for search

Therefore, the final method can look like the one below, with the consideration that if we find other special cases, we might need to add an additional if statement at the start.

public with sharing class QueryServices{
    
    public static List<sObject> searchRecords(String objectType, List<String> fields, String searchKeyword){
        List<SObject> records = new List<SObject>();    
        
        //Is it metadata types - special query
        if(objectType.endsWith('__mdt')){
            records = Database.query('SELECT ' + String.join(fields,',') + ' FROM ' + objectType + ' WHERE DeveloperName LIKE \'' + searchKeyword + '%\'');
        }
        //Support search? Run SOSL
        else if(Schema.getGlobalDescribe().get(objectType).getDescribe().isSearchable()){
            records = searchWithSOSL(objectType, fields, searchKeyword);
        }
        //Else - run SOQL
        else {
            records = searchWithSOQL(objectType, fields, searchKeyword);
        }
        
        return records;
    }
    
    private static List<sObject> searchWithSOSL(String objectType, List<String> fields, String searchKeyword){
        List<SObject> records = new List<SObject>();    
        
        searchKeyword += '*';
        String returningQuery = objectType + ' (' + String.join(fields,',') + ')';
        String query = 'FIND :searchKeyword IN ALL FIELDS RETURNING '+returningQuery+' LIMIT 20';
        
        records = Search.Query(Query)[0];
        
        return records;
    }
    
    private static List<sObject> searchWithSOQL(String objectType, List<String> fields, String searchKeyword){
        List<sObject> records = new List<sObject>();
        
        Schema.SObjectType objType = Schema.getGlobalDescribe().get(objectType);
        
        //Retrieve the fields on the object
        Map<String, Schema.SObjectField> fieldsMap = objType.getDescribe().fields.getMap();
        List<String> textFields = new List<String>();
        
        //Find text fields on the object
        for (String fieldName : fieldsMap.keySet()) {
            Schema.DescribeFieldResult fieldResult = fieldsMap.get(fieldName).getDescribe();
            if (fieldResult.getType() == Schema.DisplayType.String && fieldResult.isFilterable()) {
                textFields.add(fieldName);
            }
        }
                
        // Construct SOQL query dynamically
        String query = 'SELECT ';
        for (String field : fields) {
            query += field + ', ';
        }
        query = query.removeEnd(', ') + ' FROM ' + objectType + ' WHERE ';
        
        for (String field : textFields) {
            query += field + ' LIKE \'' + searchKeyword + '%\' OR ';
        }
        query = query.removeEnd(' OR ');
        System.debug('query::' + query);
        
        return Database.query(query);
    }
}