John Davidson

Message:

I am building a sort of urban dictionary for the language of my home island. I have this kind of set up in the database:

table: words
table: word_definitions (has a word_id column)

I am just using Eloquent to the pattern matching in a scope function on my Word.php model.

public function scopeWhereLike(Builder $query, string $searchString): Builder

{

    $lowercasedSearchString = Str::lower($searchString);



    return $query->where('word_definitions.definition', 'like', "%{$lowercasedSearchString}%")

        ->orWhere('word_definitions.example_sentence', 'like', "%{$lowercasedSearchString}%")

        ->orWhere('words.word', 'like', "%{$lowercasedSearchString}%");

}

I am not performing any kind of ordering on it at the moment.

The problem I am trying to solve is this:

If someone searches the word "wi" (meaning "we" in the language"), the first result isn't "wi", it returns other words that contains "wi" within their definitions first:

Is there a recognised pattern to be able to set up some kind of rules like "order 1: exact matching words, order 2: any other partial matched words, order 3: any other word definitions

---

My Answer:
>where('word', 'LIKE', '%' . $lowercasedSearchString . '%')
->orderByRaw("CASE WHEN word = ? THEN 1 WHEN word LIKE ? THEN 2 ELSE 3 END", [$lowercasedSearchString, $lowercasedSearchString . '%', $lowercasedSearchString])
->orderBy('word', 'asc');
}

This scope function will search for words that contain the search string and order the results based on how closely they match the search string. Words that exactly match the search string will be returned first, followed by words that start with the search string, and finally, words that contain the search string anywhere in the word.