Ad

Is There Any Other Way To Do Filter With Laravel Query Builder?

- 1 answer

I am doing filter for displaying data and the code are getting longer since I use if else clause for every possible condition. Is there a better way to write these code?

This is one of the example code that I use on filter.

HTML code:

        <div class="well well-sm  col-xs-12">
            <div class="row">
                <div class="col-sm-2 col-xs-12">
                    <div class="form-group form-group-sm">
                        <label>Date &nbsp;</label>
                        <select class="form-control month" name="month">
                            <option value="1"> January </option>
                            <option value="2"> February </option>
                            <option value="3"> March </option>
                            <option value="4"> April </option>
                            <option value="5"> May </option>
                            <option value="6"> June </option>
                            <option value="7"> July </option>
                            <option value="8"> August </option>
                            <option value="9"> September </option>
                            <option value="10"> October </option>
                            <option value="11"> November </option>
                            <option value="12"> December </option>
                        </select>
                        <select class="form-control year" name="year">
                            <option value="2019"> 2019 </option>
                            <option value="2018"> 2018 </option>
                            <option value="2017"> 2017 </option>
                            <option value="2016"> 2016 </option>
                            <option value="2015"> 2015 </option>
                            <option value="2014"> 2014 </option>
                            <option value="2013"> 2013 </option>
                            <option value="2012"> 2012 </option>
                            <option value="2011"> 2011 </option>
                            <option value="2010"> 2010 </option>
                            <option value="2009"> 2009 </option>
                        </select>
                    </div>

                </div>

                <div class="col-sm-2 col-xs-12">
                    <div class="form-group form-group-sm">
                        <label>Customer</label>
                        <select class="form-control customer" name="customer">
                            <option value="all">All</option>
                            @foreach($customer_options as $key => $data)
                            <option value="{{ $data->ref_code}}">{{$data -> ref_code}}-{{ $data->name_t }}</option>
                            @endforeach
                        </select>
                    </div>
                </div>


                <div class="col-sm-3 col-xs-12">
                    <div class="form-group form-group-sm">
                        <label>Search Text&nbsp;&nbsp;&nbsp;</label>
                        <input type="text" name="search" class="form-control" id="search">
                        <input type="submit" name="" value="Show Data" id="show" class="btn btn-success btn-sm">
                    </div>
                </div>

            </div>
        </div>

the filter function in controller:

public function filterQAPassed(Request $request)
    {
        $current_year = $request['year'];
        $current_month = $request['month'];
        $customer_options = Ref::where('ref_group', 'C')->orderBy('name_t', 'asc')->get();
        $customer = $request['customer'];
        $search = $request['search'];

        if ($customer != 'all') {
            $process_trans = DB::table('process_trans')
            ->leftJoin('jt_d', function ($join) {
                $join->on('process_trans.doc_code', '=', 'jt_d.doc_code');
                $join->on('process_trans.book', '=', DB::raw('concat(jt_d.book,"-",jt_d.seq)'));
            })
            ->leftJoin('jt_h', function ($join) {
                $join->on('jt_d.doc_code', '=', 'jt_h.doc_code');
                $join->on('jt_d.book', '=', 'jt_h.book');
            })
            ->leftJoin('astaff', 'process_trans.staff_code', '=', 'astaff.staff_code')
            ->select(
                'process_trans.staff_code',
                'process_trans.p_code',
                'process_trans.book',
                'process_trans.qty',
                'jt_d.product_code',
                'jt_d.name_t as product_name',
                'jt_d.dwg_file',
                'jt_d.part_no',
                'jt_d.cost',
                'jt_h.cc',
                'jt_h.sale_name',
                'jt_h.ref_code',
                'process_trans.description',
                'astaff.name_t as staff_name',
                'process_trans.from_time as qa_date',
            )
            ->whereMonth('process_trans.from_time', $current_month)
            ->whereYear('process_trans.from_time', $current_year)
            ->where('process_trans.p_code2', '=', 'Finished')
            ->where('ref_code',$customer)
            ->where(function ($query) use ($search) {
                $query->where('jt_h.book', 'LIKE', '%' . $search . '%');
                $query->orWhere('jt_h.ref_name', 'LIKE', '%' . $search . '%');
                $query->orWhere('jt_h.reference', 'LIKE', '%' . $search . '%');
                $query->orWhere('jt_d.product_code', 'LIKE', '%' . $search . '%');
                $query->orWhere('process_trans.description', 'LIKE', '%' . $search . '%');
                $query->orWhere('astaff.name_t', 'LIKE', '%' . $search . '%');
            })
            ->orderBy('process_trans.from_time')
            ->get();

        return view('production_system.qa_passed', compact('search', 'current_month', 'current_year', 'process_trans','customer_options','customer'));
        }else if($customer =='all'){
            $process_trans = DB::table('process_trans')
            ->leftJoin('jt_d', function ($join) {
                $join->on('process_trans.doc_code', '=', 'jt_d.doc_code');
                $join->on('process_trans.book', '=', DB::raw('concat(jt_d.book,"-",jt_d.seq)'));
            })
            ->leftJoin('jt_h', function ($join) {
                $join->on('jt_d.doc_code', '=', 'jt_h.doc_code');
                $join->on('jt_d.book', '=', 'jt_h.book');
            })
            ->leftJoin('astaff', 'process_trans.staff_code', '=', 'astaff.staff_code')
            ->select(
                'process_trans.staff_code',
                'process_trans.p_code',
                'process_trans.book',
                'process_trans.qty',
                'jt_d.product_code',
                'jt_d.name_t as product_name',
                'jt_d.dwg_file',
                'jt_d.part_no',
                'jt_d.cost',
                'jt_h.cc',
                'jt_h.sale_name',
                'jt_h.ref_code',
                'process_trans.description',
                'astaff.name_t as staff_name',
                'process_trans.from_time as qa_date',
            )
            ->whereMonth('process_trans.from_time', $current_month)
            ->whereYear('process_trans.from_time', $current_year)
            ->where('process_trans.p_code2', '=', 'Finished')
            ->where(function ($query) use ($search) {
                $query->where('jt_h.book', 'LIKE', '%' . $search . '%');
                $query->orWhere('jt_h.ref_name', 'LIKE', '%' . $search . '%');
                $query->orWhere('jt_h.reference', 'LIKE', '%' . $search . '%');
                $query->orWhere('jt_d.product_code', 'LIKE', '%' . $search . '%');
                $query->orWhere('process_trans.description', 'LIKE', '%' . $search . '%');
                $query->orWhere('astaff.name_t', 'LIKE', '%' . $search . '%');
            })
            ->orderBy('process_trans.from_time')
            ->get();

        return view('production_system.qa_passed', compact('search', 'current_month', 'current_year', 'process_trans','customer_options','customer'));
        }
    }

Is there a better way of doing filter with cleaner code other than using extensive if-else clause? For the example above, there is only 1 option that needs to choose from, which is 'customer', either is getting all customer or choosing a specific one. For my other forms, many options need to be specified. What is the alternative way of writing these code without repeating the same things other than the conditions?

Ad

Answer

Yes, you can use conditional clauses for this.

->when($customer === 'all', function ($query) {
    // Special query parts for all customers
}, function ($query) {
    // Optional query parts for when not all customers
})
Ad
source: stackoverflow.com
Ad