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.

withcauses the eloquent to be slow? granted it maps the rows on laravel side but a join wont always guarantee better performance. for examplePost'spublished_atandis_publishthenMedia'sis_thumbnailare 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 3withand each involves more than two tables.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.