'Laravel Search Query which meets conditions

Hi I have this code that is to search for records.

Admins are able to see all records, this is the query for admins

$trainings = Training::where('staffName', 'LIKE', "%{$request->search}%")
            ->orWhere('programTitle', 'LIKE', "%{$request->search}%")
            ->orWhere('trainer', 'LIKE', "%{$request->search}%")
            ->paginate();

Whereas Employees should only see their records, so how do I add this condition into the existing query for the search function? This is the query I have for the Employee search currently

$employees = Training::where('staffName',$username)
            ->orWhere('staffName', 'LIKE', "%{$request->search}%")
            ->orWhere('programTitle', 'LIKE', "%{$request->search}%")
            ->orWhere('trainer', 'LIKE', "%{$request->search}%")
            ->paginate();

I have another version for Managers, which is meant to also refer to their roles from a different table. I am trying it like this but it only is working properly when it comes to the Manager, but when the function fetches anything related to staff under them it fetches all records instead of specific records.

$managers = DB::table('trainings')
            ->join('users','users.id','=','trainings.staff_id')
            ->select('trainings.staffName','programTitle','trainer','trainingDate','hours')
            ->where('trainings.staffName',$username)
            ->orWhere('reportingTo',$username)
            ->where(function ($query) use ($request) {
            $query->where('trainings.staffName', 'LIKE', "%{$request->search}%")
                  ->orWhere('programTitle', 'LIKE', "%{$request->search}%")
                  ->orWhere('trainer', 'LIKE', "%{$request->search}%");
        })
        ->paginate();
phplaravel


Solution 1:[1]

Your usage of orWhere is wrong because orWhere remove all conditions if it's true try this code below

Training::where('staffName', $username)
                ->where(function ($query) {
                    $query->where('staffName', 'LIKE', "%{$request->search}%")
                          ->orWhere('programTitle', 'LIKE', "%{$request->search}%")
                          ->orWhere('trainer', 'LIKE', "%{$request->search}%");
                })
                ->paginate();

For the last query

$managers = DB::table('trainings')
                      ->join('users', 'users.id', '=', 'trainings.staff_id')
                      ->select('trainings.staffName', 'programTitle', 'trainer', 'trainingDate', 'hours')
                      ->where(function ($query) use ($username) {
                          $query->where('trainings.staffName', $username)
                                ->orWhere('reportingTo', $username);
                      })
                      ->where(function ($query) use ($request) {
                          $query->where('trainings.staffName', 'LIKE', "%{$request->search}%")
                                ->orWhere('programTitle', 'LIKE', "%{$request->search}%")
                                ->orWhere('trainer', 'LIKE', "%{$request->search}%");
                      })
                      ->paginate();

Solution 2:[2]

here's when query scopes comes to the rescue.

class Training extends Eloquent {

    public function scopeFilterByRole($query, $roleId, $searchInput)
    {
        if ($roleId === 3) { 
            return $query->where('staffName', 'LIKE', "%{$searchInput}%")
        }
        return $query;
    }
}

Then in your controller

Training::where('programTitle', 'LIKE', "%{$request->search}%")
        ->orWhere('trainer', 'LIKE', "%{$request->search}%")
        ->FilterByRole(3, $request->search)
        ->get();

Solution 3:[3]

You can write a global scope for that it will automatically apply in all existing queries:

protected static function boot()
{
    parent::boot();
    static::addGlobalScope(function (Builder $builder) {
        if (auth()->check() and auth()->user()->role == 3) {
            $builder->where('staffName', 'LIKE', "%{request()->get('search_name')}%")
        }
   });
}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 sid
Solution 3 Robius Sani

Related Questions