How to Convert Many Statement MySQL to Laravel Eloquent

How can I convert many statement mysql to laravel eloquent?

It's mostly raw queries:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
DB::statement('SET @sql = CONCAT(...)');
DB::statement('PREPARE stmt FROM @sql');
DB::statement('EXECUTE stmt');
DB::statement('DEALLOCATE PREPARE stmt');

Try this:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
$sql = DB::selectOne('select @sql')->{'@sql'};
ItemDetails::select('item_number', DB::raw('SUM(quantity) as total_quantity'))
    ->selectRaw($sql)
    ->groupBy('item_number')
    ->get();

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

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().

Convert MySQL Query Into Laravel Eloquent

You can't access the propertys because ->get() returns a collection of objects. For this to work you only need one object of your Eloquent Model.

If you only ever need one result you can replace ->get() with ->first() to directly receive the model object.

How to convert this raw sql query to laravel eloquent or query builder?

Try in this way.
For more details Check: https://laravel.com/docs/8.x/queries

$query  = DB::table('employees as emp')
    ->leftjoin('attendance_log as al','al.emp_id','emp.device_emp_id')
    ->join('departments as dep','dep.id','emp.department_id')
    ->select('emp.*', 'dep.department_name', 'al.auth_date' , DB::raw("MIN(al.auth_time) AS check_in, MAX(al.auth_time) AS check_out"))
    ->whereIn('al.emp_id', [1,2,3]) // use your array
    ->groupBy('emp.device_emp_id', DB::raw('DATE(al.auth_date_time)'));

    if($start_date){
        $query  = $query->where('al.auth_date' , '>=' , $start_date);
    }
    if($end_date){
        $query  = $query->where('al.auth_date' , '<=' , $end_date);
    }
  $data = $query->get();

Convert mysql query into Eloquent laravel query

Try this one, If you get a problem, please comment.

$value = \Auth::user()->id;

$query = DB::table('subarea')
             ->where('user_subarea.user_id', '=',$value)
             ->leftJoin('user_subarea', 'subarea.id', '=', 'user_subarea.subarea_id')
             ->leftJoin('branch', 'subarea.area_id', '=', 'branch.id')
             ->select('subarea.name AS subname','subarea.area_id', 'branch.name', 'user_subarea.id','user_subarea.lawyer_id','user_subarea.active_search')
             ->get();

how do i convert SQL query to laravel Eloquent?

The reason why you are getting Call to a member function links() on array is because the QueryBuilder returns an array. Not a collection as Eloquent.

I changed your SQL query to use double quotes in order to use string interpolation, and the work sum to product_sum, since sum is a reserved word by mysql.

"SELECT *, (( weight * 400 ) + making_charge) as product_sum FROM products WHERE {$cat} = {$id} ORDER BY product_sum ASC";

Your query, using eloquent, will look as somethin like this:

Product::query()
    ->where($cat, $id)
    ->select(['*', DB::raw('(weight * 400 + making_charge) as product_sum')])
    ->orderBy('product_sum')
    ->get();

This query returns a collection. You can also paginate using laravel pagination:

$items_per_page = 10;

Product::query()
    ->where($cat, $id)
    ->select(['*', DB::raw('(weight * 400 + making_charge) as product_sum')])
    ->orderBy('product_sum') // The default direction is ASC
    ->paginate($items_per_page);