Laravel 5.6: Get sum of a field from a table using sum and where

Question

Laravel 5.6: Get sum of a field from a table using sum and where

Navigation

#1 by (1 votes)

3

I have the following code:

$tiendas=Tienda::with('factura.cuenta.cuentapago.pago')
                    ->where('cuenta.id_tcuenta','=','1')
                    ->join('ventas.factura', 'tienda.id', '=', 'factura.id_tienda')
                    ->join('cuentas.cuenta', 'factura.id', '=', 'cuenta.id_factura')
                    ->leftJoin('cuentas.cuenta_pago', 'cuenta.id', '=', 'cuenta_pago.id_cuenta')
                    ->leftJoin('cuentas.pago', 'cuenta_pago.id_pago', '=', 'pago.id')
                    ->where('cuenta.pagada', '=', false)
                    ->where('factura.casa_m', '=', false)
                    ->where('cuenta.estatus', '=', true) // Solo tiendas con cuenta con status true
                    //->where('pago.estatus', '=', true)
                    ->select('tienda.*', 
                                DB::raw('COUNT(distinct cuenta.id) as n_facturas'), 
                                DB::raw('SUM(cuenta.m_total) as m_total'),
                                DB::raw('SUM(cuenta.m_iva) as miva_total'),
                                DB::raw('SUM(pago.m_total) as m_total2'))
                    ->groupBy('tienda.id','tienda.nombre')
                    ->get();

In m_total2 I sums the total amounts fields of the payment table. However, the payment table has another field called status that is Boolean type. I want to use the sum with a where payment.estatus = true.

I tried it this way but it gives me an error:

  DB::raw('SUM(pago.m_total) as m_total2')->where('pago.estatus', '=', true))

php eloquent laravel-5

asked by Kinafune 03.09.2018 в 23:24

source

1 answer

Problem with accents when compiling HTML in R-Markdown change the image the index of a stakedWidget using a QcomboBox

score 1 · Answer 1

Try the following query:

$tiendas=Tienda::where('cuenta.id_tcuenta','=','1')
                    ->join('factura', 'tienda.id', '=', 'factura.id_tienda')
                    ->join('cuenta', 'factura.id', '=', 'cuenta.id_factura')
                    ->leftJoin('cuenta_pago', 'cuenta.id', '=', 'cuenta_pago.id_cuenta')
                    ->leftJoin('pago', 'cuenta_pago.id_pago', '=', 'pago.id')
                    ->where('cuenta.pagada', '=', false)
                    ->where('factura.casa_m', '=', false)
                    ->where('cuenta.estatus', '=', true) 
                    ->where('pago.estatus', '=', true)
                    ->select('tienda.*', 
                                DB::raw('COUNT(distinct cuenta.id) as n_facturas'), 
                                DB::raw('SUM(cuenta.m_total) as m_total'),
                                DB::raw('SUM(cuenta.m_iva) as miva_total'),
                                DB::raw('SUM(pago.m_total) as m_total2'))
                    ->groupBy('tienda.id','tienda.nombre')
                    ->get();

Greetings!