3

I'm looking a way to provide a user ability to search on multiple fields like the user can search on Name or Description or Make or Model etc..

I have the following code in my apex

public PageReference Find()
{
    String TempSearchText;
    list<AssetEntry__c> AssetList;

    TempSearchText = '%' + SearchText + '%';
    system.debug('tempSearchText  ' + TempSearchText );

    AssetList = [SELECT id,name,Description__c,Make__r.Name,Model__r.Name,Model_Year__c 
                 FROM AssetEntry__c WHERE name like :TempSearchText 
                 OR  Description__c like :TempSearchText];

    //do more work with the result        
    return null;
  }

when I search for the description it does not find any thing related to description and I know that there is data in the object, when I looked at the debug, this what I see..

|DEBUG|TempSearchText  %monitor%
|DEBUG|SELECT id, name, Description__c, Make__r.Name, Model__r.Name, Model_Year__c FROM AssetEntry__c WHERE (name LIKE :tmpVar1 OR Description__c LIKE :tmpVar2)

|SOQL_EXECUTE_END|[139]|Rows:0

Why is it not returning the data? is my SOQL is wrong?

6
  • do you get any data when you take the where clause of the query from the soql and is description__c a long text?
    – Rao
    Commented Aug 7, 2015 at 17:02
  • yes i do get the data when i take out the WHERE clause
    – Nick
    Commented Aug 7, 2015 at 17:03
  • what is the data type of the description__c field?
    – Rao
    Commented Aug 7, 2015 at 17:04
  • its a string field
    – Nick
    Commented Aug 7, 2015 at 17:04
  • can you post a screenshot of some data where description has a keyword like monitor ? can't see any issue with the query and your debug clearly shows %monitor%
    – Rao
    Commented Aug 7, 2015 at 17:16

4 Answers 4

2

I've implemented something similar to the following.

 public PageReference myMethod() {
     list<AssetEntry__c> myList = new list<AssetEntry__c>();     
     string searchText = 'some text';
     string soql = 'select name, id, from AssetEntry__c where name != null';

     if (!searchText.equals(''))
       soql += ' and name LIKE \''+String.escapeSingleQuotes(searchText)+'%\'';

     myList = Database.query(soql + ' limit 100');
     system.debug('*** My List values ' + myList);

   return null;
   }
1
  • The OP asked about multiple string fields, not a single field. I am having the same problem where querying one fields is fine, but chaining multiple LIKE statements with OR isn't.
    – Shanerk
    Commented May 20, 2019 at 23:56
1

I would try using https://workbench.developerforce.com to build out your SOQL. It helps me out of binds like this all the time.

1

There is a syntax requirement that seems to be undocumented.

This will fail to return results:

SELECT id FROM foo WHERE (field1 LIKE '%bar' OR field2 LIKE '%bar')

But the correct syntax is as follows and will search both (or N) fields:

SELECT id FROM foo WHERE ((field1 LIKE '%bar') OR (field2 LIKE '%bar'))

Each LIKE term must be inside its own set of parens.

0

You can use parentheses to define the order in which fieldExpressions are evaluated

StringTempSearchText = '%' + SearchText + '%';

List<Asset> assetList = [SELECT Id, Name, Description__c,
                                FROM AssetEntry__c 
                                WHERE ( Name like :TempSearchText 
                                    OR  Description__c like :TempSearchText)
                                    AND Make__c =: makeobjId Order By CreatedDate DESC];

For Ex: Table:

Name Description
Smith Aus Cric
Gill Aus Cric
Sachin Ind Cric
ABD SA Cric

If Search Keyword is 'Aus Cric',

our backend SOQL will be:

SELECT Id, Name, Description__c,
FROM AssetEntry__c 
WHERE ( Name like '%Aus Cric%' OR Description__c like '%Aus Cric%' ) 
AND Make__c = '0e2____' Order By CreatedDate DESC]

So our result we be:

Smith              Aus Cric
Gill               Aus Cric

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .