Do not speak Portuguese? Translate this site with Google or Bing Translator
Laravel eloquent searching

Posted on: February 08, 2024 12:00 AM

Posted by: Renato

Views: 123

Laravel eloquent searching

 
 
13 min read Mar 9, 2023
Laravel eloquent search

In many projects that I have worked on, in one way or another, I needed to build a simple and lightweight search.

In this article, I would like to go in-depth on how to efficiently and securely perform search queries into MySQL using laravel eloquent.

Some search criteria that we will implement will be a search of users by first name, last name, and company.

Installing Laravel

Install a new laravel instance (You can also apply this approach on an existing project as well).

composer create-project laravel/laravel --prefer-dist laravel-search

Then go inside the laravel application you just created: cd laravel-search. We will also install barryvdh/laravel-debugbar and erlandmuchasaj/sanitize to debug MySQL queries and sanitize user input respectively.

composer require barryvdh/laravel-debugbar --dev
composer require erlandmuchasaj/sanitize

Now we need to Connect to a database, add/update migration, and add a factory and a seeder so we have some data that we can play with.

Connect to DB

Update the .env file with your database credentials. I will not go into details on how to create a database since this is not in the scope of this article

Connect laravel to mysql database
Connect laravel to MySQL database

Update migration.

Let’s tweak the user migration file that is located at database/migrations/xxxx_create_users_table.php a little bit:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('first_name'); // <==
            $table->string('last_name'); // <==
            $table->string('email')->unique();
            $table->string('company')->nullable(); // <==
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('users');
    }
};

Update factory.

Now we modify the user factory (database/migrations/UserFactory.php) to reflect changes made to the database.

<?php

namespace Database\Factories;

use App\Models\User;
use Illuminate\Database\Eloquent\Factories\Factory;
use Illuminate\Support\Str;

/**
 * @extends Factory<User>
 */
class UserFactory extends Factory
{
    /**
     * Define the model's default state.
     *
     * @return array<string, mixed>
     */
    public function definition(): array
    {
        return [
            'first_name' => fake()->firstName(), // <==
            'last_name' => fake()->lastName(), // <==
            'email' => fake()->unique()->safeEmail(),
            'company' => fake()->company(), // <==
            'email_verified_at' => now(),
            'password' => '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', // password
            'remember_token' => Str::random(10),
        ];
    }

    /**
     * Indicate that the model's email address should be unverified.
     */
    public function unverified(): static
    {
        return $this->state(fn (array $attributes) => [
            'email_verified_at' => null,
        ]);
    }
}

Add seeder.

Update the seeder on the database/seeders/DatabaseSeeder.php file, we uncomment User::factory() and add some other use cases. It will look something like this:

<?php

namespace Database\Seeders;

use App\Models\User;
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     */
    public function run(): void
    {
       User::factory(500)->create();

       User::factory()->create([
            'first_name' => 'Erland',
            'last_name' => 'Muchasaj',
            'company' => 'EMCMS Corporation',
            'email' => '[email protected]',
        ]);

        User::factory()->create([
            'first_name' => 'Erland',
            'last_name' => 'Muchasaj',
            'company' => 'EMCMS Group',
            'email' => '[email protected]',
        ]);

        User::factory()->create([
            'first_name' => 'Erland',
            'last_name' => 'Doe',
            'company' => 'EMCMS LTD',
            'email' => '[email protected]',
        ]);

        User::factory()->create([
            'first_name' => 'Erland',
            'last_name' => 'Doe',
            'company' => 'EMCMS Corporation',
            'email' => '[email protected]',
        ]);

        User::factory()->create([
            'first_name' => 'john',
            'last_name' => 'Muchasaj',
            'company' => 'Pimlico LTD',
            'email' => '[email protected]',
        ]);

        User::factory()->create([
            'first_name' => 'john',
            'last_name' => 'Doe',
            'company' => 'Pimlico LTD',
            'email' => '[email protected]',
        ]);

        User::factory()->create([
            'first_name' => 'Bill',
            'last_name' => 'Gates',
            'company' => 'Microsoft Corporation',
            'email' => '[email protected]',
        ]);

        User::factory()->create([
            'first_name' => 'Tim',
            'last_name' => 'O\'Reilly',
            'company' => 'O\'Reilly Media Inc.',
            'email' => '[email protected]',
        ]);
    }
}

Now if we run: php artisan migrate:fresh --seed we will see the following error (at least in most cases)😱:

PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes")

But don't worry, there is an easy fix for this issue that can be found in laravel documentation here.
Update your /app/Providers/AppServiceProvider.php to contain the following line of code and run php artisan migrate:fresh — seed again.

AppServiceProvider.php

Yay!!..🥳 So far everything seems working. Noice.
Let’s not get carried away and proceed with the rest of the implementation.

Getting it working.

Let’s open up routes/web.php and add the route where we will show a table with the list of all users with a search bar. To keep things simple I will be using the same route for index and searching.

<?php

use App\Http\Controllers\SearchController;
use Illuminate\Support\Facades\Route;


Route::get('/users', SearchController::class)->name('users');

Next, let’s create an invokable SearchController.

php artisan make:controller SearchController -i
<?php

namespace App\Http\Controllers;

use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Contracts\View\View;
use Illuminate\Database\Eloquent\Builder;

class SearchController extends Controller
{
    /**
     * Handle the incoming request.
     */
    public function __invoke(Request $request): View
    {
        $q = $request->input('query');

        $query = User::query()
            ->latest()
            ->select(['id', 'first_name', 'last_name', 'email', 'company', 'created_at'])
            ->where(function (Builder $subQuery) use ($q) {
                $subQuery->where('first_name', 'like', '%'.$q.'%')
                    ->orWhere('last_name', 'like', '%'.$q.'%')
                    ->orWhere('company', 'like', '%'.$q.'%');
            });

        return view('users', [
            'users' => $query->paginate(),
        ]);
    }
}

We know that the search will need to access the request data, so I’ve injected that into the method. Laravel’s IoC container will auto-magically resolve this and make it available to the method. Likewise for the User model, which we’ll use to perform the search and return data to the view.

Now if we try and access this route http://localhost:8100/users we will see the missing view error:

missing view error

So let's go ahead and create that file on resources/views/users.blade.php and place the following code inside of it.

<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Users Search</title>
    <!-- Fonts -->
    <link rel="preconnect" href="https://fonts.bunny.net">
    <link href="https://fonts.bunny.net/css?family=figtree:400,600&display=swap" rel="stylesheet" />
    <!-- Styles -->
    <style>
        /* ! tailwindcss v3.2.4 | MIT License | https://tailwindcss.com */*,::after,::before{box-sizing:border-box;border-width:0;border-style:solid;border-color:#e5e7eb}::after,::before{--tw-content:''}html{line-height:1.5;-webkit-text-size-adjust:100%;-moz-tab-size:4;tab-size:4;font-family:Figtree, sans-serif;font-feature-settings:normal}body{margin:0;line-height:inherit}hr{height:0;color:inherit;border-top-width:1px}abbr:where([title]){-webkit-text-decoration:underline dotted;text-decoration:underline dotted}h1,h2,h3,h4,h5,h6{font-size:inherit;font-weight:inherit}a{color:inherit;text-decoration:inherit}b,strong{font-weight:bolder}code,kbd,pre,samp{font-family:ui-monospace, SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", "Courier New", monospace;font-size:1em}small{font-size:80%}sub,sup{font-size:75%;line-height:0;position:relative;vertical-align:baseline}sub{bottom:-.25em}sup{top:-.5em}table{text-indent:0;border-color:inherit;border-collapse:collapse}button,input,optgroup,select,textarea{font-family:inherit;font-size:100%;font-weight:inherit;line-height:inherit;color:inherit;margin:0;padding:0}button,select{text-transform:none}[type=button],[type=reset],[type=submit],button{-webkit-appearance:button;background-color:transparent;background-image:none}:-moz-focusring{outline:auto}:-moz-ui-invalid{box-shadow:none}progress{vertical-align:baseline}::-webkit-inner-spin-button,::-webkit-outer-spin-button{height:auto}[type=search]{-webkit-appearance:textfield;outline-offset:-2px}::-webkit-search-decoration{-webkit-appearance:none}::-webkit-file-upload-button{-webkit-appearance:button;font:inherit}summary{display:list-item}blockquote,dd,dl,figure,h1,h2,h3,h4,h5,h6,hr,p,pre{margin:0}fieldset{margin:0;padding:0}legend{padding:0}menu,ol,ul{list-style:none;margin:0;padding:0}textarea{resize:vertical}input::placeholder,textarea::placeholder{opacity:1;color:#9ca3af}[role=button],button{cursor:pointer}:disabled{cursor:default}audio,canvas,embed,iframe,img,object,svg,video{display:block;vertical-align:middle}img,video{max-width:100%;height:auto}[hidden]{display:none}*, ::before, ::after{--tw-border-spacing-x:0;--tw-border-spacing-y:0;--tw-translate-x:0;--tw-translate-y:0;--tw-rotate:0;--tw-skew-x:0;--tw-skew-y:0;--tw-scale-x:1;--tw-scale-y:1;--tw-pan-x: ;--tw-pan-y: ;--tw-pinch-zoom: ;--tw-scroll-snap-strictness:proximity;--tw-ordinal: ;--tw-slashed-zero: ;--tw-numeric-figure: ;--tw-numeric-spacing: ;--tw-numeric-fraction: ;--tw-ring-inset: ;--tw-ring-offset-width:0px;--tw-ring-offset-color:#fff;--tw-ring-color:rgb(59 130 246 / 0.5);--tw-ring-offset-shadow:0 0 #0000;--tw-ring-shadow:0 0 #0000;--tw-shadow:0 0 #0000;--tw-shadow-colored:0 0 #0000;--tw-blur: ;--tw-brightness: ;--tw-contrast: ;--tw-grayscale: ;--tw-hue-rotate: ;--tw-invert: ;--tw-saturate: ;--tw-sepia: ;--tw-drop-shadow: ;--tw-backdrop-blur: ;--tw-backdrop-brightness: ;--tw-backdrop-contrast: ;--tw-backdrop-grayscale: ;--tw-backdrop-hue-rotate: ;--tw-backdrop-invert: ;--tw-backdrop-opacity: ;--tw-backdrop-saturate: ;--tw-backdrop-sepia: }::-webkit-backdrop{--tw-border-spacing-x:0;--tw-border-spacing-y:0;--tw-translate-x:0;--tw-translate-y:0;--tw-rotate:0;--tw-skew-x:0;--tw-skew-y:0;--tw-scale-x:1;--tw-scale-y:1;--tw-pan-x: ;--tw-pan-y: ;--tw-pinch-zoom: ;--tw-scroll-snap-strictness:proximity;--tw-ordinal: ;--tw-slashed-zero: ;--tw-numeric-figure: ;--tw-numeric-spacing: ;--tw-numeric-fraction: ;--tw-ring-inset: ;--tw-ring-offset-width:0px;--tw-ring-offset-color:#fff;--tw-ring-color:rgb(59 130 246 / 0.5);--tw-ring-offset-shadow:0 0 #0000;--tw-ring-shadow:0 0 #0000;--tw-shadow:0 0 #0000;--tw-shadow-colored:0 0 #0000;--tw-blur: ;--tw-brightness: ;--tw-contrast: ;--tw-grayscale: ;--tw-hue-rotate: ;--tw-invert: ;--tw-saturate: ;--tw-sepia: ;--tw-drop-shadow: ;--tw-backdrop-blur: ;--tw-backdrop-brightness: ;--tw-backdrop-contrast: ;--tw-backdrop-grayscale: ;--tw-backdrop-hue-rotate: ;--tw-backdrop-invert: ;--tw-backdrop-opacity: ;--tw-backdrop-saturate: ;--tw-backdrop-sepia: }::backdrop{--tw-border-spacing-x:0;--tw-border-spacing-y:0;--tw-translate-x:0;--tw-translate-y:0;--tw-rotate:0;--tw-skew-x:0;--tw-skew-y:0;--tw-scale-x:1;--tw-scale-y:1;--tw-pan-x: ;--tw-pan-y: ;--tw-pinch-zoom: ;--tw-scroll-snap-strictness:proximity;--tw-ordinal: ;--tw-slashed-zero: ;--tw-numeric-figure: ;--tw-numeric-spacing: ;--tw-numeric-fraction: ;--tw-ring-inset: ;--tw-ring-offset-width:0px;--tw-ring-offset-color:#fff;--tw-ring-color:rgb(59 130 246 / 0.5);--tw-ring-offset-shadow:0 0 #0000;--tw-ring-shadow:0 0 #0000;--tw-shadow:0 0 #0000;--tw-shadow-colored:0 0 #0000;--tw-blur: ;--tw-brightness: ;--tw-contrast: ;--tw-grayscale: ;--tw-hue-rotate: ;--tw-invert: ;--tw-saturate: ;--tw-sepia: ;--tw-drop-shadow: ;--tw-backdrop-blur: ;--tw-backdrop-brightness: ;--tw-backdrop-contrast: ;--tw-backdrop-grayscale: ;--tw-backdrop-hue-rotate: ;--tw-backdrop-invert: ;--tw-backdrop-opacity: ;--tw-backdrop-saturate: ;--tw-backdrop-sepia: }.relative{position:relative}.mx-auto{margin-left:auto;margin-right:auto}.mx-6{margin-left:1.5rem;margin-right:1.5rem}.ml-4{margin-left:1rem}.mt-16{margin-top:4rem}.mt-6{margin-top:1.5rem}.mt-4{margin-top:1rem}.-mt-px{margin-top:-1px}.mr-1{margin-right:0.25rem}.flex{display:flex}.inline-flex{display:inline-flex}.grid{display:grid}.h-16{height:4rem}.h-7{height:1.75rem}.h-6{height:1.5rem}.h-5{height:1.25rem}.min-h-screen{min-height:100vh}.w-auto{width:auto}.w-16{width:4rem}.w-7{width:1.75rem}.w-6{width:1.5rem}.w-5{width:1.25rem}.max-w-7xl{max-width:80rem}.shrink-0{flex-shrink:0}.scale-100{--tw-scale-x:1;--tw-scale-y:1;transform:translate(var(--tw-translate-x), var(--tw-translate-y)) rotate(var(--tw-rotate)) skewX(var(--tw-skew-x)) skewY(var(--tw-skew-y)) scaleX(var(--tw-scale-x)) scaleY(var(--tw-scale-y))}.grid-cols-1{grid-template-columns:repeat(1, minmax(0, 1fr))}.items-center{align-items:center}.justify-center{justify-content:center}.gap-6{gap:1.5rem}.gap-4{gap:1rem}.self-center{align-self:center}.rounded-lg{border-radius:0.5rem}.rounded-full{border-radius:9999px}.bg-gray-100{--tw-bg-opacity:1;background-color:rgb(243 244 246 / var(--tw-bg-opacity))}.bg-white{--tw-bg-opacity:1;background-color:rgb(255 255 255 / var(--tw-bg-opacity))}.bg-red-50{--tw-bg-opacity:1;background-color:rgb(254 242 242 / var(--tw-bg-opacity))}.bg-dots-darker{background-image:url("data:image/svg+xml,%3Csvg width='30' height='30' viewBox='0 0 30 30' fill='none' xmlns='http://www.w3.org/2000/svg'%3E%3Cpath d='M1.22676 0C1.91374 0 2.45351 0.539773 2.45351 1.22676C2.45351 1.91374 1.91374 2.45351 1.22676 2.45351C0.539773 2.45351 0 1.91374 0 1.22676C0 0.539773 0.539773 0 1.22676 0Z' fill='rgba(0,0,0,0.07)'/%3E%3C/svg%3E")}.from-gray-700\/50{--tw-gradient-from:rgb(55 65 81 / 0.5);--tw-gradient-to:rgb(55 65 81 / 0);--tw-gradient-stops:var(--tw-gradient-from), var(--tw-gradient-to)}.via-transparent{--tw-gradient-to:rgb(0 0 0 / 0);--tw-gradient-stops:var(--tw-gradient-from), transparent, var(--tw-gradient-to)}.bg-center{background-position:center}.stroke-red-500{stroke:#ef4444}.stroke-gray-400{stroke:#9ca3af}.p-6{padding:1.5rem}.px-6{padding-left:1.5rem;padding-right:1.5rem}.text-center{text-align:center}.text-right{text-align:right}.text-xl{font-size:1.25rem;line-height:1.75rem}.text-sm{font-size:0.875rem;line-height:1.25rem}.font-semibold{font-weight:600}.leading-relaxed{line-height:1.625}.text-gray-600{--tw-text-opacity:1;color:rgb(75 85 99 / var(--tw-text-opacity))}.text-gray-900{--tw-text-opacity:1;color:rgb(17 24 39 / var(--tw-text-opacity))}.text-gray-500{--tw-text-opacity:1;color:rgb(107 114 128 / var(--tw-text-opacity))}.underline{-webkit-text-decoration-line:underline;text-decoration-line:underline}.antialiased{-webkit-font-smoothing:antialiased;-moz-osx-font-smoothing:grayscale}.shadow-2xl{--tw-shadow:0 25px 50px -12px rgb(0 0 0 / 0.25);--tw-shadow-colored:0 25px 50px -12px var(--tw-shadow-color);box-shadow:var(--tw-ring-offset-shadow, 0 0 #0000), var(--tw-ring-shadow, 0 0 #0000), var(--tw-shadow)}.shadow-gray-500\/20{--tw-shadow-color:rgb(107 114 128 / 0.2);--tw-shadow:var(--tw-shadow-colored)}.transition-all{transition-property:all;transition-timing-function:cubic-bezier(0.4, 0, 0.2, 1);transition-duration:150ms}.selection\:bg-red-500 *::selection{--tw-bg-opacity:1;background-color:rgb(239 68 68 / var(--tw-bg-opacity))}.selection\:text-white *::selection{--tw-text-opacity:1;color:rgb(255 255 255 / var(--tw-text-opacity))}.selection\:bg-red-500::selection{--tw-bg-opacity:1;background-color:rgb(239 68 68 / var(--tw-bg-opacity))}.selection\:text-white::selection{--tw-text-opacity:1;color:rgb(255 255 255 / var(--tw-text-opacity))}.hover\:text-gray-900:hover{--tw-text-opacity:1;color:rgb(17 24 39 / var(--tw-text-opacity))}.hover\:text-gray-700:hover{--tw-text-opacity:1;color:rgb(55 65 81 / var(--tw-text-opacity))}.focus\:rounded-sm:focus{border-radius:0.125rem}.focus\:outline:focus{outline-style:solid}.focus\:outline-2:focus{outline-width:2px}.focus\:outline-red-500:focus{outline-color:#ef4444}.group:hover .group-hover\:stroke-gray-600{stroke:#4b5563}@media (prefers-reduced-motion: no-preference){.motion-safe\:hover\:scale-\[1\.01\]:hover{--tw-scale-x:1.01;--tw-scale-y:1.01;transform:translate(var(--tw-translate-x), var(--tw-translate-y)) rotate(var(--tw-rotate)) skewX(var(--tw-skew-x)) skewY(var(--tw-skew-y)) scaleX(var(--tw-scale-x)) scaleY(var(--tw-scale-y))}}@media (prefers-color-scheme: dark){.dark\:bg-gray-900{--tw-bg-opacity:1;background-color:rgb(17 24 39 / var(--tw-bg-opacity))}.dark\:bg-gray-800\/50{background-color:rgb(31 41 55 / 0.5)}.dark\:bg-red-800\/20{background-color:rgb(153 27 27 / 0.2)}.dark\:bg-dots-lighter{background-image:url("data:image/svg+xml,%3Csvg width='30' height='30' viewBox='0 0 30 30' fill='none' xmlns='http://www.w3.org/2000/svg'%3E%3Cpath d='M1.22676 0C1.91374 0 2.45351 0.539773 2.45351 1.22676C2.45351 1.91374 1.91374 2.45351 1.22676 2.45351C0.539773 2.45351 0 1.91374 0 1.22676C0 0.539773 0.539773 0 1.22676 0Z' fill='rgba(255,255,255,0.07)'/%3E%3C/svg%3E")}.dark\:bg-gradient-to-bl{background-image:linear-gradient(to bottom left, var(--tw-gradient-stops))}.dark\:stroke-gray-600{stroke:#4b5563}.dark\:text-gray-400{--tw-text-opacity:1;color:rgb(156 163 175 / var(--tw-text-opacity))}.dark\:text-white{--tw-text-opacity:1;color:rgb(255 255 255 / var(--tw-text-opacity))}.dark\:shadow-none{--tw-shadow:0 0 #0000;--tw-shadow-colored:0 0 #0000;box-shadow:var(--tw-ring-offset-shadow, 0 0 #0000), var(--tw-ring-shadow, 0 0 #0000), var(--tw-shadow)}.dark\:ring-1{--tw-ring-offset-shadow:var(--tw-ring-inset) 0 0 0 var(--tw-ring-offset-width) var(--tw-ring-offset-color);--tw-ring-shadow:var(--tw-ring-inset) 0 0 0 calc(1px + var(--tw-ring-offset-width)) var(--tw-ring-color);box-shadow:var(--tw-ring-offset-shadow), var(--tw-ring-shadow), var(--tw-shadow, 0 0 #0000)}.dark\:ring-inset{--tw-ring-inset:inset}.dark\:ring-white\/5{--tw-ring-color:rgb(255 255 255 / 0.05)}.dark\:hover\:text-white:hover{--tw-text-opacity:1;color:rgb(255 255 255 / var(--tw-text-opacity))}.group:hover .dark\:group-hover\:stroke-gray-400{stroke:#9ca3af}}@media (min-width: 640px){.sm\:fixed{position:fixed}.sm\:top-0{top:0px}.sm\:right-0{right:0px}.sm\:ml-0{margin-left:0px}.sm\:flex{display:flex}.sm\:items-center{align-items:center}.sm\:justify-center{justify-content:center}.sm\:justify-between{justify-content:space-between}.sm\:text-left{text-align:left}.sm\:text-right{text-align:right}}@media (min-width: 768px){.md\:grid-cols-2{grid-template-columns:repeat(2, minmax(0, 1fr))}}@media (min-width: 1024px){.lg\:gap-8{gap:2rem}.lg\:p-8{padding:2rem}}
    </style>
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-rbsA2VBKQhggwzxH7pPCaAqO46MgnOM80zW1RWuH61DGLwZJEdK2Kadq2F9CUG65" crossorigin="anonymous">
</head>
<body class="antialiased">
    <div class="relative sm:flex sm:justify-center sm:items-center min-h-screen bg-dots-darker bg-center bg-gray-100 dark:bg-dots-lighter dark:bg-gray-900 selection:bg-red-500 selection:text-white">
        <div class="container">
            <div class="row">
                <div class="col-12 py-5">
                    <div class="card my-5">
                        <div class="card-header">
                            <form action="{{ route('users')  }}">
                                <div class="mb-3">
                                    <label for="exampleInputEmail1" class="form-label">Search users</label>
                                    <input
                                        placeholder="ex: John Doe"
                                        value="{{ request()->get('query', '')  }}"
                                        type="text"
                                        name="query"
                                        class="form-control"
                                        id="exampleInputEmail1"
                                        aria-describedby="emailHelp">
                                    <div id="emailHelp" class="form-text">
                                        Search users by name and company.
                                        {{--                                           <b>total found</b>: {{ $user_count }}--}}
                                    </div>
                                </div>
                            </form>
                        </div>
                        <div class="card-body">
                            <table class="table border-collapse">
                                <thead>
                                <tr>
                                    <th>Name</th>
                                    <th>Company</th>
                                    <th>Email</th>
                                    <th>Created</th>
                                </tr>
                                </thead>
                                <tbody>
                                @forelse ($users as $user)
                                    <tr>
                                        <td>{{ $user->first_name }} {{ $user->last_name }}</td>
                                        <td>{{ $user->company }}</td>
                                        <td>{{ $user->email }}</td>
                                        <td>{{ $user->created_at?->diffForHumans() }}</td>
                                    </tr>
                                @empty
                                    <tr>
                                        <td colspan="4">No user found</td>
                                    </tr>
                                @endforelse
                                </tbody>
                                <tfoot>
                                <tr>
                                    <th>name</th>
                                    <th>company</th>
                                    <th>email</th>
                                    <th>created</th>
                                </tr>
                                </tfoot>
                            </table>
                        </div>
                        <div class="card-footer">
                            {{ $users->links() }}
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-kenU1KFdBIe4zVF0s0G1M5b4hcpxyD9F7jL+jjXkk+Q2h455rYXK/7HAuoJl+0I4" crossorigin="anonymous"></script>
</body>
</html>

And hit refresh and you will see the following image:

table list view of all users

So far so good. Let’s try different search criteria and see how it behaves. First, let us try and search by John: and we will see the following result:

Search result for “John”

Let us try John Doe:

Search result for “John Doe”

Hem… interesting 🤔. Let’s dig a little deeper (now the laravel-debugbar comes in handy and input sanitizer) and see what is the query. Click on the bottom left corner of the debug bar icon and click on the Queries tab. and you will see the following:

Search query
select count(*) as aggregate from `users` where (`first_name` like '%John Doe%' or `last_name` like '%John Doe%' or `company` like '%John Doe%')

As you can see the search criteria are being passed and checked agents' all 3 columns (first_name, last_name, and company).
Let’s change the search method to handle multiple words. Here we split the words whenever we find a space and we match each word against all 3 columns.

<?php

namespace App\Http\Controllers;

use App\Models\User;
use ErlandMuchasaj\Sanitize\Sanitize;
use Illuminate\Http\Request;
use Illuminate\Contracts\View\View;
use Illuminate\Database\Eloquent\Builder;

/**
 * Handle the incoming request.
 */
public function __invoke(Request $request): View
{
    $q = $request->input('query');
    
    $query = User::query()
        ->latest()
        ->select(['id', 'first_name', 'last_name', 'email', 'company', 'created_at']);
    
    $words = explode(' ', $q);
    foreach ($words as $term) {
    
        $word = Sanitize::sanitize($term); // <== clean user input
    
        $word = str_replace(['%', '_'], ['\\%', '\\_'], $word);
    
        $searchTerm = '%'.$word.'%';
    
        $query->where(function (Builder $subQuery) use ($searchTerm) {
            $subQuery->where('first_name', 'like', $searchTerm)
                    ->orWhere('last_name', 'like', $searchTerm)
                    ->orWhere('company', 'like', $searchTerm);
        });
    }
    
    return view('users', [
        'users' => $query->paginate(),
    ]);
}
The SQL query made to the Database
The resulting search for “John Doe” after fixing.

We can refactor this more fluently:

First, we wrap the array into a laravel collection, so we have access to all collection methods.
The filter() method is used to filter out all empty strings, spaces, null values, 0, and false, then we loop through each search term, sanitize it and build the query.

In the next article, I will show you how we can improve the performance of the search.

 
 
Laravel eloquent search
 
 

Let’s dig a little deeper and see what we can optimize.
I seeded the DB with 15k users, so I can test a little bit more the performance of the query.

Let’s make a query using the string “John Pimlico”(1) and we see the following result:

Search result for “John Pimlico”

As we can see the query is running at 128ms (3), but we can make it faster by introducing indexing to increase the performance of this query.

Looking at query (2), we can see that we use 3 columns for searching, first_name, last_name, and company.

Let’s go ahead and update the user migration at database/migrations/xxxx_create_users_table.php and index these 3 columns.

Adding indexes to users' table

Now let’s run our migration and our seeders again by running php artisan migrate:fresh --seed and see if this had any impact on our performance.

Search result for “John Pimlico” after the indexing

As you can see, there are not any significant improvements, even after we introduced indexing to the query.

Hem…Interesting! 🤔

Let’s see if the indexes have been added to the database or not. Go to phpmyadmin -> select the Database you are using -> select users table and the tab structure and scroll down to indexes.

Indexes of users' table

As you can see the indexes on all 3 columns are present, so how come these indexes have not helped at all?

In this kind of situation, is better to copy the query generated by laravel and analyze it in more detail in PHPMyAdmin (or in any other SQL system).

Copy query (3) and run explain on this query to see what is happening:

explain SQL query
the result of the explained query

Pay close attention to possible_keys and key columns from this result.
possible_keys represent indexes that can be used in a query and key-s represent indexes that are being used.
As you can see here, our indexes are not being used by this query.
One of the reasons is because of the wildcard (%) prefix we use in our query.

MySQL will not be able to utilize the index when you have a leading wildcard

So let’s remove the leading wildcard and run EXPLAIN again and see what happens.

explain after removing the leading wildcard

As you can see from the possible_keys and key columns, the indexes now are being used. So let us head back to our SearchController.php and fix it.

remove % wildcard

Now, let's run the query again and see how we are doing.

result after removing the % wildcard prefix.

As you can see, we have a total combined query of ~38ms from ~208ms (81.7% increase in overall performance) and a search query of 1.57ms, which is a huge improvement from ~96ms (98% increase in query speed 🏎).

It’s essential to notice that this change will limit us to only searching for strings that start with a specific string. In general, this is fine since this is how we search anyway, but it will introduce an edge case.

We will tackle this in more detail in the next article with some other tricks and tips.

In the previous article, we removed the leading % operator to be able to use indexes, and this introduces a new edge case.

We can no longer use multi-word search terms.
For example, if I would like to search for a user who belongs to a specific company, for the sake of example let's assume I want to search for users who work for EMCMS company.

Search result for “EMCMS”

As you can see so far everything seems fine. I see a list of all users who work for EMCMS company.

The issue occurs when we want to search for “EMCMS Corporation”.
Let us try a different search term (EMCMS Corp):

Search result for “EMCMS Corp”
A search query for multi-term strings.

As you might notice from the query, the reason why we don't get any results is that first, our query searches for companies whose name starts with emcms and then also whose name starts with corp which it can not find any match for.

Solution

The way how we can solve this is through search groups, where you add quotations around the word groups that you would like to keep together as one term when you search.

And as it turns out, there is an easy way to do just that in PHP using the str_getcsv function.

Let us head back again to SearchController.php and fix it.

search queries using string groups

So we remove explode and use str_getcsv.
Let’s give it another try grouping words with quotations (“EMCMS Corp”).

Search results using word groups
Another more specific example

Everything seems working correctly, Noice 😎.

It “Sounds Like” we are missing something 🤔

Now let’s move on to the last thing I want to cover in this article. What happens if the user mistypes a word?
It would be nice to check if is there any record in the database that is similar to the given string.

This is where SOUNDS LIKE come in. I won’t go into much detail on what SOUNDS LIKE does but you can read more about it here.

NOTE: This operator works best on strings in the English language (using it with other languages may return unreliable results)

Feel free to Subscribe for more content like this 🔔, clap 👏🏻 , comment 💬 and share the article with anyone you’d like

And as it always has been, I appreciate your support, and thanks for reading.

 

2

Share

Donate to Site


About Author

Renato

Developer

Add a Comment
Comments 0 Comments

No comments yet! Be the first to comment

Blog Search


Categories

OUTROS (15) Variados (109) PHP (127) Laravel (154) Black Hat (3) front-end (28) linux (110) postgresql (37) Docker (25) rest (5) soap (1) webservice (6) October (1) CMS (2) node (7) backend (13) ubuntu (54) devops (25) nodejs (5) npm (2) nvm (1) git (8) firefox (1) react (6) reactnative (5) collections (1) javascript (6) reactjs (7) yarn (0) adb (1) Solid (2) blade (3) models (1) controllers (0) log (0) html (2) hardware (3) aws (14) Transcribe (2) transcription (1) google (4) ibm (1) nuance (1) PHP Swoole (5) mysql (28) macox (4) flutter (1) symfony (1) cor (1) colors (2) homeOffice (2) jobs (3) imagick (2) ec2 (1) sw (1) websocket (1) markdown (1) ckeditor (1) tecnologia (14) faceapp (1) eloquent (14) query (3) sql (36) ddd (3) nginx (9) apache (4) certbot (1) lets-encrypt (3) debian (11) liquid (1) magento (2) ruby (1) LETSENCRYPT (1) Fibonacci (1) wine (1) transaction (1) pendrive (1) boot (1) usb (1) prf (1) policia (2) federal (1) lucena (1) mongodb (4) paypal (1) payment (1) zend (1) vim (3) ciencia (6) js (1) nosql (1) java (1) JasperReports (1) phpjasper (1) covid19 (1) saude (1) athena (1) cinnamon (1) phpunit (2) binaural (1) mysqli (3) database (37) windows (6) vala (1) json (2) oracle (1) mariadb (4) dev (12) webdev (24) s3 (4) storage (1) kitematic (1) gnome (2) web (2) intel (3) piada (1) cron (2) dba (13) lumen (1) ffmpeg (2) android (2) aplicativo (1) fedora (2) shell (4) bash (3) script (3) lider (1) htm (1) csv (1) dropbox (1) db (3) combustivel (2) haru (1) presenter (1) gasolina (1) MeioAmbiente (1) Grunt (1) biologia (1) programming (21) performance (3) brain (1) smartphones (1) telefonia (1) privacidade (1) opensource (3) microg (1) iode (1) ssh (3) zsh (2) terminal (3) dracula (1) spaceship (1) mac (2) idiomas (1) laptop (2) developer (36) api (4) data (1) matematica (1) seguranca (2) 100DaysOfCode (9) hotfix (1) documentation (1) laravelphp (9) RabbitMQ (1) Elasticsearch (1) redis (2) Raspberry (4) Padrao de design (4) JQuery (1) angularjs (4) Dicas (36) Kubernetes (2) vscode (2) backup (1) angular (3) servers (2) pipelines (1) AppSec (1) DevSecOps (4) rust (1) RustLang (1) Mozilla (1) algoritimo (1) sqlite (1) Passport (1) jwt (4) security (2) translate (1) kube (1) iot (1) politica (2) bolsonaro (1) flow (1) podcast (1) Brasil (1) containers (2) traefik (1) networking (1) host (1) POO (2) microservices (2) bug (1) cqrs (1) arquitetura (2) Architecture (3) sail (3) militar (1) artigo (1) economia (1) forcas armadas (1) ffaa (1) autenticacao (1) autorizacao (2) authentication (4) authorization (2) NoCookies (1) wsl (4) memcached (1) macos (2) unix (2) kali-linux (1) linux-tools (5) apple (1) noticias (2) composer (1) rancher (1) k8s (1) escopos (1) orm (1) jenkins (4) github (5) gitlab (3) queue (1) Passwordless (1) sonarqube (1) phpswoole (1) laraveloctane (1) Swoole (1) Swoole (1) octane (1) Structurizr (1) Diagramas (1) c4 (1) c4-models (1) compactar (1) compression (1) messaging (1) restfull (1) eventdrive (1) services (1) http (1) Monolith (1) microservice (1) historia (1) educacao (1) cavalotroia (1) OOD (0) odd (1) chatgpt (1) openai (3) vicuna (1) llama (1) gpt (1) transformers (1) pytorch (1) tensorflow (1) akitando (1) ia (1) nvidia (1) agi (1) guard (1) multiple_authen (2) rpi (1) auth (1) auth (1) auth (1) auth (1) Oh My Zsh (1) Manjaro (1) BigLinux (2) ArchLinux (1) Migration (1) Error (1) Monitor (1) Filament (1) LaravelFilament (1) replication (1) phpfpm (1) cache (1) vpn (1) l2tp (1) zorin-os (1) optimization (1) scheduling (1) monitoring (1) linkedin (1) community (1) inteligencia-artificial (2) wsl2 (1) maps (1) API_KEY_GOOGLE_MAPS (1)

New Articles



Get Latest Updates by Email