SOQL Query for a search bar, where records are returned based on multiple fields using only 1 input string

Question

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.

Answer 1

Searching on multiple fields and ranking by relevance are both features that would make SOSL a better tool for the job here. You don't have perfect control over which fields are searched or how relevance is calculated, but you also can basically just one-line it and performance should be significantly better.

List<Clients_Accommodation__c> clientsAccommodations = [
    FIND ...
    IN NAME FIELDS
    RETURNING (SELECT ... FROM Clients_Accomodation__c)
][0];