在数据库级别按预先加载的数据过滤模型

Question

In the laravel docs they discuss eager loading related data to solve the N+1 query problem. I would like to filter my data based on the data from these relationships, without having to iterate through the results (that is, I would like this done at query time)

An example used in the docs is as follows:

$books = App\Book::with('author.contacts')->get();

If I wanted to filter these books to only include those who's author lives in the zip code 12345, how would I do that?

Neither of the following queries work for me:

$books = App\Book::with('author.contacts')->where('zip', 12345)->get();
$books = App\Book::with('author.contacts')->where('author.contacts.zip', 12345)->get();

Is there a simple way to do this in Eloquent?

Answer 1

In order to achieve what you're looking for you can use the following query:

App\Book::with('author.contacts')
->whereHas('author.contacts' => function($q){ 
     $q->where('zip', 12345)}
)
->get();

Arguably you could also use a similar query in the contacts model, something along the lines of this:

public function withZipCode()
{
    return Contacts::where('zip', 12345);
}

I honestly haven't tried it, but it should work.