How can I best optmize these queries in Laravel?

Question

I have a simple script in post.blade.php

{{ Auth::user() ? ($post->ratings()->where('user_id',  Auth::user()->id)->exists() ? 'You rated ' . $post->userRating($post) : '') : '' }}

The 2 functions that get access in my post model are:

public function ratings() {
    return $this->hasMany(Rating::class);
}

public function userRating(Post $post) {
    return $this->ratings()->where([
        ['post_id', '=', $post->id],
        ['user_id', '=', Auth::user()->id]
    ])->first('stars')->stars / 2;
}

This is the index in my postcontroller:

public function index() {
    $posts = Post::with('user')->withAvg('ratings', 'stars')->paginate(100);

    return view('posts.index', [
        'posts' => $posts,
    ]);
}

This however takes about 1 second to load each page because it has to execute that code 100 times.

If I remove the script mentioned at the top from the blade file the page loads way faster.

I made this query in raw MySQL and it loads the results significantly faster:

select `posts`.*, (select avg(`ratings`.`stars`) from `ratings` where `posts`.`id` = `ratings`.`post_id`) as `ratings_avg_stars`, (SELECT count(*) FROM ratings WHERE post_id = posts.id and user_id = 1) as rated from `posts` where `posts`.`deleted_at` is null

If I were to put that in my postcontroller I think the page would load faster, I don't know how to convert the MySQL to Eloquent, I have tried a query converter but those get stuck on the subqueries.

How can I convert the MySQL query to an Eloquent query?

user_1978311 · Answer

I believe you are looking to Constrain Your Eager Loading and create an access mutator for Post.

You can see my fiddle here.

What this approach does, is load all the necessary data in one go, using the constrained eager load, and the access mutator will have this relationship loaded already when it appends the ratingAverage to the post.

This way you can avoid any unnecessary database calls, or method calls to recompute values you already have.

Schema:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->timestamps();
});

Schema::create('posts', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('user_id');
    $table->string('title');
    $table->string('body');
    $table->timestamps();
});

Schema::create('ratings', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('user_id');
    $table->unsignedInteger('post_id');
    $table->integer('stars');
    $table->timestamps();
});

Models:

use \Illuminate\Database\Eloquent\Relations\Pivot;

class User extends Model
{
    protected $guarded = [];
}


class Rating extends Pivot
{
    protected $table = 'ratings';
    protected $guarded = [];

    public static function rate(User $user, Post $post, int $stars)
    {
        return static::create([
            'user_id' => $user->id,
            'post_id' => $post->id,
            'stars' => $stars
        ]);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

class Post extends Model
{
    protected $guarded = [];

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function ratings()
    {
        return $this->hasMany(Rating::class);
    }

    public function getRatingAverageAttribute()
    {
        return $this->ratings->sum('stars') / $this->ratings->count();
    }
}

Controller:

public function index() {
    $posts = Post::query()->with(['ratings' => function ($query) {
        $query->where('user_id', Auth::id());
    }])->get();

    return view('posts.index', [
        'posts' => $posts,
    ]);
}

And finally in blade:

@if($post->ratings->count())
    {{ 'You rated ' . $post->ratingAverage }}
@endif

How can I best optmize these queries in Laravel?

1 Answers