Forem: Cosme Escobedo

Signed URLs with Laravel

Cosme Escobedo — Sun, 03 Apr 2022 02:12:19 +0000

In a project, you might need to generate unique URLs that perform some code where you need to make sure that the user didn't change the URL in any way. For example, unsubscribing a user from a newsletter, or maybe creating a link for a user to signup without a password, you might also want to offer a discount for your email subscribers only.

Whatever your use case might be, Laravel offers features to allow us to work with signed URLs.

Generating a signed URL

To generate a signed URL, the first thing we need to do is create a named route. For example, I'm going to create a named route to unsubscribe a user when they click on the link.

// routes/web.php
Route::get('/unsubscribe/{user}', [SubscriptionsController::class, 'unsubscribe'])
       ->name('unsubscribe');

Now, to generate a signed URL, we can use the URL facade:

use Illuminate\Support\Facades\URL;

return URL::signedRoute('unsubscribe', ['user' => 1]);

The first parameter to the signedRoute method is the name of the route. Here I'm using unsubscribed since that's the name that we gave the route above and the second parameter are the route parameters that we want to pass to the route, in this case, the user ID. This method will return a URL like this one:

https://yoursite.com/unsubscribe/1?signature=7594beb1a7af889d997eedc50f68eadb8b2e8d4097ba3a9ff1c600ffa8a02e49

As you can see, Laravel attached a signature parameter to the URL with a hash, this has represented the URL so if the user modifies any part of that URL the hash will be invalid.

Temporally signed URLs

You may also want to create a URL that expires after some time has passed, you can also create this type of URL with the temporarySignedRoute method

return URL::temporarySignedRoute(
    'unsubscribe', now()->addMinutes(30), ['user' => 1]
);

The second parameter to this method is the time at which the URL should expire, using this method an expires parameter will be added to the URL, and Laravel will report this URL as invalid.

Verifying the URL

We've now created the signed URL, but up to this point, we aren't verifying the hash. To do that, we can use the built-in middleware signed in our route. To do this, just add the middleware in your route declaration.

// routes/web.php
Route::get('/unsubscribe/{user}', [SubscriptionsController::class, 'unsubscribe'])
       ->name('unsubscribe')
       ->middleware('signed');

By adding this middleware, Laravel will automatically check the hash and throw a 403 HTTP code.

Personalizing the error page.

You may also want to customize the error that the user sees when the URL is invalid. That can be achieved by defining a custom renderable closure for the InvalidSignatureException:

// app\Exceptions\Handler.php
use Illuminate\Routing\Exceptions\InvalidSignatureException;

/**
 * Register the exception handling callbacks for the application.
 *
 * @return void
 */
public function register()
{
    $this->renderable(function (InvalidSignatureException $e) {
        return response()->view('error.link-expired', [], 403);
    });
}

Inside this closure, we can define what the user will see when visiting an invalid singed URL.

Verifying the URL without middleware

In some cases, you might want to verify the URL in your controller. You can do this by removing the singed middleware from your route definition and, instead, using the hasValidSignature in the request.

public function unsubscribe(Request $request)
{
    if (!$request->hasValidSignature()) {
        abort(401);
    }

    //...
}

This allows a little more flexibility if we need a custom experience.

5 ways to optimize your eloquent queries

Cosme Escobedo — Fri, 01 Apr 2022 15:13:53 +0000

Did you know that 1 in 4 visitors would abandon a website if it takes more than 4 seconds to load? While multiple factors can slow down a website, one common factor is inefficient database queries.

Eloquent is a great tool that makes it easy to interact with the database in our Laravel applications, but sometimes we forget that what looks like a method call or property is running a database query under the hood, which might lead to slow page loads or high memory usage. Here I some techniques that can improve your load times in your Laravel application.

1. Select only the columns you need

One way we can optimize a query is by reducing the amount of data we fetch from the database. When you make a query, the data that the database returns is sent over the network. The more data there is the more time it will take, but that's not all, all that data has to be stored in memory for the lifetime of the request and that can cause your server to slow down and run out of memory under heavy loads.

Fortunately, in Laravel, we can specify exactly the data we need. Let's say for example that we have an e-commerce website, and we want to display a list of products. Our controller would look something like this:

public function index()
{
    return view('products', [
        'products' => Products::query()->paginate()
    ]);
}

While doing this might seem reasonable (we are even paginating the results!), there may be a lot of information in our products' table that we don't need for a listing page (body, category id, product type, etc). You can reduce the amount of data by selecting only the columns you need:

public function index()
{
    return view('products', [
        'products' => Products::query()
                        ->select(['id', 'title', 'slug', 'thumbnail'])
                        ->paginate()
    ]);
}

This would make our query more efficient, this is especially important since the products' table would presumably contain a body column of type TEXT which could be quite large.

This technique is most effective in pages where you need to work with a ton of records, so you probably won't see a big change in memory usage/performance if you apply it to a page that fetches a few database records, nevertheless, it's still good to keep it in mind.

2. Beware of N+1 issues

Continuing with our e-commerce example, let's say that we need to display the brand that each product belongs to. Let's imagine we have a brand relationship in our Product model:

public function brand()
{
    return $this->belongsTo(Brand::class);
}

If we need to access the brand name in our blade template, it would look something like this:

@foreach ($products as $product)
    //...
    <p>{{ $product->brand->name }}</p>
    //...
@endforeach

Again, this looks fine. I have written code like these counties times in the past. However, there is a small issue here that might not be obvious. While we loaded all of our products into memory using one SQL query in our controller, the brands are retrieved from the database one by one in our forloop. If your relationship isn't loaded, Laravel will execute an SQL query to retrieve it from the database.

That issue is called "n+1", it's called that because we execute 1 SQL query to fetch our product and then N SQL queries to fetch the brands, N being the number of products.

Laravel offers a simple way to fix this issue using eager loading. Eager loading means that we want to retrieve all the related models before using them. Under the hood, Laravel will make only one SQL query to retrieve every related brand. So instead of making N+1 queries, we only make 2.

To use eager loading, you call the with method in your query

public function index()
{
    return view('products', [
        'products' => Products::query()
                        ->select(['id', 'title', 'slug', 'thumbnail'])
                        ->with('brand')
                        ->paginate()
    ]);
}

We can even combine the previous technique and this one by selecting only the columns we need from our relationship, we can do this by adding : followed by the columns we want to select from the relationship.

public function index()
{
    return view('products', [
        'products' => Products::query()
                        ->select(['id', 'title', 'slug', 'thumbnail'])
                        ->with('brand:id,name')
                        ->paginate()
    ]);
}

This way, Laravel will make sure to only select the id and name columns when eager loading the relationship.

3. Don't fetch all the records if you only need one

Let's say that in our store, we want to display all the users and show the total of the last order they made, we could do this in our blade template

@foreach($users as $user)
    <p>{{ $user->name }}</p>
    <p>{{ $user->orders()->latest()->first()->total }}</p>
@endforeach

Here we are simply iterating over all our users, then making a query to fetch the orders sorted by created_at (the latest() method takes care of that) and then we execute the query fetching only the first result, meaning the last order created, after that we simply access the total on that model.

You might have realized, however, that this is introducing an N+1 one issue. But we know how to fix that using eager loading!. Well, eager loading might not do what we want, if we are going to fetch all the orders into memory instead of only the most recent one.

There are a couple of ways to fix this in Laravel. One of them is by defining a hasOne relationship in our User model that retrieves only the last order created:

function lastOrder()
{
    return $this->hasOne(Order::class)->latestOfMany();
}

By using the method latestOfMany we tell Laravel that we only need the most recently created record, you can also use the oldestOfMany method if you want the oldest record instead.

Now that we have a new relationship, we can eager load it like any other relationship.

public function index()
{
    return view('users', [ 
        'users' => Users::query()
                        ->with('lastOrder')
                        ->get();
    ]);
}

And in our template now, we can fetch the total by accessing our loaded relationship.

@foreach($users as $user)
    <p>{{ $user->name }}</p>
    <p>{{ $user->lastOrder->total }}</p>
@endforeach

4. Use indexes

Database indexes can get pretty complicated if you want to know how they work, however you don't need to know too much about them to make use of them. In the simplest terms, indexes are sort of a look-up table that your database can consult when searching for a record, and using them can make your lookup queries a lot faster.

You can use indexes in Laravel by adding them to your migrations. Let's say that we expect our store users to search the products by name a lot, we could add an index query to the title of the products:

Schema::table('products', function (Blueprint $table) {
    $table->index('title');
});

By creating this migration, we can speed up our table. Keep in mind, however, this index will only be applied if we search the product using the whole title or the start of the title. In other words, the index will be applied if we run queries like this:

Product::where('title', '=', $search);
Product::where('title', 'like', $search . '%');

But it won't be applied if we try to match by using a like query like this:

Product::where('title', 'like', '%' . $search . '%');

This can be solved using a full-text index, there are other types of indexes which are outside the scope of this article.

5. Optimizing circular relationships

Let's say that our product model looks like this,

class Product extends Model
{
    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function url()
    {
        return URL::route('product', [
            'category' => $this->category->slug,
            'product' => $this->slug,
        ]);
    }
}

In this model, we have a helper function that uses the related category slug to generate the URL, a simple but useful method in our model. However, let's assume that we want to show all the products for a given category, our controller would look something like this:

 public function show(Category $category)
{
    $category->load('products'); // eager load the products

    return view('categories.show', ['category' => $category]);
}

Now let's say that we want to show the URL of the product on our view by calling the url method that we defined in the product model:

@foreach($category->products as $product)
    <li>
        <a href="{{ $product->url() }}">{{ $product->name }}</a>
    </li>
@endforeach

The problem here is that we've introduced an N+1 issue once again, when we call the url method in the product, we are making a query to fetch the category of each product even though we already have it!. One way to solve this is by eager loading the category in our products. We can do this by adding a .category to the load method call:

 public function show(Category $category)
{
    $category->load('products.category'); // eager load the products

    return view('categories.show', ['category' => $category]);
}

Now the N+1 issue is solved, but we are making two SQL queries to fetch the same category, once when the model gets injected into the show method in the controller and the other when we call the load method on that category. Fortunately, there is a way to avoid that by assigning the relationship directly using the setRelation method.

public function show(Category $category)
{
    $category->products->each->setRelation('category', $category);

    return view('categories.show', ['category' => $category]);
}

If you want to learn more about this technique, you can read this amazing article by Jonathan Reinink.

Conclusion

And that's it, I hope that you learned at least one new thing from this article and you found it valuable, and you can use them to make your website faster.

If you found this article valuable, make sure to follow me on Twitter, I post tips and other Laravel content there, also you can ask me any other questions you have about this or other Laravel topics.

Eloquent optimization: displaying only one record of a one-to-many relationship

Cosme Escobedo — Tue, 29 Mar 2022 02:26:23 +0000

Sometimes we need to display data for only one record in our one-to-many relationships. For example:

Displaying the title of the last video each user posted
Showing the most sold product in each category
Showing the last comment on every blog post
etc.

In this article, I want to go over the different ways we can do this and figure out the most efficient way in both terms of time and memory

The naive approach

In this article, I'm going to use the example of a platform where users can publish videos, and I want to show the title of the last video for each user.

One way to do this is retrieving the users in the controller and then in our blade template order the videos by the created_at date, grab the first and then show the title.

    return view('users', ['users' => User::get()]);

    @foreach($user as $users)
        <!-- Table html -->
        <td>{{ $user-> }}</td>
        <td>{{ $user->videos->sortByDesc('created_at')->first()->title }}</td>
    @endforeach

In this database, I seeded 30 users with 100 videos each to figure out the performance of the queries.

Using Laravel debugbar, I can see that we are making 32 queries to the database and loading 330 models into memory!

This is very inefficient for what we are doing. Let's fix this query.

Solving the n + 1 issue

If you are not familiar with n + 1 issues, what I'm referring to is that we are making one query per user, meaning that if we are showing 30 users, we would make 31 queries, if we are showing 100 users we will make 101 queries.

This is the issue we are currently facing with our application. One way that Laravel offers to fix this is by using eager loading. And we can do this by adding a with('videos') call to our eloquent query.

    return view('users', ['users' => User::with('videos')->get()]);

This simple change solves our n + 1 issues, If I look at the Laravel debugbar output now, I see that the queries we reduced to only 2!

This is a big improvement, but we are still loading 330 models into memory, meaning that we are retrieving all the videos for every user from the database. Let's fix that.

Retrieving only the last video

Fortunately, Laravel offers a simple way to create a relationship for the latest video, the relationship will look something like this in our User model:

    public function latestVideo()
    {
        return $this->hasOne(Video::class)->latestOfMany();
    }

This way we can retrieve only the last video from a user using $user->latestVideo, and since it's a relationship we can eager load it as well. So let's make use of our new relationship in our controller.

    return view('welcome', ['users' => User::with('latestVideo')->get()]);

And In our blade template:

    @foreach($user as $users)
        <!-- Table html -->
        <td>{{ $user-> }}</td>
        <td>{{ $user->latestVideo->title }}</td>
    @endforeach

Now If I check Laravel debugbar, we are only making 2 queries since we are using eager loading, and we are loading 60 models into memory, 30 users, and 30 videos.

But I still think we can make it a little better, what if we didn't have to load any video model and instead only fetched the title?

Fetching only the last video title

To fetch only the title of the latest video, we can use the addSelect method. Using this method, we can add a subselect that will be added as a column to our query. Here is what I came up with:

    $users = User::addSelect(['latest_video_title' => Video::query()
        ->select('title')
        ->whereColumn('user_id', '=', 'users.id')
        ->orderByDesc('created_at')
        ->limit(1) 
    ])->get();

    return view('users', ['users' => $users]);

In this query we select the title, we add a where to make sure the video belongs to the user order the results by the created_at column in descending order and then limit the results to only 1 to grab the first one.

This is not as easy to read as the previous method, so let's create a scope in our user model instead of doing this work in the controller:

    public function scopeWithLastestVideoTitle($query)
    {
        $query->addSelect(['lastest_video_title' => Video::query()
            ->select('title')
            ->whereColumn('user_id', '=', 'users.id')
            ->orderByDesc('created_at')->limit(1) 
        ]);
    }

And in our controller, we can just call the scope:

    return view('welcome', ['users' => User::withLastestVideoTitle()->get()]);

Then we need to change our blade template to use this new generated column:

    @foreach($user as $users)
        <!-- Table html -->
        <td>{{ $user-> }}</td>
        <td>{{ $user->lastest_video_title }}</td>
    @endforeach

Using Laravel debugbar for the last time, I can see that we got our page from making 32 queries and loading 330 models to making 2 queries and loading only 30 models into memory. Pretty neat!

If you liked this article, follow me on Twitter, I post tips and other Laravel content there.

Create make:custom commands in Laravel

Cosme Escobedo — Wed, 09 Mar 2022 16:27:24 +0000

Laravel comes with a feature that allows us to scaffold some commonly used classes, like models, factories, notifications, etc. If we want to create a model, for example, we can do it by running:

php artisan make:model MyModel

But what if we could do this for our custom classes? Instead of copying and pasting code, why don't we automate that process with a simple command!?. In this article, I'll show you how to do that exactly.

Let's start at the end

Before we start, we need to think about what we are building. I want to make a command that creates an empty report class that extends a different imaginary Report class.
I would like to run a command like this:

php artisan make:report MonthlyReport

That would then create a class under app/Reports/ with the following content:

<?php

namespace App\Reports;

class MontlyReport extends Report
{
    public function generate()
    {
        // TODO: add code..
    }
}

Of course, your classes don't have to be exactly like mine, you can use what I'm going to show you to make a generator command that fits your needs.

Generating a generator command

First, we need to create the command that will handle the generation of the classes. Make a new command with:

php artisan make:command MakeReport

I named my command MakeReport you can name it whatever you think fits better for your use case.

After that, we can find it under app/Console/Commands/. Open the file and change the extended class from Command to GeneratorCommand.

While we are here, we should also change the signature to make:report {name} (replace "report" with your type).

<?php

namespace App\Console\Commands;

use Illuminate\Console\GeneratorCommand;

class MakeReport extends GeneratorCommand
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'make:report {name}';
    ...
}

We'll come back to this command later. Before that, let's create the template for our class.

Adding the class template

To create the template for our classes, we need to create a new folder under app/Console/Commands named Stubs. Inside this folder, we need to create a new text file that contains the template for our class. I made a file with the name make-report.stub, and in that file, I added the template for the class:

<?php

namespace App\Reports;

class DummyReport extends Report
{
    public function generate()
    {
        // TODO: add code..
    }
}

The DummyReport name for the class is just a placeholder for the real class name. You can set it to whatever you want, but remember what it is since we'll replace it in the next step.

The generation logic

Now inside the get MakeReport command that we created earlier, I deleted every method and overwrote three new methods getStub getDefaultNamespace and replaceClass

class MakeReport extends GeneratorCommand
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'make:report {name}';

    //...

    public function getStub()
    {
        return app_path() . '/Console/Commands/Stubs/make-report.stub';
    } 

    public function getDefaultNamespace($rootNamespace)
    {
        return $rootNamespace . '\Reports';
    }

    public function replaceClass($stub, $name)
    {
        $stub = parent::replaceClass($stub, $name);

        return str_replace("DummyReport", $this->argument('name'), $stub);
    }
}

In the first method, we define the path to our stub file. In the second one, we add the namespace of the generated command, in this case, it will be App\Reports. The namespace will be used to calculate the correct path of the new class. And in the last method, we replace the DummyReport class name with the actual class name sent as an argument to the command.

The final result

And that is it. Now we have a command to generate our custom class by simply running this in the terminal:

php artisan make:report MyReport

And a new report will be created automatically.

If you liked this article follow me on Twitter, I post tips and other laravel content there.

How I added zero downtime deployment to my website

Cosme Escobedo — Wed, 23 Feb 2022 15:51:40 +0000

While I was building laravelremote.com (a Laravel remote job aggregator), I was worried that each time I wanted to push some changes to the production server, it might take the website down for a couple of seconds. Which probably isn't a big deal, but I still wanted to avoid it.

Laravel offers a first-party paid product to avoid this, Envoyer it's only $10 bucks a month. But laravelremote.com doesn't generate any revenue right now, and I'm the type of person that likes to do things in-house to learn how it works, and I also like the freedom that it provides.

So with that in mind, I figured out a way to do this for free, and I wanted to share this in case someone else finds it useful.

The theory

I searched around the internet to see how Envoyer works under the hood, I don't exactly remember where I saw this, but basically, it works like this:

We have 3 directories that relate to our project:

releases/
current/
storage/

A new directory will be created in the releases/ directory each time we need to deploy a change to the website, then we run some "set up" commands (ex. composer install, move the cache routes, views, etc.), and we add a symbolic link from the releases' storage directory to the "permanent" storage/ directory, since we don't want to forget sessions and stuff like that on each deployment. After all of this, we add a symbolic link to the current/directory that points to the new release we just created.

This article is not a tutorial on how to deploy a Laravel app, so I'm not going to explain my NGINX configuration. The only thing you need to know is that my NGINX server is configured to read my website from the /var/www/laravelremote.com/ directory. This directory has another symbolic that points to the current/ directory.

More simply, every new release will be deployed then linked to the current directory, which will make the server show the updates.

The practice

Let's put that into practice, first I created a base directory that will contain everything we need for the project. I like to add this directory under the home folder and give it the name of the app

mkdir laravelremote/

since we are going to be working in this directory, we should cd into it

cd laravelremote

Now we need to create the storage directory and the other subdirectories that Laravel uses.

mkdir storage/
mkdir -p storage/cache/data/
mkdir storage/sessions/
mkdir storage/views/

These are the default Laravel directories that exist within the storage directory.

After that, we need to create the releases folder.

mkdir releases/

Also, we should create and global .env file that we should link to on every release.

touch .env

Add to this file the production environment variables.

The current directory will be created once we deploy the project for the first time, so there is no need to create it right now.

Deploying

We could create a new directory in the releases' directory, pull the main branch from GitHub, run every other needed command, and link everything at the end to the current directory. But that seems like a lot of steps for simple deployment, so I created a bash script to do that instead:

#!/bin/sh

UNIX_TIME=$(date +%s)

DEPLOYMENT_DIRECTORY=$UNIX_TIME

mkdir -p $DEPLOYMENT_DIRECTORY
git clone --depth 1 --branch master git@github.com:username/reponame.git $DEPLOYMENT_DIRECTORY

echo removing storage
rm -Rf $DEPLOYMENT_DIRECTORY/storage

cd $DEPLOYMENT_DIRECTORY

cp ~/laravelremote/.env .env

echo link master storage
ln -s -n -f -T ~/laravelremote/storage ~/laravelremote/releases/$DEPLOYMENT_DIRECTORY/storage

composer install --no-dev
npm install
npm run prod

echo view:cache command
php artisan view:cache

echo route:cache command
php artisan route:cache

echo config:cache command
php artisan config:cache

echo migrations
php artisan migrate --force


cd ..

#link to the latest deployment from Live
ln -s -n -f -T ~/laravelremote/releases/$DEPLOYMENT_DIRECTORY ~/laravelremote/current

echo Done!

This script lives under the releases/ folder. It creates a new directory for the new release with the current UNIX timestamp, then it pulls the repo from GitHub into that directory. After that, I remove the storage folder from that directory, copy the .env file that contains the production keys (you might want to create a symbolic link instead), and then link the "permanent" storage file to the current release.

After that, I run some necessary commands for the project to work.

Install the composer dependencies
Install the NPM dependencies and build the resources. (I don't commit my build resources, other people might do it differently)
Cache the views, routes, and config for optimization
Run the database migrations

Then finally, we link the current directory to this release, which will publish the changes.

And that's it, whenever I push something to the master branch, I ssh into the server, and then I run this script, and my changes are deployed with 0 downtime.

Possible improvements

I still have some things that I don't like about this approach.
One of them is that I would like to make it, so it automatically deploys the changes when I push them to GitHub without having to ssh into the server.
Another one is that the old versions are not removed automatically, I currently remove them manually, but I would like to automate this too. And since we are talking about that, I noticed that Envoyer has a way to roll back the changes if something goes wrong, so you can link one of the previous versions if you need to. That would probably need a more complicated setup on my part, but It would be interesting to do.

Regardless, I'm pretty happy with how this turned up, even though I still want to improve it. If you would like to hear about those future improvements follow me on Twitter, I'll probably post any developments there.

Filter Eloquent models with multiple optional filters

Cosme Escobedo — Tue, 15 Feb 2022 04:33:51 +0000

Often we need to filter eloquent models when displaying to a view. If we have a small number of filters this can be fine, but if you need to add more than a couple the controller might get cluttered and difficult to read.

This is especially true when dealing with multiple optional filters that can be used in conjunction.

However, there are ways to create these filters, and even make them reusable. By the end of this article, you will be better equipped to deal with complex filtering options in your projects.

Defining the problem

Let's say, as an example that I have a controller method that returns every product in our store, this can be for an API, passing it to a blade template, or whatever the case might be, the controller will probably look something like this:

<?php

namespace App\Http\Controllers;

use App\Models\Product;
use Illuminate\Http\Request;

class ProductsController extends Controller
{
    public function index()
    {
        return Product::all();
    }
}

However, this is not realistic at all. Most often than not, we need to add some filtering, for example, let's say we want to get only the products in a given category, we could do that by sending the category slug in our query string and then filtering using the slug. So our new controller would look something like this:

class ProductsController extends Controller
{
    public function index()
    {
        if ($request->filled('category')) {
            $categorySlug = $request->category;

            return Product::whereHas('category', function ($query) use ($categorySlug) {
                $query->where('slug', $categorySlug);
            });
        }

        return Product::all();
    }
}

If we only need one filter, this is fine. But, look what happens if we introduce only two more filtering options:

class ProductsController extends Controller
{
    public function index(Request $request)
    {
        $query = Product::query();

        if ($request->filled('price')) {
            list($min, $max) = explode(",", $request->price);

            $query->where('price', '>=', $min)
                  ->where('price', '<=', $max);
        }


        if ($request->filled('category')) {
            $categorySlug = $request->category;

            $query->whereHas('category', function ($query) use ($categorySlug) {
                $query->where('slug', $categorySlug);
            });
        }

        if ($request->filled('brand')) {
            $brandSlug = $request->brand;

            $query->whereHas('brand', function ($query) use ($brandSlug) {
                $query->where('slug', $brandSlug);
            });
        }


        return $query->get();
    }
}

We've introduced two more filters, one for the brand slug and one for the price range. In my opinion, this is too busy, it's hard to keep track of what is going on here, and it can get a lot worse quickly if we need to add more filtering options.

When you search for a product on eBay, for example, you often get more than ten optional filters. We need to look for another approach to doing this.

The dream

What if, instead of having all those filters in our controller, we could do something like this:

class ProductsController extends Controller
{
    public function index(ProductFilters $filters)
    {
        return Product::filter($filters)->get();
    }
}

Here we are receiving a ProductFilters class that presumably has all of our filters and, then we can apply them to a query scope named filter. This makes our controller very slim, and it's easy to guess what it's happening. We are filtering the products, if we need to know more details, we can then look into the ProductFilters class.

Implementing the new approach

First, let's add the scope to our model:

class Product extends Model
{
    use HasFactory;

    public function category() 
    {
        return $this->belongsTo(Category::class);
    }

    public function brand() 
    {
        return $this->belongsTo(Brand::class);
    }

    // This is the scope we added
    public function scopeFilter($query, $filters)
    {
        return $filters->apply($query);
    }
}

In this scope, we receive a QueryBuilder instance and the ProductFilters instance we passed down from the controller. Then we call the apply method on this $filter instance.

Up to this point, we know the ProductFilters class will look something like this:

namespace App\Filters;

class ProductFilters
{
    public function apply($query)
    {
        if (request()->filled('price')) {
            list($min, $max) = explode(",", $request->price);

            $query->where('price', '>=', $min)
                  ->where('price', '<=', $max);
        }


        if (request()->filled('category')) {
            $categorySlug = $request->category;

            $query->whereHas('category', function ($query) use ($categorySlug) {
                $query->where('slug', $categorySlug);
            });
        }

        if (request()->filled('brand')) {
            $brandSlug = $request->brand;

            $query->whereHas('brand', function ($query) use ($brandSlug) {
                $query->where('slug', $brandSlug);
            });
        }


        return $query->get();
    }
}

This code will work, but it isn't that much better than just having the filters in the controller. Instead of doing this, I would like to have separate filter classes that only contain their filtering logic.

Let's do that for the categories filter:

namespace App\Filters;


class CategoryFilter
{
    function __invoke($query, $categorySlug)
    {
        return $query->whereHas('category', function ($query) use ($categorySlug) {
            $query->where('slug', $categorySlug);
        });
    }
}

Here we have a self-contained class that we can even use in other models, let's say we have a blog attached to the store, we can use this same filter for the blog posts.

By the way, if you are not familiar with the __invoke magic method, you can find more information here: https://www.php.net/manual/en/language.oop5.magic.php#object.invoke. In short, it just lets us call an instance of a class as if we were calling a function.

After we create the classes for our filters, we need to find a way to call them from the ProductFilters class. There are many ways to do this, for this article, I'm going to take the following approach:


namespace App\Filters;

class ProductFilters
{
    protected $filters = [
        'price' => PriceFilter::class,
        'category' => CategoryFilter::class,
        'brand' => BrandFilter::class,
    ];

    public function apply($query)
    {
        foreach ($this->receivedFilters() as $name => $value) {
            $filterInstance = new $this->filters[$name];
            $query = $filterInstance($query, $value);
        }

        return $query;
    }


    public function receivedFilters()
    {
        return request()->only(array_keys($this->filters));
    }
}

This is the finished class, let me break it down and explain each part.

How does this work?

First, let's start with the $filters property

    protected $filters = [
        'category' => CategoryFilter::class,
        'price' => PriceFilter::class,
        'brand' => BrandFilter::class,
    ];

Here I've defined every optional filter for the products, the key of the array is the key that will be used to check if the filter it's in the request, and the value of the array is the class that defines the filter behavior. This makes it easy for us to add more filters, when we need to add a new filter we just create the new filter class and add that class to this array.

The second part of the puzzle is this method:

    public function receivedFilters()
    {
        return request()->only(array_keys($this->filters));
    }

This method it's used to find what filters are being used in the request so we only apply the needed filters. We're also calling the only method and passing to it the keys of the $filters array to prevent us from attempting to call a filter class that doesn't exist. Let's say someone sends the filter "size" but we currently don't support it, this will make it so that the request key is ignored.

Now let's talk about the meat of this class, the apply method:

    public function apply($query)
    {
        foreach ($this->receivedFilters() as $name => $value) {
            $filterInstance = new $this->filters[$name];
            $query = $filterInstance($query, $value);
        }

        return $query;
    }

This method is simply a for each that loops through the received filters creates a class of the filter, and then calls the filter with the $query and the value received in the request.

For example, let's say we get a request that looks like this:

['category' => 'mobile-phones', 'price' => '100,150']

This class will first create an instance of CategoryFilter and then pass the $query and mobile-phones to it. Essentially, it will do this:

$filterInstance = new CategoryFilter();
$query = $filterInstance($query, 'mobile-phones');

Same thing for the next PhoneFilter:

$filterInstance = new PhoneFilter();
$query = $filterInstance($query, '100,150');

Then after all the queries are applied, it will return the $query object with every requested filter. And in that is what we receive when we apply the scope in the controller:

class ProductsController extends Controller
{
    public function index(ProductFilters $filters)
    {
        return Product::filter($filters)->get();
    }
}

Conclusion

We've created an extendible way to make multiple filters for our products, and there are things here that we can improve, for example adding validation for the filter value or creating a base class from ProductFilters so we can add the same type of filtering to other models. But I'll leave that as a challenge to you.

If you liked this post, follow me on twitter. I post tips and other content there.

How to find the slowest query in your application

Cosme Escobedo — Sat, 12 Feb 2022 20:00:38 +0000

Is your website slow? does it take ages to load? Are the users complaining that it's almost unusable?. You should check your database queries. And I'm going to show you a neat way to profile all database queries easily.

Of course, your website might be slow for a lot of reasons, but one of the most common reasons is slow database queries.

But in laravel we don't use SQL (most of the time) to fetch data from our database, we use laravel's eloquent ORM and query builder which sometimes makes it hard to pinpoint that query that is making our site so slow.

DB::listen()

Fortunately, in laravel, we can define a callback that gets called every time a query gets executed (see here). To do this add the following code to any service provider (for example the AppServiceProvider):

    public function boot()
    {
        DB::listen(function ($query) {
            // TODO: make this useful
        });
    }

As you can see we are receiving a variable $query, this variable is an instance of the QueryExecuted class. That means that we have access to some information about the query that was executed:

        DB::listen(function ($query) {
            $query->sql; // the sql string that was executed
            $query->bindings; // the parameters passed to the sql query (this replace the '?'s in the sql string)
            $query->time; // the time it took for the query to execute;
        });

This is very useful information, now we have a way to identify slow queries by looking at the $query->time property. But this doesn't tell us where the query was executed in our code.

How do I know where the query was executed?

Even though the $query variable doesn't give us any information about where that $query originated from, we can still get that information using the PHP built-in function debug_backtrace().

        DB::listen(function ($query) {
            dd(debug_backtrace());
        });

If you run this on your project you will see something like this on the browser:

array:63 [▼
  0 => array:7 [▼
    "file" => "/home/cosme/Documents/projects/cosme.dev/vendor/laravel/framework/src/Illuminate/Events/Dispatcher.php"
    "line" => 404
    "function" => "App\Providers\{closure}"
    "class" => "App\Providers\AppServiceProvider"
    "object" => App\Providers\AppServiceProvider {#140 ▶}
    "type" => "->"
    "args" => array:1 [▶]
  ]
  1 => array:7 [▼
    "file" => "/home/cosme/Documents/projects/cosme.dev/vendor/laravel/framework/src/Illuminate/Events/Dispatcher.php"
    "line" => 249
    "function" => "Illuminate\Events\{closure}"
    "class" => "Illuminate\Events\Dispatcher"
    "object" => Illuminate\Events\Dispatcher {#27 ▶}
    "type" => "->"
    "args" => array:2 [▶]
  ]
  2 => array:7 [▼
    "file" => "/home/cosme/Documents/projects/cosme.dev/vendor/laravel/framework/src/Illuminate/Database/Connection.php"
    "line" => 887
    "function" => "dispatch"
    "class" => "Illuminate\Events\Dispatcher"
    "object" => Illuminate\Events\Dispatcher {#27 ▶}
    "type" => "->"
    "args" => array:1 [▶]
  ]
  ....

This is an array containing every function call up to this point on the request. I'm going to focus only on the file and line keys in each array.

If you look closely you can see that in my example there were 63 function calls, which can be considered a lot, and this is a simple application, in a more complicated one, there might be a lot more. To make matters worse if you look at the ones at the top, all are internal functions to the laravel framework. Are we supposed to look at each one until we find something that might help us?

Finding the query location

As I said before most of them are internal framework calls, which means that most of those files are inside our vendor/ directory. That means that we can check each file and filter out any call that has vendor/, like this:

        DB::listen(function ($query) {
            $stackTrace = collect(debug_backtrace())->filter(function ($trace) {
                return !str_contains($trace['file'], 'vendor/');
            });

            dd($stackTrace);
        });

Here I'm converting the array into a collection to use the filter method, if the file of the current $trace has vendor/ we remove it from the collection.

If you run the code above, you will see something like this:

Illuminate\Support\Collection {#1237 ▼
  #items: array:5 [▼
    12 => array:7 [▼
      "file" => "/home/cosme/Documents/projects/cosme.dev/app/Models/Post.php"
      "line" => 61
      "function" => "get"
      "class" => "Illuminate\Database\Eloquent\Builder"
      "object" => Illuminate\Database\Eloquent\Builder {#310 ▶}
      "type" => "->"
      "args" => []
    ]
    16 => array:6 [▶]
    17 => array:6 [▶]
    61 => array:7 [▶]
    62 => array:4 [▶]
  ]
  #escapeWhenCastingToString: false
}

Those are a lot fewer items, we went from 63 to only 5. And the best part is that the first item in the collection is the exact place where we triggered the SQL query. That means that we can extract that information to find the slowest queries.

Putting it all together

Now that we have all the information that we need, why don't we log it so we can check and look for the slowest queries?:


    public function boot()
    {
        DB::listen(function ($query) {
            $location = collect(debug_backtrace())->filter(function ($trace) {
                return !str_contains($trace['file'], 'vendor/');
            })->first(); // grab the first element of non vendor/ calls

            $bindings = implode(", ", $query->bindings); // format the bindings as string

            Log::info("
                   ------------
                   Sql: $query->sql
                   Bindings: $bindings
                   Time: $query->time
                   File: ${location['file']}
                   Line: ${location['line']}
                   ------------
            ");
        });
    }

If you use this in your application you can then check your log file and you should see the queries information like this:

[2022-02-03 02:20:14] local.INFO:
                    ------------
                    Sql: select "title", "slug", "body" from "posts" where "published" = ? order by "id" desc   
                    Bindings: 1
                    Time: 0.18
                    File: /home/cosme/Documents/projects/cosme.dev/app/Models/Post.php
                    Line: 61
                    ----------

Now you know what queries are the slowest and start tackling them one by one, try to make them faster, or at the very least cache them.

Beyond debugging

This is very useful for debugging but this technique can be used in a multitude of ways.

You could create a weekly report showing the slowest queries of the week.

You could get a slack alert if a query exceeds a time threshold

You could create a dashboard where you and your team could see every query executed

The sky is the limit.

If you liked this, follow me on twitter so you can find out about my other Laravel experiments as I try them.