How can I do a filter from multiple dropdown select on the view using one single query

On my view i have 4 dropdown selects where the user can select any of them, a combination of any, or all of them, clicks on a button and it queries the db and returns the result. In trying to implement this, I thought of using different queries for different dropdowns, but I can't seem to get the query that should execute when all is selected.

$stat = request('status');
        $network = request('network');
        $search = request('search');
        $dt = request('date');

        $startDay = Carbon::now()->startOfDay();
        $endDay = Carbon::now()->endOfDay();

        $startWeek = Carbon::now()->startOfWeek();
        $endWeek = Carbon::now()->endOfWeek();

        $startMonth = Carbon::now()->startOfMonth();
        $endMonth = Carbon::now()->endOfMonth();
        if ($dt == 'today') {$date = [$startDay, $endDay];} elseif ($dt == 'week') {$date = [$startWeek, $endWeek];} elseif ($dt == 'month') {$date = [$startMonth, $endMonth];} else { $date = null;}

        if (!$network && !$search && !$stat && !$date) {
            $transactions = auth()->user()->orders()->latest()->paginate(10);
        } elseif ($network) {
            $transactions = auth()->user()->orders()
                ->whereHas('product.category', function ($query) use ($network) {
                    $query->where('name', $network);
                })
                ->latest()->paginate(10);
        } elseif ($stat) {
            if ($stat == 'success') {$status = 1;} elseif ($stat == 'failed') {$status = 0;} else { $status = 1;}
            $transactions = auth()->user()->orders()
                ->where('paid', $status)
                ->latest()->paginate(10);
        } elseif ($date) {
            if ($dt == 'today') {$date = [$startDay, $endDay];} elseif ($dt == 'week') {$date = [$startWeek, $endWeek];} elseif ($dt == 'month') {$date = [$startMonth, $endMonth];} else { $date = [$startMonth, $endMonth];}
            $transactions = auth()->user()->orders()
                ->wherebetween('created_at', $date)
                ->latest()->paginate(10);
        } elseif ($stat || $network || $date) {
            $status = $stat == 'success' ? 1 : $stat == 'failed' ? 0 : 1;
            if ($dt == 'today') {$date = [$startDay, $endDay];} elseif ($dt == 'week') {$date = [$startWeek, $endWeek];} elseif ($dt == 'month') {$date = [$startMonth, $endMonth];} else { $date = [$startMonth, $endMonth];}
            $transactions = auth()->user()->orders()
                ->where('paid', $status)
                ->whereHas('product.category', function ($query) use ($network) {
                    $query->where('name', $network);
                })->wherebetween('created_at', $date)
                ->latest()->paginate(10);
        } elseif ($search) {
            $transactions = auth()->user()->orders()
                ->where('phone', 'LIKE', "%$search%")
                ->latest()->paginate(10);

        } else {
            $transactions = auth()->user()->orders()->latest()->paginate(10);

        }
        return view('dashboard.customers.transactions', compact('transactions'));

This is my code right now, please help me with a much simpler way to achieve this, I'll be so grateful

bobbybouwmann

6 years ago

Level 88

Your code is very unreadable. There is so much going on here..

In general, when you need to do filtering based on different inputs you simply build up the query. So in your case something like this

public function index(Request $request)
{
    $query = auth()->user()->orders();

    if ($request->has('network')) {
        $network = $request->input('network');

        $query = $query->whereHas('product.category', function ($query) use ($network) {
            $query->where('name', $network);
        });
    }

    // This is the default behaviour
    return $query->latest()->paginate(10);;
}

Well, I hope you get the idea. This way you can add all of the cases or one depending on what the user has selected.

1 like

guybrush_threepwood

6 years ago

Best Answer

Level 33

$paid = (request('status') == 'failed') ? 0 : 1;
$date = request('date');
$network = request('network');
$search = request('search');

$transactions = auth()->user()->orders()->where('paid', $paid)
   ->when($date == 'today', function ($q) {
      return $q->wherebetween('created_at', [Carbon::now()->startOfDay(), Carbon::now()->endOfDay()]);
   })
   ->when($date == 'week', function ($q) {
      return $q->wherebetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()]);
   })
   ->when($date == 'month', function ($q) {
      return $q->wherebetween('created_at', [Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()]);
   })
   ->when(request('network'), function ($q) use ($network) {
      return $q->whereHas('product.category', function ($sq) use ($network) {
         $sq->where('name', $network);                    
      });
   })
   ->when($search, function ($q) use ($search) {
      return $q->where('phone', 'like', "%$search%");
   })
   ->latest()->paginate(10);

return view('dashboard.customers.transactions', compact('transactions'));

pianistprogrammer

6 years ago

Level 1

will this method cover for when the user select two or more options at the same time?

like the user selects a network, a status (failed or success) and date

pianistprogrammer

6 years ago

Level 1

thanks very much, this was very helpful, these are things you don't see in documentation, hahaha. so happy right now

guybrush_threepwood

6 years ago

Level 33

You're welcome. Yes, it should account for any combination of status, date, network and search. Status being mandatory and the rest optional.

Please or to participate in this conversation.