Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I need, help, with this monstros query it runs 15 times, :D I am just learning, so can someone mentor me in my journey how to optimise this query?
$now = Carbon::now()->subHour();
//category is field inside lessongs table with category name
$categories = Lesson::select('category')->groupBy('category')->get();
$procedures = Event::where('start', '>=', $now )
->where('client_id', NULL)
->where('client_status', 1)
->with('lesson.course', 'lesson.school', 'student', 'client', 'table.address')
->get();
return view('frontend.partials.procedures.index', compact('procedures', 'lesson.course', 'lesson.school', 'student', 'client', 'categories', 'table.address'));
Thanks you guys in advance,
What my debugger shows
select `category` from `lessons` group by `category`
270μs
select * from `events` where `start` >= '2016-09-12 21:01:29' and `client_id` is null and `client_status` = '1'
810μs
select * from `lessons` where `lessons`.`id` in ('5', '6')
290μs
select * from `courses` where `courses`.`id` in ('2')
580μs
select * from `schools` where `schools`.`id` in ('1')
230μs
select * from `users` where `users`.`id` in ('3')
600μs
select * from `users` where `users`.`id` in ('0')
210μs
select * from `tables` where `tables`.`id` in ('1', '2')
600μs
select `addresses`.*, `addressables`.`addressable_id` as `pivot_addressable_id`, `addressables`.`address_id` as `pivot_address_id` from `addresses` inner join `addressables` on `addresses`.`id` = `addressables`.`address_id` where `addressables`.`addressable_id` in ('1', '2') and `addressables`.`addressable_type` = 'App\Table'
1.09ms
select * from `schools` where `schools`.`id` = '1' limit 1
570μs
select * from `users` where `users`.`id` = '1' limit 1
580μs
select * from `schools` where `schools`.`id` = '1' limit 1
210μs
select * from `events` where `start` >= '2016-09-12 21:01:29' and `client_id` is null and `client_status` = '1'
660μs
select * from `lessons` where `lessons`.`id` in ('5', '6')
600μs
select * from `courses` where `courses`.`id` in ('2')
550μs
select * from `schools` where `schools`.`id` in ('1')
190μs
select * from `users` where `users`.`id` in ('3')
570μs
select * from `users` where `users`.`id` in ('0')
Hey,
return view('frontend.partials.procedures.index', compact('procedures', 'lesson.course', 'lesson.school', 'student', 'client', 'categories', 'table.address'));
can be simplified:
return view('frontend.partials.procedures.index', compact('procedures'));
because lesson.course and the likes are relationships stored within $procedures.
First 9 queries make sense and are a result of Eloquent eager loading relationships.
Not sure about the next ones. What's you view like?
@JoolsMcFly So edited as you suggested, All still works,
now 2 qerties less Querie image
I am wondering, is it normal :)
If 16 queries, are for this data, then all ok
$now = Carbon::now()->subHour();
$categories = Lesson::select('category')->groupBy('category')->get();
$procedures = Event::where('start', '>=', $now )
->where('model_id', NULL)
->where('model_status', 1)
->with('lesson.course', 'lesson.school', 'table.address')
->get();
return view('frontend.partials.procedures.index', compact('procedures', 'categories'));
This is my view:
<ul class="nav nav-pills sort-source sort-source-style-2" data-sort-id="procedures" data-option-key="filter" data-plugin-options='{"layoutMode": "fitRows", "filter": "*"}'>
<li class="all" data-hash data-option-value="*" class="active"><a class="filter" href="#"></a><a class="filter" href="#">SHOW ALL</a></li>
@foreach($categories as $categorie)
<li class="all" data-hash data-option-value=".{{$categorie->category}}"><a class="filter" href="#">{{$categorie->category}}</a></li>
@endforeach
</ul>
<tbody>
@foreach($procedures as $procedure)
<tr>
<td data-title="">
<snap class="">
<strong>{{ $procedure->present()->title }}</strong>
<span class="label label-primary pull-right">{{$procedure->present()->price}}</span>
<br>{{ $procedure->school->title }}
</snap>
</td>
<td data-title="" class="">
<snap class="">
<i class="fa fa-clock-o"></i> {{ $procedure->present()->date }}
</snap>
</td>
<td data-title="" class="">
<span class="">
<i class="fa fa-map-marker" aria-hidden="true"></i> {{ $procedure->present()->locationAddress }}
</span>
</td>
<td data-title="" class="">
<a href="@if (Auth::check()) {{ url('api/'.$procedure->id.'/procedure/data/view') }} @else {{ url('/alogin') }} @endif " data-ajax-on-modal="" type="button" class="btn btn-default mr-xs mb-sm btn-block pull-right" target="_top">Pieteikties</a>
</td>
<td style="display: none;">
{{ $procedure->present()->category }}
</td>
</tr>
@endforeach
</tbody>
And inside every menu I have this
<li class="">
<a class="" href="{{ url('procedures') }}">
Procedures <span class="label label-primary">{{count($procedures)}}</span>
</a>
</li>
You're eager loading lesson.school but you're using $procedure->school->title. Why is that? If you used $procedure->lesson->school->title you would not need an additional query.
Also, if you do $procedure->present()->title you might as well eager load present() relationship and use $procedure->present->title
How can I eager load present() ?
Add it to with() just like you did with the other relationships:
->with('lesson.course', 'lesson.school', 'student', 'client', 'table.address', 'present')
@JoolsMcFly Hmm I got
FatalThrowableError in Builder.php line 655:
Call to undefined method App\Acme\Presenters\EventPresenter::addEagerConstraints()
I changet
$procedure->present()->title
to
$procedure->present->title
and error is the same
My presenter looks like this
public function title()
{
return $this->lesson->public_title;
}
public function category()
{
return $this->lesson->category .' '. $this->lesson->subcategory;
}
public function studentName()
{
return $this->student->name;
}
public function clientName()
{
return $this->model->name;
}
public function phone()
{
return $this->model->phone;
}
public function locationAddress()
{
return $this->table->address[0]->street.', '.$this->table->address[0]->city;
}
public function price()
{
return '€'.number_format($this->lesson->price/100, 2, '.', ' ');
}
public function date()
{
return $this->start->format('d-M-Y H:i, l');
}
public function appointmentDate()
{
return $this->start->format('jS F H:i');
}
public function dayOfWeek()
{
return $this->start->format('l');
}
public function description()
{
return $this->lesson->description;
}
What does your Event model look like?
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use Laracasts\Presenter\PresentableTrait;
class Event extends Model implements \MaddHatter\LaravelFullcalendar\IdentifiableEvent
{
protected $dates = [
'start',
'end',
];
protected $attributes = [
'editable' => true,
];
use PresentableTrait;
protected $presenter = 'App\Acme\Presenters\EventPresenter';
/**
* The attributes that are mass assignable.
*
* @var array
*/
protected $fillable = [
'lesson_id', 'start', 'end', 'rendering', 'eventColor', 'model_status', 'fullDay', 'table_id', 'school_id', 'student_id','model_id',
];
/**
* Get the event's id number
*
* @return int
*/
public function getId() {
return $this->id;
}
/**
* Get the event's title
*
* @return string
*/
public function getTitle()
{
return $this->lesson->title;
}
/**
* Is it an all day event?
*
* @return bool
*/
public function isAllDay()
{
return (bool)$this->fullDay;
}
/**
* Get the start time
*
* @return DateTime
*/
public function getStart()
{
return $this->start;
}
/**
* Get the end time
*
* @return DateTime
*/
public function getEnd()
{
return $this->end;
}
public function getEventOptions()
{
return [
'eventColor' => $this->eventColor,
'resourceId' => $this->resourceId,
];
}
public function school()
{
return $this->belongsTo(School::class, 'school_id');
}
public function lesson()
{
return $this->belongsTo(Lesson::class, 'lesson_id');
}
public function student()
{
return $this->belongsTo(User::class, 'student_id');
}
public function client()
{
return $this->belongsTo(User::class, 'client_id');
}
public function table()
{
return $this->belongsTo(Table::class, 'table_id');
}
}
OK sorry, never mind. I didn't realize you were using a presenter! Do not add present() to with(). :)
@JoolsMcFly Then, this query is ok? :)
Please or to participate in this conversation.