[SalesForce] Filtering by Long Text Area field in SOQL

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?

Best Answer

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);
    }
}

Related Solutions

[SalesForce] SOQL on Rich text fields long text

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 call 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.

NOTE: You don't need to use 40 for the LEFT in the formula field. It was just an example.

[SalesForce] SOQL UNION Operator workaround

Pretty sure you need two queries and a Comparable implementation. Luckily for you DISTANCE is supported in SELECT clauses.

The sorting wrapper can be fairly simple:

public class DistanceSorter implements Comparable
{
    final SObject record;
    final Decimal distance;
    public DistanceSorter(SObject record, Decimal distance)
    {
        this.record = record;
        this.distance = distance;
    }
    public Integer evaluate(Object instance)
    {
        DistanceSorter that = (DistanceSorter)instance;
        return (this.distance - that.distance).round(RoundingMode.UP);
        // the above implementation implementation sorts closest first
        // to reverse, just swap this and that
    }
}

Then you can do the requisite two queries:

List<DistanceSorter> wrappers = new List<DistanceSorter>();
for (Project__c record : [SELECT DISTANCE(...) distance FROM Project__c WHERE ...])
{
    wrappers.add(new DistanceSorter(record, (Decimal)record.get('distance'));
}
for (OtherObject__c record : [SELECT DISTANCE(...) distance FROM OtherObject__c WHERE ...])
{
    wrappers.add(new DistanceSorter(record, (Decimal)record.get('distance'));
}
wrappers.sort();

List<SObject> combinedRecords = new List<SObject>();
for (DistanceSorter wrapper : wrappers)
{
    combinedRecords.add(wrapper.record);
}

Best Answer

Related Solutions

[SalesForce] SOQL on Rich text fields long text

[SalesForce] SOQL UNION Operator workaround

Related Topic