[FIXED] How can I convert native PHP7 into query eloquent Laravel?

Issue

I'm migrating from php 7 to laravel and am having trouble completing the query. how to solve data query like the example below

$year = date('Y'); $month = date('m');

select id, tglupload, 
substring(CONVERT(varchar,tglupload,106),4,2) date, 
COUNT(1) as Totalemployeall
from [MS_SK] 
where substring(CONVERT(varchar,tglupload,106),4,2) = $month
and substring(CONVERT(varchar,tglupload,106),7,4) = $year
AND (status_allow <> 'NOTALLOW' OR status_allow is null)
GROUP BY rollup(id, tglupload)
order by id ASC

Solution

Unfortunately you have to use a raw query for that. But you can make your query nicer using the Laravel's scope function.

For example in your model (the model related with the table [MS_SK]) you can add the following 2 scope functions:

class YourModel extends Model {

    public function scopeFiltrateTglUpload($query, int $month=null, int $year=null)
    {
        $year = $year ?? date('Y');
        $month = $month ?? date('m');

        return $query->where(\DB::raw("substring(CONVERT(varchar,tglupload,106),4,2)", "=", $month))
            ->where(\DB::raw("substring(CONVERT(varchar,tglupload,106),7,4)", "=", $year));
    }

    public function scopeTheStatusIsNullOrNotAllowed($query)
    {
        return $query->where(function($subQuery) {
            $subQuery->where('status_allow', '<>', 'NOTALLOW')
                ->orWhereNull('status_allow');
        });
    }

}

Then you can use them as per below:

$result = YourModel::selectRaw('
        `id`, `tglupload`, 
        substring(CONVERT(varchar,tglupload,106),4,2) `date`, 
        COUNT(1) as `Totalemployeall`
    ')
    ->filtrateTglUpload()
    ->theStatusIsNullOrNotAllowed()
    ->groupBy(\Db::raw('rollup(id, tglupload)'))
    ->orderBy('id')
    ->get();

Please note that this is just an example to give you an idea. Then you should make it work :) Please tell me in the comment if you need any help.

Answered By - Dan