Laravel 1 Query For Multiple DIfferent Result

Question

I am having problems getting total data with several conditions in 1 request, so the data I mean has status, namely ranap and ralan, where I want to calculate the total of all data, ralan data and ranap data. but it definitely produces a value of 0 in the last query run.

in my case ralan always gets value 0 because it was executed last time. and when I tried to look at the query I found that the last query had 2 where statuses, whereas in my data there was only 1 status, namely between ralan and ranap, one of them.

this is the code I have,

$msg = "Data detail pemberian obat";
$data = \App\Models\DetailPemberianObat::select('tgl_perawatan', 'jam', 'no_rawat');

if ($request->bulan) {
    $msg .= " bulan {$request->bulan}";
    $data = $data->whereMonth('tgl_perawatan', $request->bulan)->whereYear('tgl_perawatan', date('Y'));
} else {
    $msg .= " bulan ini ";
    $data = $data->whereMonth('tgl_perawatan', date('m'))->whereYear('tgl_perawatan', date('Y'));
}

$all = $data->count();
$ranap = $data->where('status', 'Ranap')->count();
$ralan = $data->where('status', 'Ralan')->count();

$msg .= " berhasil diambil";
return isSuccess([
    'all' => $all,
    'ranap' => $ranap,
    'ralan' => $ralan,
], $msg);

this is the result I got

{
    "success": true,
    "message": "Data detail pemberian obat bulan ini  berhasil diambil",
    "data": {
        "all": 32589,
        "ranap": 21136,
        "ralan": 0
    }
}

and the following is the query that I got when looking at the query from the ranal variable, the last one running the query to the database.

"select `tgl_perawatan`, `jam`, `no_rawat` from `detail_pemberian_obat` where month(`tgl_perawatan`) = ? and year(`tgl_perawatan`) = ? and `status` = ? and `status` = ?"

Actually, this problem can be solved by creating maybe 2 different variables to run queries to the database, say data and data2. but because I think by only using 1 variable the code I have is more concise and easier to read. However, if this is not possible, that is the only method that I can apply and think of for now.

maybe 1 more thing, If the total data is 1000 and the data is 500 then the other 500 can be said to be accurate data, but I'm quite doubtful about using this method.

Answer 1

the reason is because Laravel's query builder is mutable. When you call $data->where('status', 'Ranap')->count(), you're modifying the original $data query.

You can try this:

$msg = "Data detail pemberian obat";
$data = \App\Models\DetailPemberianObat::select('tgl_perawatan', 'jam', 'no_rawat');

if ($request->bulan) {
    $msg .= " bulan {$request->bulan}";
    $data = $data->whereMonth('tgl_perawatan', $request->bulan)->whereYear('tgl_perawatan', date('Y'));
} else {
    $msg .= " bulan ini ";
    $data = $data->whereMonth('tgl_perawatan', date('m'))->whereYear('tgl_perawatan', date('Y'));
}

$all = $data->count();
$ranap = (clone $data)->where('status', 'Ranap')->count(); 
$ralan = (clone $data)->where('status', 'Ralan')->count(); 

$msg .= " berhasil diambil";
return isSuccess([
    'all' => $all,
    'ranap' => $ranap,
    'ralan' => $ralan,
], $msg);