In this lesson, we're going to start a mini-series where we build out a search feature using just string matching with a like operator. Now imagine you have an application that has a User page like this. When someone types in a search term, we want to filter the results to only Users that have a match against their firstName, or their lastName, or their companyName. This application has two models, a User model and a Company model, where every User belongs to a Company. I have a UserController already set up, which grabs the Users from the database, eager loads the Companys for those Users, and then paginates the results. I've seeded the database with 10,000 Companys, and every Company has 10 Users, for a total of 100,000 Users. This should be enough data to help us identify any potential performance issues. The first thing we'll do is update our query in the UserController to call a new search scope. search, request, and we'll just get the search term from the query string. Now let's go to our User model and add this new scope.

Search, request, and we'll just get the search term from the query string. Now let's go to our User model and add this new scope. public function scopeSearch, and that'll take an instance of the QueryBuilder, as well as our search terms. Now, how exactly do we want this scope to work? One approach I like to take is to split up the search terms into individual keywords, and then check each one of those individual keywords against the database. For example, if we had the search term Bill Gates Microsoft, we would first look in the database for a User with a first name, last name, or company name that matches the term Bill, and then we would do two subsequent checks in the exact same way for the terms Gates and Microsoft. For a User to be included, each keyword must match at least one column, meaning the more keywords there are, the less results we'll get. Let's start by splitting up the search terms into keywords. We'll wrap it in a Collection just so we can do subsequent operations on it, and then we'll explode the search terms using a space as the delimiter.

Let's start by splitting up the search terms into keywords. We'll wrap it in a collection just so we can do subsequent operations on it, and then we'll explode the search terms using a space as the delimiter. We'll filter out any blank values, and then we'll iterate through each term to add the necessary query logic. As I mentioned, we're going to use the like operator for this, which is a form of pattern matching. By default, the like operator requires a full match. For example, if we were searching for John, that wouldn't match Jonathan. It would only match John. However, we can wrap each term within a wildcard symbol to do partial matching. Next, let's add the where conditions for the first and last names. Query where the firstName is like our search term or where the lastName is like our search term.

Next, let's add the where conditions for the firstName and lastName. Query where the firstName is like our search term or where the lastName is like our search term. The only thing we need to remember here is that we want to check each keyword in isolation, which means we need to wrap our orWhere conditions in a closure. Now, let's add the companyName to our scope. This, of course, is a little more tricky since this information doesn't exist in the users table, but rather in the companies table. One fairly straightforward approach is to use the orWhereHas Eloquent method for this. This method accepts the relationship name as the first argument and a closure as the second argument, which receives an instance of the query builder. From there, we just do the same check that we did for the firstName and lastName query where the name is like the search term. OK, our search scope is ready to go. Let's give it a try.

OK, our search scope is ready to go. Let's give it a try. Bill Gates, Microsoft. Perfect. That works. Let's have a closer look at the query we just generated. We start by selecting all the columns from the users table, and then we have this big massive where clause. This is what's being generated by our new search scope. We start by looking to see if the firstName matches the term bill and then the lastName. And then we look to see if the companyName for this user matches the same term. From there, we do the exact same check for the terms Gates and Microsoft.