When our app is smaller – for example at the beginning – it’s enough to apply small, handmade solutions. It’s true for database searching as well. It’s not necessary to pull in a package instantly. Most of the time we can use some simple techniques to perform searches in Eloquent.
Warming Up
Searching is a vital part of any application. A good interface helps the user to retrieve the information it needs. So it’s essential to bring a good solution both on front-end and back-end. In this post, we are not covering any UI or front-end related topics. We already published an article about that.
So for now, let’s talk about MySQL and Eloquent only.
The Simple Where Clauses
There are no new things here. We can use where clauses to compare columns to the given value. Nothing extra.
<?php $results = Post::where('title', 'foo')->get();
But to take a step further, we can pass an array to check multiple columns. This works like the && operator. We get the records where both the conditions evaluated true:
<?php $results = Post::where([ ['title', '=', 'foo'], ['published', '=', true], ])->get();
If we want, we can build queries that work like the || operator. All we have to do to use the orWhere() method on the query builder.
<?php $results = Post::where('title', 'foo')->orWhere('description', 'foo')->get();
Using the Like Keyword
To take a step further, let’s take a look at the LIKE MySQL keyword. The point is, we have a lot more flexibility than a regular equality check. We can use wildcard expressions, and those make things a lot more fun. We can use the % character for wildcards:
<?php // We can use a variable $keyword = 'foo'; // Starts with 'foo', ends with anything $results = Post::where('title', 'like', "{$keyword}%")->get(); // Ends with 'foo', starts with anything $results = Post::where('title', 'like', "%{$keyword}")->get(); // Contains 'foo' $results = Post::where('title', 'like', "%{$keyword}%")->get();
As you see, we have much more flexibility here. We can check, if the value starts, ends with the given string, or if it contains it at all. Imagine, you don’t remember the whole expression, only a fragment. But still, you type the few letters you know and even get the result you need!
And of course, you can use the same where clauses/combinations that you find in the docs!
Searching in JSON Columns
The JSON type brought a huge flexibility which is awesome. Also, we can perform queries easily with Eloquent, since it has a nice JSON support. But a big note before we dive deeper: even if you have a case-insensitive collation, JSON columns are case-sensitive.
If we would not have this issue, we could do the following:
<?php $results = Post::where('meta->description', 'like', '%foo%')->get();
Nothing special, right? But what if we have to handle it as it would be case-insensitive? Now, this is the perfect place to get to know the whereRaw() expression. Let’s see the code, then we’ll explain:
<?php $keyword = 'foo'; $results = Post::whereRaw('lower(meta->"$.description") like lower(?)', ["%{$foo}%"]);
You may notice a few things here. First of all the syntax is not that clean like above, because we are passing a raw expression here and not a sanitized one.
The other thing is the ? sign in the second lower function. We call it parameter binding. It’s a nice way to protect yourself against vulnerable values. As you can see, we pass an array as the second parameter. The first item of the array goes for the first binding, the second one for the second binding and so on. This is how raw expressions work.
Now let’s move on the real point: we use lower() function because this way we can ensure case-insensitivity on JSON columns. We convert the given value, and the stored valued to lower case, then compare them. A little bit tricky solution, but works perfectly!
It “Sounds Like” …
Now let’s move on the last thing we want to cover today. What if the user mistypes the expression? It would be nice to check if is there any record in the database that is similar to the given expression. We can’t use the LIKE keyword here, but we don’t have to since we have the SOUNDS LIKE keyword.
Now we won’t cover how does it work, but if you are interested, you can read further here. It covers what you need to know. But for now, let’s see how can we use it.
<?php $results = Post::where('title', 'sounds like', 'mistpyed')->get();
Behind the scenes, it runs an algorithm that compares the similarity of the values. What is nice, we don’t really have to take care of it. We just pass the keyword to the where clause and waiting for the result. We get results that have similarity to the given expression, and not only those that are/contain the exact same.
Summary
As we see, Laravel offers a nice way to perform easy-to-implement, yet flexible solutions. We can combine where clauses, we can use raw expressions when we need, also we can use less-known keywords as well. All of these are supported by default. How neat!
If you use other approaches, be brave to let us know in the comment section!