0

I am trying to return the account records on the basis of some search keywords like AccountName, Billing Street, BillingState, BillingCity and PostalCode. I am able to return the results but not able to filter the accounts with the combination of multiple keywords. e.g. if there are three accounts in system as below:

   Name   BillingState  BillingCity  PostalCode
1. Test1  Alaska        Anchorage    99501
2. Test2  Arizona       Phoenix      85001
3. Test1  Arkansas      LittleRock   72201

If i enter in keywords as Test in AccountName and Phoenix in BillingCity, it returns all the three accounts but the expectation is it should return only Test2 record. 

    @auraEnabled
public static List <Account> fetchAccountRecords(String searchKeyWord, String searchKeyWord1, String 
searchKeyWord2, String searchKeyWord3, String searchKeyWord4) { 
    String searchkey = '%' + searchKeyWord + '%';
    String searchkey1 = '%' + searchKeyWord1 + '%';
    String searchkey2 = '%' + searchKeyWord2 + '%';
    String searchkey3 = '%' + searchKeyWord3 + '%';
    String searchkey4 = '%' + searchKeyWord4 + '%';
    List < Account > lstOfAccount = 
                        [Selectid,Name,BillingStreet,Type,BillingCity,BillingState,BillingPostalCode,
                         RecordType.Name,Territory__r.Name from Account where (Name LIKE: searchkey 
                         OR BillingStreet LIKE: searchkey1 OR BillingCity LIKE: searchkey2 OR 
                         BillingState LIKE: searchKey3 OR  BillingPostalCode LIKE: searchkey4)]; 
    return lstOfAccount;
}
Improve this question
1
  • Looks like you need to change "OR" to "AND" in your matching. – Phil W Apr 21 '20 at 16:43
2

If you want your query to match all filters which have been specified, you should switch to dynamic SOQL so you can add clauses as needed.

List<String> fields = new List<String>
{
    'Name', 'BillingStreet', 'etc.'
};
List<String> filters = new List<String>();
if (searchkey != null)
{
    searchkey = '%' + searchkey + '%';
    filters.add('Name LIKE :searchkey ');
}
if (searchkey1 != null)
{
    searchkey1 = '%' + searchkey1 + '%';
    filters.add('BillingStreet LIKE :searchkey1 ');
}
// etc.
String soql = String.format('SELECT {0} FROM Account WHERE {1}', new List<String> {
    String.join(fields, ', '), String.join(filters, ' AND ')
});
return Database.query(soql);
Improve this answer
4
  • I tried with the logic you provided, but i am not able to retrieve any record because i have put all the fields blank except name. Just wonder how can i deal with the nulls in order to retrieve the records. – SFDCKASH Apr 21 '20 at 17:45
  • If you have put all fields null except name, then your query string would look like `SELECT ... FROM Account WHERE Name LIKE '%searchTerm%'. – Adrian Larson♦ Apr 21 '20 at 17:50
  • Thanks for the reply. I am just saying that while testing i put all the fields blank except Name and it is returning any record . Now, even if i put some value in two fields and will keep others blank still other three will be null and it wont return any records. So my concern is how can we ignore the nulls or how can we retrieve the records because tomorrow a user may put one, two , three or four values in the fields and it should retrieve the record. – SFDCKASH Apr 21 '20 at 17:55
  • Thanks @Adrian Larson. I put some more null checks with searchkeys and it worked properly. if (searchkey != '%null%' && searchkey != '' && searchkey != null && searchkey != '%%' ) { searchkey = '%' + searchkey + '%'; filters.add('Name LIKE :searchkey'); } – SFDCKASH Apr 25 '20 at 9:45
0

Your matching is based on "OR", so any result matching at least one of the entered values will be returned. If you want only those that match all the conditions you should change the query to:

SELECT Id, Name, BillingStreet, Type, BillingCity, BillingState, BillingPostalCode,
        RecordType.Name, Territory__r.Name
    FROM Account
    WHERE (Name LIKE: searchkey
               AND BillingStreet LIKE :searchkey1
               AND BillingCity LIKE :searchkey2
               AND BillingState LIKE :searchKey3
               AND BillingPostalCode LIKE :searchkey4)

Use @Adrian Larson's version if you want search keys to be optional without the added overhead of a '%%' LIKE match. To handle null or empty inputs make sure this is converted to just percent along the lines of:

String searchKey = String.isBlank(input) ? '%' : '%' + input + '%';
Improve this answer
10
  • Except in the example given, no filter value was specified for searchKey2, so this approach would yield no records. Wait, you'd just be searching for double wildcard. – Adrian Larson♦ Apr 21 '20 at 16:51
  • Which would do the job, right? – Phil W Apr 21 '20 at 16:52
  • 1
    Yeah just more likely to hit selectivity threshold, depends on org size I guess. Though if the filter values are null rather than empty string, you'd instead search '%null%', which would be unlikely to work. – Adrian Larson♦ Apr 21 '20 at 16:53
  • Might just want to add a bit of code that would take a searchKey = null and convert it to LIKE '%' instead of LIKE '%null%'. – Adrian Larson♦ Apr 21 '20 at 17:02
  • @PhilW i tried to replace the null with '%' still no luck : searchKey = String.isBlank(searchkey) ? '%' : '%' + searchkey + '%'; filters.add('Name LIKE :searchkey'); ..likewise 1st,2nd,3rd searchKey4 = String.isBlank(searchkey4) ? '%' : '%' + searchkey4+ '%'; filters.add('BillingStreet LIKE :searchkey4'); String soql = String.format('SELECT {0} FROM Account WHERE {1}', new List<String> { String.join(fields, ', '), String.join(filters, ' AND ') }); return Database.query(soql); – SFDCKASH Apr 21 '20 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.