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
CodePudding user response:
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.