I have a table where I want to search with username or daterange. The daterange search is working but I can't figure out how to add the username select value. Please see my code below:

User Table:

Sim Table:

Blade:

  <form action="" method="GET">
    <div >
        <div >
            <label for="username">Username</label>
            <input  type="text" name="username" value="{{ request()->query('username') }}" id="username">
        </div>
        <div >
            <label for="from">From</label>
            <input  type="date" name="from" value="{{ request()->query('from') }}" id="from">
        </div>
        <div >
            <label for="to">To</label>
            <input  type="date" name="to" value="{{ request()->query('to') }}" id="to">
        </div>
        <div >
            <div >
                <button  type="submit">Search</button>
                <a   href="{{ route('admin.sale') }}">Reset</a>
            </div>
        </div>

    </div>
</form>

User Model:

 public function sim(){
    return  $this->hasMany(Sim::class);
  }

Sim Model:

 public function user(){
  return  $this->belongsTo(User::class);
}

Controller:

   public function sale_index(request $request){

    $users = User::orderBy( 'fname', 'asc')->get();
    $sales = Sim::where('stock', '>', 0)->orderBy( 'id', 'desc')->get();

    if($request->filled('username')){
        $sales = Sim::where('user_id', '=', $request->username)->orderBy( 'id', 'desc')->get();
    }
    if($request->filled('from') AND $request->filled('to')){
        $sales = Sim::whereBetween('created_at', [$request->get('from'), $request->get('to')])->orderBy( 'id', 'desc')->get();
    }

    return view('admin.all_sale', compact('sales','users'));
}

Here I want to search in Sim table. Suppose, I want to see all the Sim of Jhon Doe. Here how can I match the fname and lname from user table with sim's table user_id column. So that when I search with the user name it will show all the sim of that user.

CodePudding user response：

I'm not sure if I understand it correctly, but you could search for the user and than call the sim method on him.

$user = User::where(DB::raw("CONCAT('fname', ' ', 'lname')"), '=', $request->data['username'])->first();
$sims = $user->sim()->get();

You here could do some other things with the search term from request. You could cast everything to lower. Please think about, that a user can exist twice with the same name. In this case you should user ->get() instead of ->first(). Than you get an collection, where you can run through.

CodePudding user response：

You can create a SIM instance first then add all queries by chaining with that instance.

Your controller should look like this:

public function sale_index(request $request){

    $users = User::orderBy( 'fname', 'asc')->get();
    $sim = Sim::query();

    if($request->filled('username')){
        $sim->where('user_id', '=', $request->username);
    }

    if($request->filled('from') AND $request->filled('to')){
        $sim->whereBetween('created_at', [$request->get('from'),$request->get('to')]);
    }

    $sales = $sim->orderBy( 'id', 'desc')->get();

    return view('admin.all_sale', compact('sales','users'));
}

Note: This code is not tested. Feel free to leave comments if it doesn't work.