I have linked tables and when I need to access the information I recuse 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 repeating this in Controller , should I create it in model or in what folder?
If you can use Local Scopes which is a method done on your model eloquent to facilitate programming, would not be a bank optimization, clean, 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();
Note: In your example you have a orderBy , that there is no such relationship, if you forgot to just add another join scopes .
This would be a way to solve this problem.