Level 104
I wonder if the similar query for the total count is triggering the warning; you can get the total count anyway from the LengthAwarePaginator instance:
$this->countmembers = $ensemblemember->total();
Jun 15, 2021
9
Level 15
n+1 query results
Hi - I'm getting n+1 warnings when I run a query using a 'with' condition.
Here's the model:
class Ensemblemember extends Model
{
use HasFactory, SoftDeletes;
protected $fillable = ['ensemble_id', 'instrumentation_id', 'schoolyear_id', 'teacher_user_id', 'user_id', ];
public function person()
{
return $this->belongsTo(Person::class, 'user_id', 'user_id');
}
public function instrumentation()
{
return $this->belongsTo(Instrumentation::class);
}
public function schoolyear()
{
return $this->belongsTo(Schoolyear::class);
}
}
Here's the Livewire method:
use App\Models\Ensemblemember;
use App\Models\Schoolyear;
use App\Models\Userconfig;
use Livewire\Component;
class MembersTable extends Component
{
...
private function ensemblemembers()
{
$ensemblemembers = Ensemblemember::with('person', 'instrumentation')
->where('schoolyear_id', $this->schoolyear_id)
->where('ensemble_id', Userconfig::getValue('ensemble_id', auth()->id()))
->paginate(Userconfig::getValue('pagination', auth()->id()));
//count total members regardless of pagination
$this->countmembers = Ensemblemember::where('schoolyear_id', $this->schoolyear_id)
->where('ensemble_id', Userconfig::getValue('ensemble_id', auth()->id()))
->count();
return $ensemblemembers;
}
...
}
and here are the resulting queries:
select * from 'ensemblemembers' where 'schoolyear_id' = 2020 and 'ensemble_id' = '1' and 'ensemblemembers'.'deleted_at' is null limit 4 offset 0
select * from `people` where `people`.`user_id` in (499, 500, 640, 840)
select * from `instrumentations` where `instrumentations`.`id` in (63, 64)
It looks to me like the query engine is ignoring the 'with' condition in the LIvewire method, but I'm not seeing what I've got wrong in the method.
Thanks - Rick
Level 5
I think it's Ok. because when you use with, Eloquent add the query to select all related data with each with.
Ex: (499, 500, 640, 840) is a people_id array extract from first query. and it's not n+1 query. Until this function Userconfig::getValue('pagination', auth()->id()) got a problem.
Maybe you should control this.
Sorry my English it's not good
Level 15
@tykus @simkbaio : Thanks for the quick responses! I implemented the LengthAwarePaginator ($this->countmembers = $ensemblemembers->total();) and tested replacing the Userconfig::getValue('pagination', auth()->id()) with "4" but got the same n+1 warning results. The LengthAwarePaginator is a much better use of the framework, so thanks for this!
Level 104
What is telling you that there are N+1 queries?
Level 15
"beyondcode/laravel-query-detector"
Level 104
I haven't used that package with Livewire previously; does it alert for every rerendering of the component, or just the first? Is the issue with the component at all; or perhaps something else on the page?
Level 122
You are not showing the view, which is where the n+1 will be triggered (it has to be within a foreach loop)
Level 15
Hi @snapey - Here's the view:
<div>
<div id="schoolYear" class="flex mb-3">
<label for="schoolyear_id" class="h-8 pt-2">School Year: </label>
<select wire:model="schoolyear_id" name="schoolyear_id" id="schoolyear_id" class="h-8 mx-2 text-sm">
@foreach($schoolyears AS $schoolyear_obj)
<option value="{{ $schoolyear_obj->id }}"
class="text-xs"
>{{ $schoolyear_obj->descr }}</option>
@endforeach
</select>
<label for="schoolyear_id" class="h-8 pt-2">has {{ $countmembers }} members ({{ $schoolyear_id }})</label>
</div>
</div>
<div wire:model="ensemblemembers">
<x-tables.surgetable>
<x-slot name="head">
<x-tables.heading sortable direction="asc">Name</x-tables.heading>
<x-tables.heading sortable direction="asc">Voice Part</x-tables.heading>
<x-tables.heading><span class="sr-only">Edit</span></x-tables.heading>
<x-tables.heading><span class="sr-only">Delete</span></x-tables.heading>
</x-slot>
<x-slot name="body">
@forelse($ensemblemembers AS $ensemblemember)
<x-tables.row altcolor="{{$loop->iteration % 2}}">
<x-tables.cell>
{{ $ensemblemember->person->fullName }}
</x-tables.cell>
<x-tables.cell>
{{ $ensemblemember->instrumentation->formattedDescr() }}
</x-tables.cell>
<x-tables.cell>
<a
href="{{ route('ensemble.members.edit',['ensemblemember' => $ensemblemember]) }}"
class="border border-blue-500 rounded px-2 bg-blue-400 text-white hover:bg-blue-600"
>
Edit
</a>
</x-tables.cell>
<x-tables.cell>
<a
href="{{ route('ensemble.members.destroy') }}"
class="border border-red-500 rounded px-2 bg-red-400 text-white hover:bg-red-600"
onclick="return chickenTest({{$ensemblemember}});"
>
Delete
</a>
</x-tables.cell>
</x-tables.row>
@empty
<x-tables.row>
<x-tables.cell colspan="4">No members found</x-tables.cell>
</x-tables.row>
@endforelse
</x-slot>
</x-tables.surgetable>
</div>
<div class="mt-3">
{{ $ensemblemembers->links() }}
</div>
Level 75
Also @jeffreyway himself completely covers the n + 1 issue here:
https://laracasts.com/series/laravel-8-from-scratch/episodes/26
Please or to participate in this conversation.