如果请求为0，则Laravel查询获取所有请求

Question

AT front-end I have some select buttons (choose an provider, choose a user, date range period).

<select class="form-control" id="provider">
      <option value="0">ALL</option>
      <option value="1">First</option>
      <option value="2">Second</option>
      <option value="3">Third</option>
      <option value="4">Fourth</option>

    </select>
<select class="form-control" id="res_user">
          <option value="0">ALL</option>
          <option value="13">James</option>
          <option value="27">Perica Aleksov</option>
          <option value="30">sad</option>
          <option value="32">Test Restaurant</option>
          <option value="33">dsfdf</option>
        </select>

so now I need to build Laravel query to fetch data from a database for this requests and it's not a problem when I choose some option but I have a problem where user choose 'ALL' option... how to handle that in Laravel controller?

I create something like:

if ($request->provider == 0 AND $request->res_user== 0) {
    $vouchers = Voucher::latest()
        ->where('user_id',$request->account)
        // ->where('created_by',$request->res_user)
        // ->where('source',$request->provider)
        ->where('created_at', '>=',  Carbon::parse($request->startDate))
        ->where('created_at', '<=',  Carbon::parse($request->endDate))
        ->get();
} elseif ($request->provider == 0) {
    $vouchers = Voucher::latest()
        ->where('user_id',$request->account)
        ->where('created_by',$request->res_user)
        // ->where('source',$request->provider)
        ->where('created_at', '>=',  Carbon::parse($request->startDate))
        ->where('created_at', '<=',  Carbon::parse($request->endDate))
        ->get();
} elseif ($request->res_user== 0) {
    $vouchers = Voucher::latest()
        ->where('user_id',$request->account)
        // ->where('created_by',$request->res_user)
        ->where('source',$request->provider)
        ->where('created_at', '>=',  Carbon::parse($request->startDate))
        ->where('created_at', '<=',  Carbon::parse($request->endDate))
        ->get();
} else {
    $vouchers = Voucher::latest()
        ->where('user_id',$request->account)
        ->where('created_by',$request->res_user)
        ->where('source',$request->provider)
        ->where('created_at', '>=',  Carbon::parse($request->startDate))
        ->where('created_at', '<=',  Carbon::parse($request->endDate))
        ->get();
}

Is there a more elegant way than this I use?

So how to fetch all data when request->provider is 0 without make if {} else {} each time ?

Is there some way to do that ?

Answer 1

Use when:

$vouchers = Voucher::latest()
    ->when($request->provider != 0, function ($q) use ($request) {
        $q->where('source', $request->provider);
    })
    ->when($request->res_user != 0, function ($q) use ($request) {
        $q->where('created_by', $request->res_user);
    })
    ->where('created_at', '>=',  Carbon::parse($request->startDate))
    ->where('created_at', '<=',  Carbon::parse($request->endDate))
    ->get()

For L5.1 I'd do this:

$baseQuery = Voucher::latest()
    ->where('created_at', '>=',  Carbon::parse($request->startDate))
    ->where('created_at', '<=',  Carbon::parse($request->endDate));

if ($request->provider != 0) {
    $baseQuery = $baseQuery->where('source', $request->provider);
}
if ($request->res_user != 0) {
    $baseQuery = $baseQuery->where('created_by', $request->res_user);
}

$vouchers = $baseQuery->get();