Left Join Query On Laravel 5.3

297
May 17, 2017, at 09:13 AM

I have a query on my mySQL like this

select p.tgl_pesan, COALESCE(SUM(dp.jumlah), 0) as jumlah 
FROM pemesanan p 
LEFT JOIN detail_pemesanan dp ON p.id_pemesanan = dp.id_pemesanan 
WHERE p.tgl_pesan >= DATE_ADD(NOW(), INTERVAL -6 day) 
GROUP BY p.tgl_pesan

And I tried to apply that query to my Laravel project like this

$data = DB::table('detail_pemesanan')
      ->leftJoin('pemesanan', 'detail_pemesanan.id_pemesanan', '=', 'pemesanan.id_pemesanan')
      ->select('pemesanan.tgl_pesan as tgl_pesan', DB::raw('SUM(detail_pemesanan.jumlah) as jumlah'))
      ->where('tgl_pesan', '>=', 'DATE_ADD(NOW(), INTERVAL -6 DAY)')
      ->groupBy('pemesanan.tgl_pesan')
      ->get();

But both of that show different results. Result on my Laravel project shows unexpected data like mySQL do, it shows more data from my interval. Is there anything wrong with my query on my Laravel project? Thank you so much

Answer 1

your sql query will be:

 $data = DB::table('pemesanan as p')
      ->select('p.tgl_pesan', DB::raw('SUM(dp.jumlah) as jumlah'))
      ->leftJoin('detail_pemesanan as dp', 'p.id_pemesanan', '=', 
     'dp.id_pemesanan')
      ->whereRaw('p.tgl_pesan  >= DATE_ADD(NOW(), INTERVAL -6 DAY)')
      ->groupBy('p.tgl_pesan')
      ->get();
READ ALSO
php to get JSON problems [on hold]

php to get JSON problems [on hold]

The learning continuesI thought I had it figured out to get data from MySQL using php and converting it to JSON format

177
Whats the best way to have a RESTful python server deliver info from a MAMP sql database?

Whats the best way to have a RESTful python server deliver info from a MAMP sql database?

I've created a toy database with MAMP and can use the example on MAMP's start page to get python to connect to itHowever, I want to use RESTful http calls from another app to have my python server query the database and send it back

190
Why does read after right method not produce consistent results?

Why does read after right method not produce consistent results?

I'm watching this video about Akkanet and the speaker says read after right does not produce consistent results because the order of events is not predictable at the network level

171
mysql query running forever

mysql query running forever

I have the following MYSQl query and it is taking ages too run: all joined tables have million of records:

206