I need to pass this query to laravel

Question

I need to pass this query to laravel

Navigation

#1 by (1 votes)

0

I need to pass this query to laravel

select p.name, e.date_hour, e.num_comprobante, SUM (d1.value * d1.quantity) as water, SUM (d2.value * d2.quantity) as holidays, SUM (d.value * d. quantity) as others, e.total_species from kind and join person p on e.idclient = p.personnel left join detail_species d1 on e.idSpecies = d1.idSpecies and d1.id Tax = 1 left join detail_specie d2 on e.idSpecies = d2 .idSpecies and d2.id Tax = 2 left join detail_specie d on e.idSpecies = d.idSpecies and d.idPrimary > 2 group by e.num_comprobante;

$ species = DB :: table ('species as e') - > leftjoin ('detail_species as d', 'e.idSpecies', '=', 'd.idSpecies') - > AND ('d.idImpuesto', '=', '1')

          //  ->leftjoin('detalle_especie as d', 'e.idEspecie', '=', 'd.idEspecie', '||','d.idImpuesto', '=', '0' )
            ->select('fecha_hora', 
                    'num_comprobante', 
                    'total_especie', 
                   DB::raw ('SUM(d.valor * d.cantidad) as otros')) 


            ->groupby('num_comprobante')
            ->get();

// dd ($ species); $ data = [ "species" = > $ species

];

return view('reportes.index', $data);

laravel

asked by Mario Miranda 02.01.2019 в 03:16

source

1 answer

How to use Friendly Variables? How to ask before closing a window

score 1 · Answer 1

Apparently you do not specify how to perform the query, so ...

Add this:

use Illuminate\Support\Facades\DB;

and the query, so as not to complicate much you can use it in this way:

$resultado = DB::select('select p.nombre ,e.fecha_hora ,e.num_comprobante ,SUM(d1.valor * d1.cantidad) as agua ,SUM(d2.valor * d2.cantidad) as fiestas ,SUM(d.valor * d.cantidad) as otros ,e.total_especie from especie e join persona p on e.idcliente = p.idpersona left join detalle_especie d1 on e.idEspecie = d1.idEspecie and d1.idImpuesto = 1 left join detalle_especie d2 on e.idEspecie = d2.idEspecie and d2.idImpuesto = 2 left join detalle_especie d on e.idEspecie = d.idEspecie and d.idImpuesto > 2 group by e.num_comprobante');

Well, the code is what you use, but as I mentioned it is not very readable. inside the quotes you use your query as it is. Although the most optimal would be that you use the models. Then you use the result the way you need it.