How do I query through Many-to-Many?

In my Contact.php I have the relationship defined as:

...
    public function roles()
    {
        return $this->belongsToMany('App\Models\contact\ContactRole');
    }
...

in my controller, I have this query:

...
            $data = Contact::select("id", "company_id", "first_name", "last_name")
                ->where('first_name', 'LIKE', "%$search%")
                ->orWhere('last_name', 'LIKE', "%$search%")
                ->get();
...

and I'm trying to get Contacts that have a roles()->where('id', 5)

My roles table has two column, 'id' and 'name'. Should I not be using the id to search and instead look for the matching name column? Pivots are so confusing to me. The matching name to 'id' record 5 is 'PM Contact'. I wonder if I should be trying to match that instead of the id. Help!

Edit: Normally I'd google it, but trying to query google for "laravel with examples" get me example sof laravel not specifically the "with" statements.

MichalOravec

5 years ago

Level 75

$contacts = Contact::whereHas('roles', function ($query) {
    $query->where('id', 5);
})->get();

Docs: https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence

Talinon

5 years ago

Level 51

@fbc Just to add, try avoid string comparison whenever possible, which is exponentially slower than integer comparison.

fbc

5 years ago

Level 2

@michaloravec @talinon How would I go about integrating that solution?

something like this? maybe?:

            $contacts = Contact::whereHas('roles', function ($query) {
                $query->where('id', 5);
            })->get();
            $data = $contacts->select("id", "company_id", "first_name", "last_name")
                ->where('first_name', 'LIKE', "%$search%")
                ->orWhere('last_name', 'LIKE', "%$search%")
                ->get();

MichalOravec

5 years ago

Best Answer

Level 75

$contacts = Contact::where(function ($query) use ($search) {
    $query->where('first_name', 'like', "%{$search}%")->orWhere('last_name', 'LIKE', "%{$search}%");
})->whereHas('roles', function ($query) {
    $query->where('id', 5);
})->get();

With select, but I don't think that it is necessary.

$contacts = Contact::select('id', 'company_id', 'first_name', 'last_name')->where(function ($query) use ($search) {
    $query->where('first_name', 'like', "%{$search}%")->orWhere('last_name', 'LIKE', "%{$search}%");
})->whereHas('roles', function ($query) {
    $query->where('id', 5);
})->get();

1 like

fbc

5 years ago

Level 2

@michaloravec Wow.. that is a wild query for me to interpret! Thanks.!

Please or to participate in this conversation.