Soql to return results on the basis of multiple filters

Question

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

Answer 1

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

Answer 2

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 + '%';