Laravel 5.2 subtraction in the same column

Question

I am starting in Laravel (5.2) and need to assemble the query down in the Builder! Practically it subtracts the previous value from the same column.

It works normally, but I’m migrating to the 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";

I already appreciate any help , Valdir.

Answer 1

Hello , I battled a little and I arrived in this expression, I do not know if it is 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)