Filtering by Long Text Area field in SOQL

Question

I'm working on a custom search page, which will search through the fields of the Project object. I use dynamic SOQL query similar to that:

String query = 'SELECT Name, Status__c ... 
    FROM Project__c WHERE Name LIKE \'%' + String.escapeSingleQuotes(name)
     +'%\' + ' OR Status__c = \'' + String.escapeSingleQuotes(status) +'\'';
List<Project__c> projects = Database.query(query);

But when I try to add to filter criteria Description__c field, which is Long Text Area, get the following error:

System.QueryException: field 'Description__c' can not be filtered in query call

How can I use Long Text Area fields in filtering of results?

Answer 1

You can not use text area fields in SOQL and SOSL filter criterias.

Try this approach:

List<Project__c> projects = new List<Project__c>();
for(Project__c proj : [SELECT Name, Status__c, Description__c FROM Project__c WHERE Name LIKE '%' + String.escapeSingleQuotes(name) +'%' + OR Status__c = String.escapeSingleQuotes(status)]) {
    if(proj.Description__c.contains('filter string')) {
        projects.add(proj);
    }
}

Answer 2

There is an Idea to Allow filtering on Long Text Area, so you can't. In a pinch, you could iterate over your results in a for loop and use String methods to filter out/in the desired results. Of course, that may not be reasonable depending on the number of records and whether or not what you are doing is needed in real-time.

Answer 3

I just discovered a work around for this here. Create a Formula field that uses the LEFT formula and filter on that. You cannot filter on the ENTIRE long text but you can filter on part of it:

Legal_Street__c is a long text area. I created a formula field called StreetConc__c:

LEFT(Legal_Street__c, 40)

I then created a dummy record and put the text 'testingthis' in the Legal_Street__c field. From here I filtered on this formula field.

System.debug('answer = ' + [SELECT Name FROM Account WHERE StreetConc__c LIKE 'testing%']);

You should be able to apply this to both SOQL and SQL.

Answer 4

The other options here are good for using logic during page render or during data processing. But if you have a lot happening at this stage of your process these could be heavy. (maybe maybe not).

Another option which will alow you to filter on your situation and reduce the number of records returned.

Use a BeforeTrigger when saving each record to set a boolean or smaller text field. Then query/filter on that new boolean or text.

TRIGGER:

trigger MyObjectBeforeInsertUpdate on Booking__c (before insert, before update) {
    for(myObject__c item:objectList){
        item.hasBoolean__c = !string.ISBLANK(item.largeTextField__c);
    }
}//code not tested.

Answer 5

This is an old thread but I hope my SOSL solution can help others. Actually, we can use SOSL as a workaround to turn Textarea field types into filterable/searchable field. See code sample below

String fieldValue = 'somevalue';
String fieldName = 'MyTextAreaField__c';
String query =  = 'FIND {"'+ fieldValue + '"}  RETURNING ' +
                  ' YourCustomObject__c (id, ' + fieldName + ' ) LIMIT 2000';
List<List<SObject>> searchList = Search.query(query);//Return List of SObject list
system.debug('Search result: ' + searchList[0]);//Get search result
if(searchList[0].size() > 0) {
   //You can cast your result and return other fields if needed
   List<YourCustomObject__c > objSearchResultsList = (List<YourCustomObject__c >) searchList[0];
   system.debug(fieldName +' with "' + objSearchResultsList[0].get(fieldName) + '" found. ');
 }

If your search result has record, the sample output will be:

Search result: (YourCustomObject__c:{Id=aeb02000000000LXXX, MyTextAreaField__c=somevalue})   
MyTextAreaField__c with "somevalue" found.