Guidance for implementing search query

Apologies in advance as I'm probably missing some basic knowledge due to my patchwork Laravel education...

So I'm creating a training website and am trying to implement a way for admins to search for grade results by user first and last names. I have a standard 'users' table and a 'grades' table. Right now I can display all grades pretty easily:

user model:

public function grades(){
        return $this->hasMany('App\Grade');
    }

grade model:

public function user(){
        return $this->belongsTo('App\User');
    }

grade controller :

public function index($course){
        //
        $grades = Grade::orderBy('created_at', 'desc')->simplePaginate(20);
        return view('content/admin/gradebook', compact('grades'));
    }

blade template:

@foreach ($grades as $grade)
    <tr>
        <td>{{$grade->user->nameFirst}} {{$grade->user->nameLast}}</td>
        <td>{{$grade->created_at}}</td>
        <td>{{$grade->grade}} / {{$grade->gradeTotal}}</td>
        <td>{{($grade->grade) / ($grade->gradeTotal) * 100}}%</td>
        <td>view</td>
    </tr>
@endforeach

Once again, all of this displays all grades and their associated users without any issues. However, my search function in the Grades controller:

 public function search(Request $request){

        $input = $request->all();

        $searchFirst    = $request->input('searchFirst');
        $searchLast     = $request->input('searchLast');

        $grades = DB::table('grades')
        ->join('users', 'grades.user_id', '=', 'users.id')
        ->select('grades.*')

        ->when($searchFirst, function ($query) use ($searchFirst) {
            return $query->where('nameFirst', 'LIKE', '%' . $searchFirst . '%');
        })

        ->when($searchLast, function ($query) use ($searchLast) {
            return $query->where('user.nameLast', 'LIKE', '%' . $searchLast . '%');
        });

        session()->flash('search', array(
            $searchFirst,
            $searchLast,
        ));

        return view('content/admin/gradebook', compact('grades'));
    }

gives me

ErrorException (E_ERROR) Undefined property: Illuminate\Database\MySqlConnection::$user (View: C:\xampp...\gradebook.blade.php)

I don't understand why it returns this. I made a test route to search for a particular user:

Route::get('/testpage3', function() {

    $searchFirst = 'Herbert';

    $grades = DB::table('grades')
        ->join('users', 'grades.user_id', '=', 'users.id')
        ->select('grades.*')
        ->where('nameFirst', 'LIKE', '%' . $searchFirst . '%')
        ->get();

    echo $grades;
});

And this returns the correct data:

[{"id":3,"user_id":4,"course_id":1,"grade":8,"gradeTotal":20,"created_at":"2018-05-20 16:29:31","updated_at":null},{"id":23,"user_id":4,"course_id":1,"grade":14,"gradeTotal":20,"created_at":"2018-04-13 08:07:49","updated_at":null}]

The format of this output seems no different than the broad query I do in my index function for the Grade controller if I were to just echo it.

I think the problem is that I may not understand exactly how to handle the object being returned by my search function / test route. Am I going about all of this the wrong way? Am I missing something fundamental?

Any help is sincerely appreciated.

DirkZz

8 years ago

Level 22

Using the query builder you won't get a user relation like you are using in your blade file. You are basicly by passing all the models you made. Besides that in the select statement specifically request the grades data only.

You can start by testing the following;

$grades = Grade::with('users' )->get();

And then go from there. You can also add users.* to your select statement and get the data like that.

Personally if i were you i'd try both options and notice the differences for yourself a nice and fun training exercise ;)

1 like

Snapey

8 years ago

Best Answer

Level 122

In the index method you should eager load the user information, otherwise you have n+1 issue. (ie, many, many more queries than you want

public function index($course){
        
        $grades = Grade::with('user')->orderBy('created_at', 'desc')->simplePaginate(20);
        return view('content/admin/gradebook', compact('grades'));
    }

To do similar with search, and assuming you want grades as the primary object and not users;

    $users = User::when($searchFirst, function ($query) use ($searchFirst) {
            return $query->where('nameFirst', 'LIKE', '%' . $searchFirst . '%');
        })

        ->when($searchLast, function ($query) use ($searchLast) {
            return $query->where('user.nameLast', 'LIKE', '%' . $searchLast . '%');
        })
    ->pluck('id');


        $grades = Grade::with('user')
        ->whereHas('user', function($query) use($users){
            $query->whereIn('id',$users);
        })
        ->orderBy('created_at', 'desc')
        ->simplePaginate(20);

... or something like that!

1 like

bertmi01

8 years ago

Level 3

Whoa, thank you both! This totally solves my issue, blows my mind, and gives me a lot to play with from here. Appreciate it!

Please or to participate in this conversation.