Identifying which child matches in whereHas()

Published 3 weeks ago by bwrigley

Hello,

I have User and Address models in a one-to-many relationship.

In my code I find all Users that have at least one address in London

                  $users= \App\User::whereHas('addresses', function ($query) {

                                               $query->where('town', 'London');
                                      });

This works fine and returns me a list of users correctly.

However, is there an easy way to identify, from the User model, which Address matched the query.

Something like:


foreach ($users as $user){

    echo($user->addresses->matchedAddress->streetName);

}

Thanks

Ben

Best Answer (As Selected By bwrigley)
Swaz

@bwrigley Yea it should work the same way.

If you want to get rid of the duplication, you could make a custom query scope. I've done something like this in the past:

$users = User::withAndWhereHas('addresses', function ($query) {
        $query->where('town', 'London');
    })->get();
// User.php or BaseModel.php
public function scopeWithAndWhereHas($query, $relation, $callback = null)
{   
    if (is_callable($callback)) {
        return $query->with([$relation => $callback])
            ->whereHas($relation, $callback);
    }

    return $query->with($relation)->has($relation);
}
rodrigo.pedra

As your first call will return a Collection and there is collection method called where [ https://laravel.com/docs/5.5/collections#method-where ] available where you can further filter the collection for this additional condition.

    $users = \App\User::whereHas( /* ... */ );

    $usersInSomeAddress = $users->where( 'streetName', 'Some address here' );

If you need to refine more your results check other Collection's methods in the docs such as filter(...), for example.

bwrigley

I'm not sure I explained what I meant very well.

Let's say a User has two addresses, one in London and one in Paris.

If I run my first query above, this user matches as at least one address is in London and so it is returned in the collection.

Later on when I show information about the User, I wish to show the address that matched, and I'm hoping that something in the returned collection will identify it.

Are you saying that actually I have to run the search on the addresses twice? That seems inefficient to me.

Swaz
Swaz
3 weeks ago (42,855 XP)

You'll want to use the with method.

Querying Relationship Existence

Constraining Eager Loads

$users = User::whereHas('addresses', function ($query) {
        $query->where('town', 'London');
    })->with(['addresses' => function ($query) {
        $query->where('town', 'London');
    }])->get();
foreach ($users as $user) {
    foreach ($user->addresses as $address) {
        echo $address->street;
    }
}
bwrigley

Swaz that's brilliant thanks. Actually my query is a little more complex. Does the same thing apply? Simply repeat the subquery?

$users = \App\User::whereHas('addresses', function ($query) {

                             $query->where('location', 'near', [
                                       '$geometry' => [
                                                 'type' => 'Point',
                                                 'coordinates' => [
                                                           $this->first->getLongitude(),
                                                           $this->first->getLatitude(),
                                                 ],
                                       ],
                                       '$maxDistance' => $this->maxDistance,
                             ]);
                    })->with(['addresses' => function ($query) {
                                       $query->where('location', 'near', [
                                                 '$geometry' => [
                                                           'type' => 'Point',
                                                           'coordinates' => [
                                                                     $this->first->getLongitude(),
                                                                     $this->first->getLatitude(),
                                                           ],
                                                 ],
                                                 '$maxDistance' => $this->maxDistance,
                                       ]);
                              }])->paginate(9);
Swaz
Swaz
3 weeks ago (42,855 XP)

@bwrigley Yea it should work the same way.

If you want to get rid of the duplication, you could make a custom query scope. I've done something like this in the past:

$users = User::withAndWhereHas('addresses', function ($query) {
        $query->where('town', 'London');
    })->get();
// User.php or BaseModel.php
public function scopeWithAndWhereHas($query, $relation, $callback = null)
{   
    if (is_callable($callback)) {
        return $query->with([$relation => $callback])
            ->whereHas($relation, $callback);
    }

    return $query->with($relation)->has($relation);
}
rodrigo.pedra

Sorry, I must have misunderstood your explanation

@Swaz solution is better if you want to filter down the results directly from the database.

In my suggestion you would hit the database once, get all the user addresses and then filter the results further in memory. This is also useful if you want to show all the user's related addresses records and maybe highlight which made the user to return in the query.

But if you only want to show the user's matched addresses in the resultset go for the eager load constraining.

Please sign in or create an account to participate in this conversation.