Laravel eager loading and LIKE filter

Question

I am working with a typical Account->Zipcode scenario. I have 2 model2: Account and Zipcode, in Account I add

public function zipcode() {
    return $this->belongsTo(Zipcode::class);
}

The index.blade.php is like this:

<td>{{$account->id}}</td>
  <td>{{$account->fullname}}</td>
  <td>{{$account->address}}</td>
  <td>{{$account->zipcode->fulllocation}}</td>
...

I add a filter (basic input text on top of the list) for a string contained in accounts.fullname so the controller is like this:

public function index(Request $request)
 {
   $search = $request->search;
   //The instructions in the closure given to when() will only be applied if the first argument is evaluated to true
   $accounts = Account::with('zipcode')//https://laravel.com/docs/4.2/eloquent#eager-loading
                  ->when($search,function ($query) use ($search) {
                     $query->where('fullname','like','%'.$search.'%');
                     return $query;
                    })
                  ->latest()
                  ->paginate(15);
     return view('accounts.index', compact('accounts'))->with('search',$search);
 }

At this point the scenario in tested and working, if no search is inserted all accounts are listed else the result is correctly filtered.

My need is to search the inserted string in accounts.fullname OR accounts.zipcode.fulllocation. I try to modifiy the controller in this way:

$accounts = Account::with('zipcode')//https://laravel.com/docs/4.2/eloquent#eager-loading
    ->when($search,function ($query) use ($search) {
              $query->where('fullname','like','%'.$search.'%');

              $query->orWhere('fulllocation','like','%'.$search.'%');//ADDED THIS

              return $query;
           })
    ->latest()
    ->paginate(15);

When I try it this error is raised:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'fulllocation' in 'where clause' (SQL: select count(*) as aggregate from `accounts` where `fullname` like %foo% or `fulllocation` like %foo%)

Thanks to any kind of help.

Answer 1

The reason you're getting that error is because the query is looking for fulllocation in the accounts table when it's actually in the zipcodes table.

You could try something like:

$query->where('fullname', 'like', '%' . $search . '%')
    ->orWhereHas('zipcode', function ($q) use ($search) {
        $q->where('fulllocation', 'like', '%' . $search . '%');
    });

https://laravel.com/docs/5.3/eloquent-relationships#querying-relationship-existence

Hope this helps!