How to apply a Laravel Eloquent where() condition on both parent and child (hasMany() relationship)

Question

I have a Laravel Eloquent Model called EmailList and a model called Subscriber. I defined the following hasMany relationship on the EmailList.

    public function subscribers() {
        return $this->hasMany(Subscriber::class);
    }

This relationship works fine.

I now want to apply a where() condition on the parent and a where() condition on the child.

For example, I have two EmailLists, with a property id and a property called listname ("List A" and "List B"). My Subscriber model has four relevant properties: id, name, email and email_list_id.

Problem: I want to take all subscribers from a certain list, e.g. 'List A', and filter the subscribers with $query (search functionality with Livewire).

In order to accomplish this, I came up with the following code:

EmailList::where('listname', $listname)->first()
                                    ->subscribers()
                                    ->where('name', 'LIKE', '%'.$this->search.'%')
                                    ->orWhere('email', 'LIKE', '%'.$this->search.'%')
                                    ->paginate(50) //or get(), if you like

This code partially worked, because the subscribers were indeed filtered, but not the EmailList. In other words, I just searched all subscribers, rather than the subscribers on a specific list.

After a lot of searching I figured out that I should perhaps use a whereHas(), but when I tried that, it gave me only EmailLists and not Subscribers.

It'd be great if someone could help me filter this. Perhaps it's peanuts for an experienced developer😊, but it would help me a lot!

Update: After seeing the answer by EricLandheer, I realised that (of course) I also have the EmailList id. Perhaps that makes it a little bit simpler, so that you can use find() instead of a where()->first() clause. (I use first() because the ‘listname’ is unique and I only want one instance. Is that correct?)

Answer 1

Currently, your code only returns the first EmailList. Did you that purposely, or do you only want the first EmailList that has the $listname?

Here is a solution using Eager loading with constraints that overrides the subscribers relation with a callback. The result should be all EmailLists with the $listname and the matching subscribers with the search.

EmailList::where('listname', $listname)
    ->with('subscribers', function($query) {
        return $query->where('name', 'LIKE', '%'.$this->search.'%')
            ->orWhere('email', 'LIKE', '%'.$this->search.'%')
    })->get();

Regarding question update

Indeed, using Listname is fine. If it is unique, no problem. You can use find() with an ID to, which is preferable if you're thinking of changing the name of the EmailList at a later stage.

Perhaps a more elegant / readable solution would be:

$emailList = EmailList::firstWhere('listname', $listname);

$emailList->subscribers = $emailList->subscribers()
    ->where('name', 'LIKE', '%'.$this->search.'%')
    ->orWhere('email', 'LIKE', '%'.$this->search.'%')
    ->get();

You could refactor the search to a model / service if you are using it in more places:

// EmailList model

public function subscribers() 
{
    return $this->hasMany(Subscriber::class);
}

public function searchSubscribers(string $search): Collection 
{
    return $this->subscribers()
        ->where('name', 'LIKE', '%'.$this->search.'%')
        ->orWhere('email', 'LIKE', '%'.$this->search.'%')
        ->get();
}

Final code

$emailList = EmailList::firstWhere('listname', $listname);

$emailList->subscribers = $emailList->searchSubscribers($this->search);