Laravel Eloquent - Filtering results and eager loaded results

Question

So here is the use case:

You have users and they have many addresses. Say you want to filter and eager load on addresses (say...only US addresses). In order to do so you have to filter the eager load AND filter using whereHas so your result set is only those users with US addresses and only their US addresses are returned. This seems a bit clunky.

Here is an example:

(new App\User)
    ->with(['address' => function(){/* filter code */}])
    ->whereHas('address', function(){/* pretty much the same filter code */})
    ->get()

Is there a better way to do this? I feel like I am missing something.

score 6 · Accepted Answer · edited Sep 07 '20 at 06:20

6

You're confusing whereHas and with.

The with method will let you load the relationship only if the query returns true.

The whereHas method will let you get only the models which have the relationship which returns true to the query.

So here :

(new App\User)->with(['address' => function($query){/* filter code */}])->get();

will return all the users, but will only eager load the address when the filter code returns true.

And here :

(new App\User)->whereHas('address', function($query){/* filter code */})->get();

will return only the Users which have their relationship "address" returning true on the filter code.

edited Sep 07 '20 at 06:20

miken32

103
4

answered Jun 15 '16 at 20:15

Steve Chamaillard

1,691
1
11
20

I get that, but on most things I end up having to filter the relationships returned (with) using the same filter I filleted the model with. – Bill Garrison Jun 15 '16 at 20:17
Just wondering if their is a better way. – Bill Garrison Jun 15 '16 at 20:18
`whereHas` should be enough to solve your problem as it should only return the users which have the right address. That means you shouldn't have to filter again on the relationship. – Steve Chamaillard Jun 15 '16 at 20:20
But what if I filter using whereHas but also want to eager load the relationship that was filleted on by the same filter? – Bill Garrison Jun 15 '16 at 20:22
Then, use `with` without any closure, so only `whereHas(['address...)->with('address')->get()`, that'll load the users you want **and** eager load their relationship along with it. – Steve Chamaillard Jun 15 '16 at 20:24
But it will eager load all addresses will it not – Bill Garrison Jun 15 '16 at 20:25
2

Oh I see your problem. You want the users which have at least one "good" address, and then on these users you only want to load the right relationship(s). Then yes, you have to filter again then. It's still better in performance if you do `whereHas` before `with`, even though you have to do both. – Steve Chamaillard Jun 15 '16 at 20:27

score 0 · Answer 2 · answered Oct 18 '16 at 23:58

You're aware you can create custom relationships like? :

    class User extends Model implements...
    {
     ....

          function USAddresses(){
          return $this->
              belongsToMany(User::class,UserAddresses::class,'user_id','address_id')
              ->withPivot('postal_code')
              ->wherePivot('postal_code', !null );
     }
    } // end model

In your controller then:

    $this->users->with('USAddresses')->whereHas(...)->get();

This way, you have your filters already ready already. ( :

Laravel Eloquent - Filtering results and eager loaded results

2 Answers2