Full-text search

Fulltext index

LIKE searches scan letter by letter and slow down as tables grow. A fulltext index flips the work around: the database pre-builds a word map once, then answers word searches from the map instead of re-reading every row.

Schema::table("posts", function (Blueprint $table) { $table->fulltext(["title", "body"]); });

whereFullText

The whereFullText method asks the database for rows that contain your words, most relevant first. Pass the indexed columns and the search text, then use the result like any other collection of models.

$hits = Post::whereFullText(["title", "body"], "fresh bread")->get(); foreach ($hits as $post) { echo $post->title . "\n"; }

Ranking note

Fulltext search ranks by relevance, not by date or id. A post that repeats your words in the title outranks one that mentions them once at the bottom, so the first page of results already reflects what the reader most likely wants.

Do not promise exact ordering to users beyond that promise. Relevance is a score, not a sort key - phrase it as best matches first and nobody will be surprised.

Keep a scope

Wrap the query in a local scope so every search in the project spells the columns the same way. One scope means one place to add a column later, instead of a hunt through controllers.

public function scopeSearch($query, string $term) { return $query->whereFullText(["title", "body"], $term); } $hits = Post::search("fresh bread")->get();

Next: Capstone: blog

Article author: Arthur Isaev