Optimize queries with leftJoin Laravel?

Question

I have linked tables and when I need to access the information I recreate the query:

DB::table('paciente AS pac')
      ->leftJoin('pessoa AS p', 'p.id', '=', 'pac.id_pessoa')
      ->select('p.nome, pac.ficha')
      ->where('pac.id', '=', $id_paciente)
      ->orderby('ag_prof.hora_inicial')
      ->get();

How can I optimize, to avoid the repetition of this part in Controller, I must create in the model or in which folder?

Answer 1

Can be used Local Scopes which is a method made in your model eloquent to facilitate programming, would not be a bank optimization, but rather a clean coding, example:

use Illuminate\Database\Eloquent\Model;
class Paciente extends Model
{
    public function scopeJoinPessoa($query, $id_paciente)
    {
        return $query->leftJoin('pessoa', 'pessoa.id','=', 'paciente.id_pessoa')
                     ->select('pessoa.nome, paciente.ficha')
                     ->where('paciente.id', '=', $id_paciente);
    }
}

and use in your code like this:

Paciente::joinPessoa($id_paciente)->get();

Observing: in your example has a orderBy, that there is no such relationship, if by chance you forgot just add one more join in the method of scopes.

_{That would be one way to solve this problem.}

_References:

• Eloquent
• Eloquent Local Scopes