In this lesson, we're going to learn how to do full text searching with rankings. To illustrate this, I've set up a page which lists all the Post in this app, which has been seeded with 500 Posts. I've used lorem ipsum placeholder content for the titles and the body content, with the exception of three Posts. If we look at the database seeder, we can see that I've updated three Posts to include information about foxes. The first one simply has some information about foxes from Wikipedia. The second includes the famous quick brown fox sentence. And the third has a quote from the Fox and the Hound Disney movie.

The second includes the famous quick brown fox sentence. And the third has a quote from the Fox and the Hound Disney movie. Let's update this page so that when we run a search, it finds matching Posts against the title and the body content. And this is an excellent candidate for full text searching, since we're essentially searching documents when we're searching the body content of the Posts. The first thing we need to do is update our posts table migration to create a full text search index. Since the Laravel migrations don't support the creation of full text indexes,

to create a full text search index. Since the Laravel migrations don't support the creation of full text indexes, we'll need to do this manually by running a database statement. Create full text index. And then we need to provide a name for our index, which we'll just call posts_full_text_index. And then we'll specify which table and columns to create the index on. And finally, we'll instruct MySQL to use the Ngram parser. Ngram support was added to MySQL in version 5.7.6. It's similar to Trigrams and Postgres,

Ngram support was added to MySQL in version 5.7.6. It's similar to Trigrams and Postgres, and from my experience, leads to better results than the default parser. Okay, let's rerun our migrations in Seeder. Now let's go to TablePlus and do some experimenting. First, if we go to our post table, we can see our new posts full text index has been created, which is using the full text search algorithm. Now let's write a full text search query. We'll select the ID and title columns from the post table,

Now let's write a full text search query. We'll select the ID and title columns from the post table, where we have a full text match in the title and the body against the term Fox, and we'll do this in the BOOLEAN mode. MySQL offers different full text searching modes. One of those is the BOOLEAN mode, and another is a natural language mode. From my experience, the BOOLEAN mode leads to better search results. Okay, let's run this query. Amazing, that works. We're getting our three Fox posts in our search results.

Amazing, that works. We're getting our three Fox posts in our search results. Let's update our query to also get the full text match score as well. To do this, we can copy our match statement from the where statement and add it to our select statement, giving it an alias of score. And if we run the query again, we can see that our score column has been added with a match ranking for each row. Note, by default, MySQL will automatically order a full text search by the score value descending if no other ordering is in place. Okay, just to prove that this is working properly,

by the score value descending if no other ordering is in place. Okay, just to prove that this is working properly, let's update our search term to Quick Brown Fox. Currently, the blog post that has that sentence is in last place. However, if we run this again, we can see that it's now in first place. Cool, right? Okay, let's bring this to our app. Let's go to our PostController and start by commenting out the publishedAt orderBy statement. Next, let's add a when condition to our query.

the published at order by statement. Next, let's add a when condition to our query. This will check to see if a search value has been provided in the request. And if one has, it will run the query we defined in the callback. Query whereRaw, where we have a full text match in the title and body columns against the term from our request. And again, we'll do this in Boolean mode. And finally, we'll pass in our search query as the binding. And now, if we go back to the browser and run our search for the fox term, we can see that it's working.

And now, if we go back to the browser and run our search for the fox term, we can see that it's working. Amazing. How simple was that? For fun, let's add our score to this query as well. Let's copy the match statement from the where statement and then add this using a raw select. We'll grab all the columns from the post table and then we'll paste in our match statement and alias it to the score column. And finally, we'll pass in the search term again as the binding.

and then we'll paste in our match statement and alias it to the score column. And finally, we'll pass in the searchTerm again as the binding. I've actually already added a check in our post.blade.php view for the existence of the score value. Meaning, if we've done this correctly, we should now see the score in our app. Let's go back to the browser and hit refresh. And sure enough, that's working. Now, let's update our search again to quick brown fox. And again, that's working. I hope this illustrates just how easy it is to get full text searching.

And again, that's working. I hope this illustrates just how easy it is to get full text searching working in your Laravel apps. Okay, one last thing before we wrap up this lesson. As I already mentioned, MySQL will automatically order full text searches by the score descending if there's no other ordering in place. Meaning, if we uncomment the published_at order by statement and hit refresh in the browser, we'll get the wrong ordering for our search. And we don't want that. However, we do want to have the published_at ordering in place.

And we don't want that. However, we do want to have the published_at ordering in place when we're not running a search. How can we do that without messing up our full text search ordering? Well, the when condition actually accepts a second callback as the third argument. And this callback is used when the truthy check fails. And in our case, that's when we have no search query. Let's try it. Let's just remove the existing published_at orderBy statement.

Let's try it. Let's just remove the existing published_at order by statement. And then let's add the second callback to our when statement. And finally, we'll add in our default published_at order by statement. And now, if we go back to the browser and hit refresh, we can see that our full text searching is being ordered properly again. And if we remove the search query, we can see that it's now ordering by the published date by default. Thank you for watching.