Home / Questions / 402711
Accepted
vdjkelly
Asked: 2021-10-31 11:27:03 +0800 CST 2021-10-31 11:27:03 +0800 CST

Pass pure sql query to laravel

  • 772

Hello, I have a somewhat complex query which I need to migrate to Eloquent with Laravel or QueryBuilder. The query is as follows:

SELECT tipo, id_banco, nro_operacion, anno,  
sum(CASE  WHEN mes = 1 THEN cuota ELSE 0 END) AS Enero,
sum(CASE  WHEN mes = 2 THEN cuota ELSE 0 END) AS Febrero,
sum(CASE  WHEN mes = 3 THEN cuota ELSE 0 END) AS Marzo,
sum(CASE  WHEN mes = 4 THEN cuota ELSE 0 END) AS Abril,
sum(CASE  WHEN mes = 5 THEN cuota ELSE 0 END) AS Mayo,
sum(CASE  WHEN mes = 6 THEN cuota ELSE 0 END) AS Junio,
sum(CASE  WHEN mes = 7 THEN cuota ELSE 0 END) AS Julio,
sum(CASE  WHEN mes = 8 THEN cuota ELSE 0 END) AS Agosto,
sum(CASE  WHEN mes = 9 THEN cuota ELSE 0 END) AS Septiembre,
sum(CASE  WHEN mes = 10 THEN cuota ELSE 0 END) AS Octubre,
sum(CASE  WHEN mes = 11 THEN cuota ELSE 0 END) AS Noviembre,
sum(CASE  WHEN mes = 12 THEN cuota ELSE 0 END) AS Diciembre
from (
SELECT amortizacion.tipo, b.id as id_banco, amortizacion.nro_operacion,
MONTH(amortizacion_detalle.fecha_vencimiento) AS `mes`,
YEAR(amortizacion_detalle.fecha_vencimiento) AS `anno`,
Sum(amortizacion_detalle.valor_cuota) AS cuota
FROM
amortizacion_detalle
LEFT JOIN amortizacion ON amortizacion_detalle.amortizacion_id = amortizacion.id
LEFT JOIN banco AS b ON amortizacion.banco_id = b.id
WHERE
amortizacion.cliente_id = 10
GROUP BY
amortizacion_detalle.fecha_vencimiento
ORDER BY anno) as resultados
GROUP BY tipo, id_banco, nro_operacion, anno

This would be the result enter image description here

But what is needed is to be able to do it in Laravel by bringing or improving the result of said query.

$facs = DB::table('amortizacion_detalle')
    ->select(DB::raw('tipo, id_banco, nro_operacion, anno,  
    sum(CASE  WHEN mes = 1 THEN cuota ELSE 0 END) AS Enero,
    sum(CASE  WHEN mes = 2 THEN cuota ELSE 0 END) AS Febrero,
    sum(CASE  WHEN mes = 3 THEN cuota ELSE 0 END) AS Marzo,
    sum(CASE  WHEN mes = 4 THEN cuota ELSE 0 END) AS Abril,
    sum(CASE  WHEN mes = 5 THEN cuota ELSE 0 END) AS Mayo,
    sum(CASE  WHEN mes = 6 THEN cuota ELSE 0 END) AS Junio,
    sum(CASE  WHEN mes = 7 THEN cuota ELSE 0 END) AS Julio,
    sum(CASE  WHEN mes = 8 THEN cuota ELSE 0 END) AS Agosto,
    sum(CASE  WHEN mes = 9 THEN cuota ELSE 0 END) AS Septiembre,
    sum(CASE  WHEN mes = 10 THEN cuota ELSE 0 END) AS Octubre,
    sum(CASE  WHEN mes = 11 THEN cuota ELSE 0 END) AS Noviembre,
    sum(CASE  WHEN mes = 12 THEN cuota ELSE 0 END) AS Diciembre'))
    ->where('amortizacion.cliente_id', '=', $request->input('cliente_id'))
    ->get();
php

2 Answers

  1. Best Answer

    Your query would look like this if you use the Query Builder :

    $resultados = DB::query()
            ->fromSub(function ($query) {
                $query->select(
                    'amortizacion.tipo',
                    'b.id AS id_banco',
                    'amortizacion.nro_operacion',
                    DB::raw('MONTH(amortizacion_detalle.fecha_vencimiento) AS mes'),
                    DB::raw('YEAR(amortizacion_detalle.fecha_vencimiento) AS anno'),
                    DB::raw('SUM(amortizacion_detalle.valor_cuota) AS cuota'))
                ->from('amortizacion_detalle')
                ->leftJoin('amortizacion', 'amortizacion.id', 'amortizacion_detalle.amortizacion_id')
                ->leftJoin('banco AS b', 'b.id', 'amortizacion.banco_id')
                ->where('amortizacion.cliente_id', 10)
                ->groupBy('amortizacion_detalle.fecha_vencimiento')
                ->orderBy('anno');
            }, 'resultados')
        ->select(
            'tipo',
            'id_banco',
            'nro_operacion',
            'anno',
            DB::raw('SUM(CASE  WHEN mes = 1 THEN cuota ELSE 0 END) AS Enero'),
            DB::raw('SUM(CASE  WHEN mes = 2 THEN cuota ELSE 0 END) AS Febrero'),
            DB::raw('SUM(CASE  WHEN mes = 3 THEN cuota ELSE 0 END) AS Marzo'),
            DB::raw('SUM(CASE  WHEN mes = 4 THEN cuota ELSE 0 END) AS Abril'),
            DB::raw('SUM(CASE  WHEN mes = 5 THEN cuota ELSE 0 END) AS Mayo'),
            DB::raw('SUM(CASE  WHEN mes = 6 THEN cuota ELSE 0 END) AS Junio'),
            DB::raw('SUM(CASE  WHEN mes = 7 THEN cuota ELSE 0 END) AS Julio'),
            DB::raw('SUM(CASE  WHEN mes = 8 THEN cuota ELSE 0 END) AS Agosto'),
            DB::raw('SUM(CASE  WHEN mes = 9 THEN cuota ELSE 0 END) AS Septiembre'),
            DB::raw('SUM(CASE  WHEN mes = 10 THEN cuota ELSE 0 END) AS Octubre'),
            DB::raw('SUM(CASE  WHEN mes = 11 THEN cuota ELSE 0 END) AS Noviembre'),
            DB::raw('SUM(CASE  WHEN mes = 12 THEN cuota ELSE 0 END) AS Diciembre'))
        ->groupBy('tipo', 'id_banco', 'nro_operacion', 'anno')
        ->get();

    The only new thing would be the use of fromSub($query, $alias)that was a method added in the version 5.6(I don't know what version of laravel you are using).

    The query speaks for itself, the logic is the same.

    First you create the subquery, which will be where you will get the data from, after that, you do a select() to get the columns from the previous subquery and do what you had already defined.

    • 1

  2. That query is too long to validate, and you will slow down the system, better take them to aprocedimiento almacenado (store procedure)

    Something like this

    CREATE PROCEDURE Reporte_cliente (IN cliente_id INT)
BEGIN
SELECT tipo, id_banco, nro_operacion, anno,  
sum(CASE  WHEN mes = 1 THEN cuota ELSE 0 END) AS Enero,
sum(CASE  WHEN mes = 2 THEN cuota ELSE 0 END) AS Febrero,
sum(CASE  WHEN mes = 3 THEN cuota ELSE 0 END) AS Marzo,
sum(CASE  WHEN mes = 4 THEN cuota ELSE 0 END) AS Abril,
sum(CASE  WHEN mes = 5 THEN cuota ELSE 0 END) AS Mayo,
sum(CASE  WHEN mes = 6 THEN cuota ELSE 0 END) AS Junio,
sum(CASE  WHEN mes = 7 THEN cuota ELSE 0 END) AS Julio,
sum(CASE  WHEN mes = 8 THEN cuota ELSE 0 END) AS Agosto,
sum(CASE  WHEN mes = 9 THEN cuota ELSE 0 END) AS Septiembre,
sum(CASE  WHEN mes = 10 THEN cuota ELSE 0 END) AS Octubre,
sum(CASE  WHEN mes = 11 THEN cuota ELSE 0 END) AS Noviembre,
sum(CASE  WHEN mes = 12 THEN cuota ELSE 0 END) AS Diciembre
from (
SELECT amortizacion.tipo, b.id as id_banco, amortizacion.nro_operacion,
MONTH(amortizacion_detalle.fecha_vencimiento) AS `mes`,
YEAR(amortizacion_detalle.fecha_vencimiento) AS `anno`,
Sum(amortizacion_detalle.valor_cuota) AS cuota
FROM
amortizacion_detalle
LEFT JOIN amortizacion ON amortizacion_detalle.amortizacion_id = amortizacion.id
LEFT JOIN banco AS b ON amortizacion.banco_id = b.id
WHERE
amortizacion.cliente_id = cliente_id
GROUP BY
amortizacion_detalle.fecha_vencimiento
ORDER BY anno) as resultados
GROUP BY tipo, id_banco, nro_operacion, anno
END

    you invoke from Laravel

    $cliente_id = 10;
$report = \DB::select('CALL Reporte_cliente (?)', array($cliente_id));

    $report would have your list of data

    • -1