How to convert query mysql to laravel query

Question

Hai all I have this raw query in mysql I need to translate to laravel 5.4 query builder:

SELECT MONTH(calendar.datefield) AS Bulan, calendar.datefield AS Tanggal, IFNULL(SUM(dbk.jumlah),0) AS Jumlah 
FROM detail_brg_keluar AS dbk 
INNER JOIN penjualan AS pen ON pen.id = dbk.id_brg_keluar 
RIGHT JOIN calendar ON (DATE(pen.tgl_keluar) = calendar.datefield)
WHERE 
(calendar.datefield BETWEEN 
 (SELECT MIN(DATE(tgl_keluar)) FROM penjualan AS p
    INNER JOIN detail_brg_keluar AS detail ON p.id = detail.id_brg_keluar 
    WHERE detail.id_produk = 818002) 
 AND 
 (SELECT MAX(DATE(tgl_keluar)) FROM penjualan AS pe
    INNER JOIN detail_brg_keluar AS det ON pe.id = det.id_brg_keluar
    WHERE det.id_produk = 818002))
AND (dbk.id_produk = 818002 OR dbk.id_produk IS NULL)
GROUP BY MONTH(calendar.datefield)
ORDER BY calendar.datefield ASC

result of my query :

This my laravel code, and code error -_-

$proT = DB::table('detail_brg_keluar AS dbk')
    ->select( DB::raw('MONTH(calendar.datefield) AS Bulan, calendar.datefield AS Tanggal, IFNULL(SUM(dbk.jumlah),0) AS Jumlah '))
    ->join('penjualan AS pen', 'pen.id', '=', 'dbk.id_brg_keluar')
    ->join('calendar', DB::raw('DATE(pen.tgl_keluar)'), '=', 'calendar.datefield')
    ->whereBetween('calendar.datefield', [$from[0]->tanggalAwal, $to[0]->tanggalAkhir])
    ->where(DB::raw('dbk.id_produk = 818002 OR dbk.id_produk IS NULL'))
    ->groupBy(DB::raw('MONTH(calendar.datefield)'))
    ->orderBy(DB::raw('calendar.datefield', 'ASC'))
    ->get();

Code from and to :

$from = DB::table('penjualan AS p')
    ->select(DB::raw('MIN(DATE(tgl_keluar)) AS tanggalAwal'))
    ->join('detail_brg_keluar AS detail', 'p.id', '=', 'detail.id_brg_keluar')
    ->where('detail.id_produk', $id)
    ->get();

$to = DB::table('penjualan AS p')
    ->select(DB::raw('MAX(DATE(tgl_keluar)) AS tanggalAkhir'))
    ->join('detail_brg_keluar AS detail', 'p.id', '=', 'detail.id_brg_keluar')
    ->where('detail.id_produk', $id)
    ->get();

I have no idea anymore?

Thx all

Jonas Staudenmeir · Accepted Answer · 2018-06-20 01:51:06Z

1

Use this:

$from = DB::table('penjualan AS p')
    ->select(DB::raw('MIN(DATE(tgl_keluar)) AS tanggalAwal'))
    ->join('detail_brg_keluar AS detail', 'p.id', '=', 'detail.id_brg_keluar')
    ->where('detail.id_produk', $id);
$to = DB::table('penjualan AS p')
    ->select(DB::raw('MAX(DATE(tgl_keluar)) AS tanggalAkhir'))
    ->join('detail_brg_keluar AS detail', 'p.id', '=', 'detail.id_brg_keluar')
    ->where('detail.id_produk', $id);
$proT = DB::table('detail_brg_keluar AS dbk')
    ->select( DB::raw('MONTH(calendar.datefield) AS Bulan, calendar.datefield AS Tanggal, IFNULL(SUM(dbk.jumlah),0) AS Jumlah '))
    ->join('penjualan AS pen', 'pen.id', '=', 'dbk.id_brg_keluar')
    ->rightJoin('calendar', DB::raw('DATE(pen.tgl_keluar)'), '=', 'calendar.datefield')
    ->whereRaw('calendar.datefield BETWEEN ('.$from->toSql().') AND ('.$to->toSql().')', [$id, $id])
    ->where(function($query) {
        $query->where('dbk.id_produk', 818002)->orWhereNull('dbk.id_produk');
    })
    ->groupBy(DB::raw('MONTH(calendar.datefield)'))
    ->orderBy('calendar.datefield', 'ASC')
    ->get();

Don't execute $from and $to, use the generated SQL.
I replaced ->join('calendar' with ->rightJoin('calendar'.
I replaced where(DB::raw()) with a nested constraint.
orderBy() doesn't need a raw expression.

answered Jun 20, 2018 at 1:51

Jonas Staudenmeir

26.1k7 gold badges70 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Deza Farras Tsany Over a year ago

sorry, this code error : "SQLSTATE[42000]: Syntax error or access violation: 1055 'dbolshop.calendar.datefield' isn't in GROUP BY (SQL: select

Jonas Staudenmeir Over a year ago

I assume you only tested the raw query in phpMyAdmin? But not in Laravel (DB::select($sql);)?

Deza Farras Tsany Over a year ago

thanks for the solution, query works with a little modification

Deza Farras Tsany Over a year ago

yes, i just test query in phpMyAdmin !! because, i don't know test query in laravel

Collectives™ on Stack Overflow

How to convert query mysql to laravel query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related