0

I am using Laravel 5.7 and have a query written using Eloquent. I am attempting to convert it into an equivalent Query Builder query to enhance performance. However, I am facing difficulties when using leftJoins with related tables. Therefore, I will provide the original Eloquent query and the necessary models for executing the query. Could someone assist me in converting this query into an equivalent Query Builder query, and may I refer to the solution?

Here is the original query that I am using with Eloquent:

    {
        $now = cxl_carbon()::now();

        return $this->postModel
            ->select('*', 'published_at as published_at_time_line', 'created_at as created_at_time_line', 'tokens as token_detail_link')
            ->with([
                'media' => function ($queryMedia) use ($talentId, $subIds) {
                    $queryMedia->select('*', 'file_name as file_name_link_not_thumbnail')->with([
                        'plan_details' => function ($queryPlanDetail) use ($subIds) {
                            $queryPlanDetail->with('buy_back_plans_first')
                                            ->orderBy('price', 'desc');
                        },
                        'find_blur_media' => function ($queryMediaBlur) use ($talentId, $subIds) {
                            $queryMediaBlur->select('*', 'file_name as file_name_blur_media');
                        },
                    ])->whereNull('deleted_at')->where([['blur_id', '=', null], ['is_thumbnail', '!=', 1]]);
                },
                'find_thumbnail_media' => function ($queryMedia) {
                    $queryMedia->select('*', 'file_name as file_name_link_thumbnail');
                }, 'favorites'
            ])
            ->where([
                ['talent_id', '=', $talentId],
                ['published_at', '<', $now],
                ['is_publish', '!=', 0]
            ])
            ->when($token !== null, function ($q) use ($token) {
                $q->where('tokens', '!=', $token);
            })
            ->when($isPinned == 1, function ($q) use ($publishedAt) {
                $q->where(function ($q1) use ($publishedAt) {
                    $q1->where('published_at', '<', $publishedAt)->orWhere('is_pin', 0);
                })->orderBy('is_pin', 'desc');
            })
            ->when($isPinned != 1, function ($q) use ($publishedAt) {
                $q->where('published_at', '<', $publishedAt)->where('is_pin', 0);
            })
            ->orderBy('published_at', 'desc')
            ->orderBy('id', 'desc')
            ->skip($skip)
            ->take($take)
            ->get();
    }

Here is my PostModel

<?php

namespace Core\Modules\Client\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Post extends Model
{
    use SoftDeletes;
    protected $table = 'posts';

    protected $fillable = ['talent_id', 'media_id', 'description', 'is_public', 'post_type', 'publish_at', 'created_at', 'updated_at', 'deleted_at','publish_type','caption','caption_non_member','tokens','is_pin'];

    protected $hidden = [];

    protected static function boot()
    {
        parent::boot(); // TODO: Change the autogenerated stub

        static::addGlobalScope('is_draft', function (Builder $builder) {
            $builder->where('is_draft', '=', 0);
        });
    }

    public function plans()
    {
        return $this->belongsToMany(Plan::class, 'post_plan');
    }

    public function media()
    {
        return $this->hasMany(Media::class)->whereNull('deleted_at');
    }

    public function talents()
    {
        return $this->belongsTo(Talent::class);
    }

    public function talent()
    {
        return $this->belongsTo(Talent::class);
    }

    public function post()
    {
        return $this->hasMany(Post::class, 'id');
    }

    public function favorites()
    {
        return $this->hasMany(FavoriteLog::class, 'category_id','id')->where('category', 'post');
    }

    public function media_post()
    {
        return $this->hasMany(Media::class)->where([
            ['is_thumbnail', '=', 0],
            ['blur_id', '=', null],
            ['deleted_at', '=', null]
        ])->orderBy('position')->with('plan_details');
    }

    public function find_blur_media()
    {
        return $this->media()->whereNotNull('blur_id')->whereNull('deleted_at');;
    }

    public function find_thumbnail_media()
    {
        return $this->media()->where('is_thumbnail', '=', '1')->whereNull('deleted_at');
    }


    public function getPublishedAtBuyBackTimeLineAttribute($value)
    {
        cxl_carbon()::setLocale('ja');

        return cxl_carbon()::parse($value)->format('Y年n月j日');
    }

    public function getPublishedAtTimeLineAttribute($value)
    {
        cxl_carbon()::setLocale('ja');
        $monthDiff = cxl_carbon()::parse($value)->diffInMonths(cxl_carbon()::now());

        return $monthDiff === 0 ? cxl_carbon()::parse($value)->diffForHumans() : cxl_carbon()::parse($value)->format('Y年n月j日');
    }

    public function getCreatedAtTimeLineAttribute($value)
    {
        cxl_carbon()::setLocale('ja');

        return cxl_carbon()::parse($value)->diffForHumans();
    }
}

Media Model

<?php

namespace Core\Modules\Client\Models;

use Illuminate\Database\Eloquent\Model;

class Media extends Model
{
    protected $table = 'media';

    protected $fillable = ['post_id','type','is_thumbnail','file_name','created_at','updated_at','deleted_at'];

    protected $hidden = [];

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

    public function plan_details()
    {
        return $this->belongsToMany(PlanDetail::class, 'media_plan_detail', 'media_id', 'plan_detail_id')->withPivot('plan_detail_buy_back_id')->whereNull('deleted_at');
    }

    public function find_blur_media()
    {
        return $this->hasOne(Media::class, 'blur_id')->whereNotNull('blur_id');
    }

}

Here is my PlanDetailModel

<?php

namespace Core\Modules\Client\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class PlanDetail extends Model
{
    protected $table = 'plan_detail';

    protected $fillable = [
        'plan_id',
        'plan_name',
        'price',
        'title',
        'description',
        'max_image',
        'max_user',
        'remain_user',
        'is_main_plan',
        'paid',
        'is_draft',
        'force_stop_buy_back',
        'created_at',
        'updated_at',
        'deleted_at'
    ];

    protected $hidden = [];

    use SoftDeletes;

    protected static function boot()
    {
        parent::boot(); // TODO: Change the autogenerated stub

        static::addGlobalScope('is_draft', function (Builder $builder) {
            $builder->where('is_draft', '=', 0);
        });
    }

    public function medias()
    {
        return $this->belongsToMany(Media::class, 'media_plan_detail', 'plan_detail_id', 'media_id');
    }

    function children()
    {
        return $this->hasMany($this, 'parent_id');
    }

    public function parent()
    {
        return $this->belongsTo($this, 'parent_id');
    }

    public function buy_back_plans()
    {
        return $this->hasMany(PlanDetail::class, 'parent_id')->whereNotNull('is_buy_back');
    }

    public function buy_back_plans_first()
    {
        return $this->hasOne(PlanDetail::class, 'parent_id')->whereNotNull('is_buy_back');
    }

}

Here is the result image; the data above is the query builder I converted. Below is the original eloquent query. I have retrieved the post data, but for the relationships that I use with() to get, such as media, find_thumbnail_media, and favorites, I don't know how to retrieve them. enter image description here

4
  • "convert it into an equivalent Query Builder query to enhance performance" - are you sure laravel's with causes the eloquent to be slow? granted it maps the rows on laravel side but a join wont always guarantee better performance. for example Post's published_at and is_publish then Media's is_thumbnail are good candidate to be indexed in my opinion as long as the foreign keys are in place. you have to be wary with the query doing too much, i saw at least 3 with and each involves more than two tables. Commented Dec 28, 2023 at 6:00
  • a join would complicate stuff as it won't give you similar result form. a join will give you "duplicates" and you have to handle it a wee more bit in the application side to turn it into your desired json result. joins are not the silver bullet to every problem, see this QA. Commented Dec 28, 2023 at 6:02
  • @BagusTesa You're right. Frankly speaking, I don't believe that switching the query from eloquent to query builder will significantly improve performance, and it might introduce some issues like duplicate records when we perform table joins, as you mentioned. However, this is a request from my team leader, and he wants to see results in terms of query execution time for both queries so that he can compare them. Commented Dec 28, 2023 at 6:14
  • hmm, thats difficult. perhaps you could benchmark it without the laravel first. intercept laravel's query using DB::getQueryLog(), you will get at least 4 queries if i'm not wrong. then cook a pure SQL join (dont mind the duplicates). then compare them, see if the join is any faster cummulatively. i'd bet the difference is negligible given they are overheads. Commented Dec 28, 2023 at 6:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.