Laravel 5.2 subtraction in the same column

Question

Laravel 5.2 subtraction in the same column

Navigation

#1 by (0 votes)

1

I'm starting out in Laravel (5.2) and I need to mount query down in Builder ! Pretty much it subtracts the previous value from the same column.

It works normally, but I'm migrating to Laravel.

$query = "SELECT tbl_leitura_gas.id_leitura
                ,tbl_leitura_gas.data_leitura
                ,tbl_leitura_gas.valor_leitura
                ,tbl_leitura_gas.data_cadastro
                ,ifnull(valor_leitura -
                        (SELECT mt.valor_leitura
                           FROM tbl_leitura_gas mt
                          WHERE mt.data_leitura < tbl_leitura_gas.data_leitura
                          ORDER BY mt.data_leitura DESC LIMIT 0
                                  ,1)
                       ,0) AS consumo_m3
            FROM tbl_leitura_gas
           ORDER BY tbl_leitura_gas.data_leitura DESC";

Thanks in advance for any help, Valdir.

laravel

asked by anonymous 18.05.2016 / 17:20

1 answer

How to insert checkbox dynamically from Python / Flask code? How to insert an id in SQLITE android?

score 0 · Answer 1

Hello, I struggled a bit and I got this expression, I do not know if it is the ideal but it is working (I also added a difference between dates)

$leitura_gas = DB::table('leitura_gas')
      ->select('id','id_apto', 'valor_leitura', 'data_leitura', 'obs_leitura',
        DB::raw('IFNULL(valor_leitura - (SELECT  mt.valor_leitura FROM leitura_gas mt WHERE mt.data_leitura < leitura_gas.data_leitura ORDER BY mt.data_leitura DESC LIMIT 0,1), 0) AS consumo_m3'),
        DB::raw('DATEDIFF(data_leitura, (SELECT mt.data_leitura FROM leitura_gas mt WHERE mt.data_leitura < leitura_gas.data_leitura ORDER BY mt.data_leitura DESC LIMIT 0,1)) AS consumo_dias'))
      ->orderBy('data_leitura', 'DESC')

      ->where('id_apto', '=', $apto)
      ->where(DB::raw('YEAR(data_leitura)'), '=', $ano_leitura)