I am looking to create a search bar like function in my LWC component where users will be able to add records to a list based on criteria entered into a Search input component.
My issue is where it comes to the SOQL and how I would structure it to return the most relevant results based on the string entered. I want the results based on around 4 fields on the records: record name, contact name (lookup), bedspace name (lookup), and accommodation (name).
It would work so that if the user knows the contacts name it would pull back any child records, then if the user puts in the accommodation or bedspace related record name it would further filter the records down, basically how the search bar in the standard salesforce system works.
The current code I have is:
List<String> stringFilterList = searchStr.split(' '); List<String> stringFilterListFormatted = new List<String>(); for(String str: stringFilterList) stringFilterListFormatted.add('%' + str + '%'); List<Clients_Accommodation__c> clientsAccommodations = [SELECT Name, Date_moved_in__c, Date_moved_out__c,Connected_Bed_Space__c, Connected_Bed_Space__r.Name, Client__c, Client__r.Name, Status__c, Accomodation__c, Accomodation__r.Name FROM Clients_Accommodation__c WHERE ((Client__r.Name LIKE :stringFilterListFormatted) OR (Name LIKE :stringFilterListFormatted) OR (Connected_Bed_Space__r.Name LIKE :stringFilterListFormatted) OR (Accomodation__r.Name LIKE :stringFilterListFormatted)) ORDER BY NAME DESC LIMIT 3];
This may not be possible simply or at all, however it would be great to hear of a way to do it.