Laravel Search Query which meets conditions-CodePudding

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();

CodePudding user response：

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();

CodePudding user response：

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();

CodePudding user response：

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')}%")
        }
   });
}