brigman's avatar

Laravel DB::query to eloquent

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()
                              ],
                          ]
                      ];
0 likes
4 replies
jlrdw's avatar

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.

brigman's avatar

Thanks jlrdw, I've the same position.

In other hand, I've childs of this query ( master -> detail ), best idea is foreach in this items and add the childs in one new collection to return just one payload ?

hmmehead's avatar

I have the same idea, but, a think you could test this query, to see if the time spent, is to much in compar to use "with". Not this case, but in future cases

jlrdw's avatar

See https://laracasts.com/discuss/channels/eloquent/why-doesnt-laravel-use-joins-when-pulling-belongsto-or-hasone

Different query types are used for different purposes usually a master detail it's more like an invoice is the master and the details on that invoice would be the children.

Joins and group by are usually more for monthly and quarterly reports things like that.

So the type of query depends on what data you are trying to get and why.

Master detail more like

https://laracasts.com/discuss/channels/code-review/guidance

Please or to participate in this conversation.