Optimize query in Laravel on big bd

Question

I know that this question is probably a bit absurd but I've been stuck with it for two days and I'm coming here looking for an alternative to what I currently have developed.

I put you in context, we have three tables:

Users
Sedes
UsersSedes (pivot) [user_id, sedes_id]

Currently I need to show in a list (Datatable) all the users that contain in their sedes one of the seats configured in the active user (administrator).

Example: Admin, logged and active sedes are A and B (There are also C and D) List: User 1 with sede A User 2 with sede B User 3 with sede B User 4 with sede B

I do not know if I explain ...

The problem is that my table has about 2000 records as I have programmed it takes a lot to manage the data because it makes 2000 queries.

EDIT Actually I use a foreach to make an array with data, for example:

$pacientes = Usuario::with(['sedes'])->where('role_id', 3)->get();

    $sedesHabilitadas = auth()->user()->sedes->pluck('id')->toArray();

    $habilitados = array();


     foreach($pacientes as $paciente)
    {
      if( in_array($paciente->sedes->pluck('id')->first(), $sedesHabilitadas))
        {
            array_push($habilitados, $paciente);
        }      

    }

It sounds like you're encountering an [N+1 issue](https://stackoverflow.com/questions/97197/what-is-the-n1-select-query-issue) laravel solves this using eager loading. — apokryfos, May 22 '18 at 06:47
I am sure that the problem is N + 1 but I do not know how to solve it by the subject of the sedes. Thank you... — Valentin Ayesa, May 22 '18 at 08:10

score 0 · Answer 1 · answered May 22 '18 at 11:23

It appears that you want the sedes where role_id = 3 and user_id = active user? If this is the case, then you can simply add the where constraint with the main query:

$habilitados = Usuario::where('role_id', 3)
    ->where('user_id', auth()->user()->id)
    ->with(['sedes'])->get();

This should give you the users with their 'sedes'. Otherwise if what you mean is that it should return Users::with their 'sedes' that have the same 'sedes' as the admin has then you may consider this:

$sedesHabilitadas = auth()->user()->sedes->pluck('id')->toArray();
$pacientes = Usuario::with(['sedes' => function($query) use (sedesHabilitadas){
        $query->whereIn('id', $sedesHabilitadas);
    ])->where('role_id', 3)->get();

But this does look like a repeated method since the id being queried is already retrieved as $sedesHabilitadas.

If this is not the case, let me know so I might have to remove this answer.

Optimize query in Laravel on big bd

1 Answers1