Mohamed Benhida

admin@devma.net

December 23, 2017

Query Reduction on Laravel

Hey,

We will talk today how can we increase the speed of our website by make reduction to our laravel queries . First of all we need an laravel-debugbar so we can see how much query that we call.

For Laravel 5.5 you just need to install the package on composer and package auto discover will call it for you on laravel

composer require barryvdh/laravel-debugbar --dev

Let's begin by creating a Post Model php artisan make:model Post -m and a Reply model php artisan make:model Reply -m

For relation on Post we will need an owner then the replies of each Post.

For relation on Reply we need also an owner of the current Reply.

Post Migration

<?php

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

class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->string('title');
            $table->text('body');
            $table->timestamps();
        });
    }

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

Reply Migration 

<?php

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

class CreateRepliesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('replies', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id');
            $table->integer('post_id');
            $table->string('body');
            $table->timestamps();
        });
    }

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

And Factory for Fake Data

database/factories/UserFactory.php

<?php

use Faker\Generator as Faker;

$factory->define(App\User::class, function (Faker $faker) {
    static $password;

    return [
        'name' => $faker->name,
        'email' => $faker->unique()->safeEmail,
        'password' => $password ?: $password = bcrypt('secret'),
        'remember_token' => str_random(10),
    ];
});

$factory->define(App\Post::class, function (Faker $faker) {
    return [
        'user_id' => factory(App\User::class)->create()->id,
        'title' => $faker->sentence,
        'body' => $faker->paragraph,
    ];
});

$factory->define(App\Reply::class, function (Faker $faker) {
   return [
        'post_id' => factory(App\Post::class)->create()->id,
        'user_id' => factory(App\User::class)->create()->id,
        'body' => $faker->sentence
   ];
});

Now we begin by creating Posts on php artisan tinker

tinker

And Replies For the first Post id

tinker

Now on Post Model like we said we need an owner and replies for each Post 

App\Post.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    public function owner() {
        return $this->belongsTo('App\User','user_id');
    }

    public function replies() {
        return $this->hasMany('App\Reply');
    }
}

on Reply Model we will just need the owner of the current reply

App\Reply.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Reply extends Model
{
    public function owner() {
        return $this->belongsTo('App\User','user_id');
    }
}

we come to fetch the data on view  

web.php

Route::get('/', function () {
    $posts = App\Post::all();
    return view('welcome',[
        'posts' => $posts
    ]);
});

welcome.blade.php

<div class="content">
                @foreach($posts as $post)
                <div class="title m-b-md">

                    <p><strong> {{ $post->title }} By {{ $post->owner->name }}</strong> {{ $post->replies()->count() }}</p>

                    <div style="margin-left: 30px">
                        @foreach($post->replies as $reply)
                            <p>{{ $reply->body }} By {{ $reply->owner->name }}</p>
                        @endforeach
                    </div>
                </div>
                @endforeach
            </div>

Like you see on image we have 28 queries for a little data like this but why if i told that we can decrease this to 4 queries okey stay with me

view

Laravel provide a variable $with is what you need to add in each call of the current Model like in our case we have Post and in each Post query we want to call the owner and the replies related to this Post.

So we will just add this to our Post Model

App\Post.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $with = ['owner','replies'];

    public function owner() {
        return $this->belongsTo('App\User','user_id');
    }

    public function replies() {
        return $this->hasMany('App\Reply');
    }
}

Now if we call App\Post::all() we will see the owner and the replies in each Post attributes like this

model

The same with Reply Model we need to call owner on each query

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Reply extends Model
{
    protected $with = ['owner'];

    public function owner() {
        return $this->belongsTo('App\User','user_id');
    }
}

Now as we can see the queries are decreased to 12 queries but is not enough 

the issue that we count the replies in each Post and here come the role of $withCount we have to retrieve the count of replies in each Post query so we need to add this to our Post Model like this

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $with = ['owner','replies'];
    protected $withCount = ['replies'];

    public function owner() {
        return $this->belongsTo('App\User','user_id');
    }

    public function replies() {
        return $this->hasMany('App\Reply');
    }
}

Now if we look to our Results we see that we have in each Post a replies_count attributes 

view

But we didn't finish yet we need to replace 

{{ $post->replies()->count() }} by {{ $post->replies_count }} on welcome.blade.php

Now if we look to our queries Number we see that we got only 4 :O

tinker

I hope that i could help you .

Enjoy !

 

© Mohamed Benhida.

Blog | Packages | About