Laravel groupby min / max过滤分组结果

Question

I've got a Laravel query working the way I'd like with min() and groupBy():

$certificates = CertificateRecord::whereHas('learner', function($query) use($user) { $query->where('user_id', '=', $user->id); })
->selectRaw('*,min(type) as type')
->groupBy('certificate_template_id')
->with('issuer','certificateType')
->paginate(10);

But when I add this to the mix...

->when($request->type, function ($query) use ($request) {
    return $query->where('type', '=', $request->type);
})

The previously grouped results are somehow ignored while performing this where() and it returns all ungrouped rows.

Answer 1

Edit: The original query with the when condition placement here seems fine to me. This should filter the type field without affecting the groupBy. One thing i want to tell you is that when the $request->type is set, there would be only one type value in the result set, therefore the minimum value would be the same without any change.

$certificates = CertificateRecord::whereHas('learner', function($query) use($user) {
    $query->where('user_id', '=', $user->id);
})
    ->with('issuer', 'certificateType')
    ->when($request->type, function ($query) use ($request) {
        return $query->where('type', $request->type);
    })
    ->selectRaw('*, min(type) as type')
    ->groupBy('certificate_template_id')
    ->paginate(10);

---

$certificates = CertificateRecord::whereHas('learner', function($query) use($user) {
        $query->where('user_id', '=', $user->id);
    })
    ->with('issuer', 'certificateType')
    ->selectRaw('*, min(type) as type')
    ->groupBy('certificate_template_id')
    ->when($request->type, function ($query) use ($request) {
        return $query->having('type', '=', $request->type);
    })
    ->paginate(10);

If that doesn't work, switch having to havingRaw.

return $query->havingRaw('min(type) = ' . $request->type);