How can I convert many statement mysql dinamis to laravel eloquent?

Mysql query like this :

SET @sql_dinamis = (
    SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
            'SUM( IF(id_barang=', id_barang, ',jml_bk,0) ) AS br',
            id_barang
        )
    )
    FROM barang_keluar
);

SET @SQL = CONCAT(
    'SELECT month(tgl_keluar) as m, ',@sql_dinamis,'
    FROM barang_keluar
    WHERE month(tgl_keluar) and year(tgl_keluar)=2019
    GROUP BY month(tgl_keluar)'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I want to convert it to Laravel Eloquent, but I’m confused. Because there exist many statement. There exist PREPARE, EXECUTE, SET, DEALLOCATE etc. You can see query above.

How can I convert it to Laravel Eloquent?

Answer

You don’t need to manually prepare statements (PREPARE, EXECUTE, DEALLOCATE) in Laravel since the Query Builder calls PDO::prepare, PDO::bindValue and PDO::execute behind the scenes.

You will be responsable for escaping/sanitizing the input however.

You can achieve this query by using a few raw methods with the query builder.

After some experimentation, I found out the real sql query created by your code is something like this:

SELECT
    month(tgl_keluar) as m,
    SUM(IF(id_barang=1,jml_bk,0)) AS br42,
    SUM(IF(id_barang=2,jml_bk,0)) AS br48,
    SUM(IF(id_barang=3,jml_bk,0)) AS br13,
    SUM(IF(id_barang=4,jml_bk,0)) AS br14,
    .
    .
    .
    SUM(IF(id_barang=n-1,jml_bk,0)) AS brn-1
    SUM(IF(id_barang=n,jml_bk,0)) AS brn
FROM barang_keluar
WHERE month(tgl_keluar) AND year(tgl_keluar)=2019
GROUP BY month(tgl_keluar)

To translate this into the query builder, we’ll need 2 queries:

/**
 * Equivalent to
 * 
 * SELECT
 *     id_barang 
 * FROM barang_keluar;
 */
$ids_barang = DB::table('barang_keluar')
              ->select('id_barang')
              ->get();

/**
 * Equivalent to
 *
 * SELECT
 *     month(tgl_keluar) as m,
 * FROM barang_keluar
 * WHERE month(tgl_keluar) AND year(tgl_keluar)=2019
 * GROUP BY `m`;
 */

// Pass year as a variable if you want. You can also hardcode it
$year = 2019;
$query = DB::table('barang_keluar')
         ->selectRaw('month(tgl_keluar) as m')
         ->whereRaw('month(tgl_keluar) and year(tgl_keluar)=?', [$year])
         ->groupBy('m');

Since we didn’t call ->get(), we can still add to the query.

// Now, we add all the `SUM()` statements.
foreach ($ids_barang as $row) {
    $query->selectRaw("sum(if(eme_id=?,eme_empresa_id,0)) as br{$row->id_barang}", [$row->id_barang]);
}
// And finally, get the query results
$results = $query->get();

You can verify this produces the query by dumping $query->>toSql().