Laravel Search Query which meets conditions

Question

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

See this: https://stackoverflow.com/questions/16995102/laravel-eloquent-query-using-where-with-or-and-or about the usage of grouping in models — Nemoko, Mar 08 '22 at 09:27
Did you setup a model relationship between training and employees? I think that would be the best way to go about it, so for the employee you would do Employee::with(['training' => fn($query) => $query->where....]) that should already be scoped to that employee. WIth Admins you'd get them like before — NemoPS, Mar 08 '22 at 16:54

Wael Khalifa · Accepted Answer · 2022-03-11T07:50:40.563

0

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

edited Mar 11 '22 at 07:50

answered Mar 08 '22 at 09:42

Wael Khalifa

895
7
17

This code functions properly. I have another case for a different role which is manager. I have been trying the code. I have added it into the question above, could you look at it and let me know if I can make any improvements? – jypersona Mar 11 '22 at 01:00
1

I updated my answer to help with the last query – Wael Khalifa Mar 11 '22 at 07:53
Hi @Wael Khalifa, I have another question do you by any chance know how to do it? – jypersona Mar 25 '22 at 01:04
Hey @jypersona sure how can i help you – Wael Khalifa Mar 25 '22 at 07:01
It is a sortable function that I am trying to work out. I posted another question. If you have time and are willing please check that one out. I would really appreciate that – jypersona Mar 25 '22 at 07:03
let me know if you have an idea on that. Thanks – jypersona Mar 25 '22 at 07:40
1

I will check it – Wael Khalifa Mar 25 '22 at 11:13

score 0 · Answer 2 · answered Mar 08 '22 at 09:44

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

I don't have something like roleID, what do I do in this case? I uploaded a full version of my code on another question could you check that one? — jypersona, Mar 10 '22 at 09:19

score 0 · Answer 3 · answered Mar 08 '22 at 10:01

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

Laravel Search Query which meets conditions

3 Answers3