laravel 5.5 eloquent, searching into relationship collections

Question

In my Prospect table I have a few column with IDs stored as int that represent other table's items (client_id, consultant_id & house_id) , and other columns are straight data, example street, house_no, etc. I'm hooking IDs up with Eloquent relationships, but cannot figure out to have my index list to search into these relationships collections. Example client->first_name, client->email or house->name How do I get my search to look into for example if client->name is John, display when one search for 'john' ? I have:

Prospect.php model:

    protected $fillable = ['consultant_id', 'client_id', 'house_id', 'house_spec', 'elevation', 'lot_no', 'house_no', 'street', 'suburb', 'shire', 'estate_name', 'details', 'stage'];

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

    public function consultant()
    {
        // return $this->belongsTo('App\User', 'consultant', 'id');
        return $this->belongsTo(User::class);
    }

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

and my ProspectController.php

public function index(Request $request)
    {
        $keyword = $request->get('search');
        $perPage = 25;

        if (!empty($keyword)) {
            $prospects = Prospect::where('client_id', 'LIKE', "%$keyword%")
                ->orWhere('house_id', 'LIKE', "%$keyword%")
                ->orWhere('house_spec', 'LIKE', "%$keyword%")
                ->orWhere('elevation', 'LIKE', "%$keyword%")
                ->orWhere('lot_no', 'LIKE', "%$keyword%")
                ->orWhere('house_no', 'LIKE', "%$keyword%")
                ->orWhere('street', 'LIKE', "%$keyword%")
                ->orWhere('suburb', 'LIKE', "%$keyword%")
                ->orWhere('shire', 'LIKE', "%$keyword%")
                ->orWhere('estate_name', 'LIKE', "%$keyword%")
                ->orWhere('details', 'LIKE', "%$keyword%")
                ->orWhere('details', 'LIKE', "%$keyword%")
                ->paginate($perPage);
        } else {
            $prospects = Prospect::with('client')
            ->with('house')->paginate($perPage);
        }

        return view('prospects.index', compact('prospects'));
    }

score 1 · Accepted Answer · answered Jan 20 '18 at 06:43

1

use orWhereHas()

$prospects = Prospect::where('client_id', 'LIKE', "%$keyword%")
            ->orWhere('house_id', 'LIKE', "%$keyword%")
            ->orWhere('house_spec', 'LIKE', "%$keyword%")
            ->orWhere('elevation', 'LIKE', "%$keyword%")
            ->orWhere('lot_no', 'LIKE', "%$keyword%")
            ->orWhere('house_no', 'LIKE', "%$keyword%")
            ->orWhere('street', 'LIKE', "%$keyword%")
            ->orWhere('suburb', 'LIKE', "%$keyword%")
            ->orWhere('shire', 'LIKE', "%$keyword%")
            ->orWhere('estate_name', 'LIKE', "%$keyword%")
            ->orWhere('details', 'LIKE', "%$keyword%")
            ->orWhere('details', 'LIKE', "%$keyword%")
            ->orWhereHas('client',function($q) use ($keyword){
                 $q->where('first_name', 'LIKE', "%$keyword%")->orWhere('email', 'LIKE', "%$keyword%");
            })
            ->paginate($perPage);

answered Jan 20 '18 at 06:43

Sohel0415

9,523
21
30

Perfect and elegant @sohel0415 , I was looking for days for this. – Diego Jan 20 '18 at 06:55
That's what I'm doing ATM, but clients table is in a different database. instead of maindb.clients , it's anotherdb.clients, (my .env has DB_DATABASE_2=anotherdb and this is called mysql2 so i'm getting this error: "SQLSTATE[42S02]: Base table or view not found: 1146 Table 'maindb.clients' doesn't exist I have to make it look into anotherdb.clients .. any ideas? Thanks – Diego Jan 20 '18 at 07:04
may be you could change your connection logic in Client model, add this line in your model class `protected $connection = 'mysql2';` – Sohel0415 Jan 20 '18 at 07:08
this link may help you https://stackoverflow.com/questions/31847054/how-to-use-multiple-database-in-laravel – Sohel0415 Jan 20 '18 at 07:08
perfect, that's looks like where I referred when I build the 2nd db, really need to save this link. I think what I will need is connection at runtime as the rest I have it all, thanks again @sohel0405, you save me the day – Diego Jan 20 '18 at 07:29
glad it helps :) and don't forget to accept it, it may help others :) – Sohel0415 Jan 20 '18 at 07:31

laravel 5.5 eloquent, searching into relationship collections

1 Answers1