CODEWITHSUNDEEP
phplaravel
ayush srivastava
ayush srivastava

Reputation: 49

laravel 6 search query with multiple conditions

I have a search form with four fields of customer email, vendor name, status, and fromdate, todate,. My search filter is not working properly. I want if someone searches a lead with vendor name and status Active then it shows only leads of that vendor with status active status but here it shows a leads with both Accept or Reject also my date filter is not working so please help me. Please guide me

my controller code is

    public function search(Request $request){
    $users = DB::table('users')->where('is_admin', Null)->get();

    $customer_email = $request->input('customer_email');
    $vendor_id = $request->input('vendor_id');
    $status = $request->input('lead_status');

    $leads = DB::table('leads')
        ->leftJoin('users', 'leads.vendor_id', '=', 'users.id')
        ->select('leads.*', 'users.name')
        ->where('vendor_id', $vendor_id)
        ->orWhere('customer_email', $customer_email)
        ->orWhere('lead_status', $status)
        ->orWhere('leads.created_at', array($request->start_date, $request->end_date))
        ->orderBy('leads.created_at', 'DESC')->get();


    //dd($leads);

    return view('admin.view-leads', compact('leads'), compact('users'));

    
}

please help. Thanks in advance

query image

Upvotes: 1

Views: 1052

Answers (2)

Ankit Singh
Ankit Singh

Reputation: 922

That's a pretty simple thing. Just follow the code below.

  1. To get the users where is_admin is null.

    $users = DB::table('users')->whereIsNull('is_admin')->get();

  2. To check if you are submitting a value for any filter.

    if($request->filled('name_of_filter_input')){ //code goes here }

So your query to filter records will go like.

$query = DB::table('leads')->query();
$query->leftJoin('users', 'leads.vendor_id', '=', 'users.id');
$query->select('leads.*', 'users.name');

if($request->filled('vendor_id')) {
    $query->where('vendor_id', $request->input('vendor_id'));
}

if($request->filled('customer_email')) {
    $query->where('customer_email', $request->input('customer_email'));
}

if($request->filled('lead_status')) {
   $query->orWhere('lead_status', $request->input('lead_status'));
}

if($request->filled('start_date') && $request->filled('end_date')) {
    $query->whereBetween(DB::raw('date(leads.created_at)'), [$request->input('start_date'), $request->input('end_date')]);
} 

$leads = $query->orderBy('leads.id', 'DESC')->get();

Further you can replace DB::table('table_name') syntax with respective model classes in case query() seems to be undefined function.

Wrap leads.created_at with date() function and ensure your date filter(s) has date in yyyy-mm-dd format if the created_at column is of type timestamp.

Upvotes: 1

Muhammad Bilal
Muhammad Bilal

Reputation: 344

you should write your query like as below

$leads = DB::table('leads')
        ->leftJoin('users', 'leads.vendor_id', '=', 'users.id')
        ->select('leads.*', 'users.name')
        ->where('vendor_id', $vendor_id)
        ->when($customer_email, function($q,$customer_email){
             $q->where('customer_email', $customer_email);    
        })
        ->when($status, function($q,$status){
            $q->where('lead_status', $status);
        })
        ->where(function($q) use($request){
            if(isset($request->start_date) && isset($request->end_date)){
                $fromDate = date('Y-m-d H:i:s',strtotime($request->start_date));
                $toDate= date('Y-m-d H:i:s',strtotime($request->end_date));

                $q->where('leads.created_at', '>=', $fromDate)
                      ->where('leads.created_at', '<=', $toDate);
            }
        })
        ->orderBy('leads.created_at', 'DESC')->get();

I updated my answer please look into it

Upvotes: 0

Related Questions