Let's be realistic if that complex query is already working just use as is.
There are times to use the orm and times that it's not as good.
Direct relations is better suited for orm.
Hello,
I've the follow DB query, but, I want to convert to Eloquent to use "with" statement for some childs and use in master/detail grid. It's possible ?
I've tried some ideas, without success.
$items = DB::table('pessoa_fisica as pf')
->join('atend as a', 'a.pefi_id', '=', 'pf.pefi_id')
->join('atend_servico as ateser', 'ateser.atend_id', '=', 'a.atend_id')
->join('servico as s', 'ateser.servico_id', '=', 's.servico_id')
->join('convenio as c', 'a.convenio_id', '=', 'c.convenio_id')
->join('atend_agendado_servico as aas', 'aas.ateser_id', '=', 'ateser.ateser_id')
->leftjoin('tratamento_servico_atend as tsa', 'ateser.ateser_id', '=', 'tsa.ateser_id')
->leftjoin('tratamento_servico as ts', 'tsa.traser_id', '=', 'ts.traser_id')
->select( 's.descr as descr_servico'
, 'c.descr as descr_convenio'
, 'ts.dt_alta'
, 'pf.nome'
, 'pf.num_cliente'
, 'pf.pefi_id'
, 's.servico_id'
, \DB::raw("date_format(aas.dt_agendada_ini, '%H:%i') as hr_agendada")
, \DB::raw("date_format(aas.dt_agendada_ini, '%w') as dia_semana_agendada")
, \DB::raw('count(*) as total_consultas')
)
->groupBy(\DB::raw("s.descr
, c.descr
, ts.dt_alta
, date_format(aas.dt_agendada_ini, '%w')
, date_format(aas.dt_agendada_ini, '%H:%i')
, pf.nome
, pf.num_cliente
, pf.pefi_id
, s.servico_id
"))
->whereRaw('aas.status = 1')
->whereRaw('pf.num_cliente = 107014')
->orderBy(\DB::raw("s.descr, count(*)"))
->havingRaw("count(*) > ?", [ 3])
->paginate(50);
$response = [ $items,
'links' => [
'pagination' => [
'total' => $items->total(),
'per_page' => $items->perPage(),
'current_page' => $items->currentPage(),
'last_page' => $items->lastPage(),
'from' => $items->firstItem(),
'to' => $items->lastItem()
],
]
];
Please or to participate in this conversation.