59
Laravel WhereHas() and With()
When accessing Eloquent relationships as properties, the related models are "lazy loaded". This means the relationship data is not actually loaded until you first access the property. However, Eloquent can "eager load" relationships at the time you query the parent model. Eager loading alleviates the "N+1" query problem. To illustrate the *N+1 *query problem, consider a Book model that "belongs to" to an Author model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Book extends Model
{
/**
* Get the author that wrote the book.
*/
public function author()
{
return $this->belongsTo(Author::class);
}
}We can use eager loading to reduce this operation to just two queries. When building a query, you may specify which relationships should be eager loaded using the with() method:
$books = Book::with('author')->get();
foreach ($books as $book) {
echo $book->author->name;
}select * from books
select * from authors where id in (1, 2, 3, 4, 5, …)When retrieving model records, you may wish to limit your results based on the existence of a relationship. For example, imagine you want to retrieve all authors that have books' titles start with PHP. To do so, you may pass the name of the relationship to the whereHas() method and define additional query constraints on your has queries
$authors = Author::whereHas('books', function (Builder $query) {
$query->where('title', 'like', 'PHP%');
})->get();select * from authors WHERE EXISTS(SELECT * FROM authors WHERE authors.id = books.author_id and books.title like 'PHP%');When this query gets executed you get all authors who have at least one book start with PHP, correct? Yes.
Now If you loop over the authors and access the book relationship like so,
Now If you loop over the authors and access the book relationship like so,
foreach ($authors as $author) {
echo $author->book->title;
}You will end up with N+1, and to solve it sure you will use with() method to eager load books:
$authors = Author::with('books')
->whereHas('books', function (Builder $query) {
$query->where('title', 'like', 'PHP%');
})
->get();select * from authors WHERE EXISTS(SELECT * FROM authors WHERE authors.id = books.author_id and books.title like 'PHP%');
select * from books where `books`.`author_id` in (1, 5, 11, 22, 46, 62, ….)So far so good, we solve N+1 issue, but WAIT did you notice something, well I do, the 2nd query gets all books from only the authors that selected from the 1st query, right? Yes.
Our first query did its job and gets us only authors that have books that start with PHP, but the 2nd query (eager load) will get us all books for each author, that means if I looped over the authors and call book relationship we will see also other books not only those start with PHP.
Our first query did its job and gets us only authors that have books that start with PHP, but the 2nd query (eager load) will get us all books for each author, that means if I looped over the authors and call book relationship we will see also other books not only those start with PHP.
[
App\Author : {
id: 1
name: "author 1",
…,
books: [
App\Books: {
….
title: 'PHP'
},
App\Books: {
….
title: 'Java'
},
App\Books: {
….
title: 'How to use'
},
…
]
}
…
]to get same results as we wanted from whereHas we need to use same condition query inside with() method.
$authors = Author::with(['books' => fn($query) => $query->where('title', 'like', 'PHP%')])
->whereHas('books', fn ($query) =>
$query->where('title', 'like', 'PHP%')
)
->get();select * from authors WHERE EXISTS(SELECT * FROM authors WHERE authors.id = books.author_id and books.title like 'PHP%');
select * from books where `books`.`author_id` in (1, 5, 11, 22, 25, 27, 35, 39, 46, 62, ….) and books.title like 'PHP%');I think you have already noticed the 2nd query has the same condition as 1st query.
Now, these are the results that we are looking for become:
Now, these are the results that we are looking for become:
[
App\Author : {
id: 1
name: "author 1",
…,
books: [
App\Books: {
….
title: 'PHP'
},
…
]
},
App\Author : {
id: 2
name: "author 2",
…,
books: [
App\Books: {
….
title: 'PHP'
},
App\Books: {
….
title: 'PHP Laravel'
},
…
]
}
…
] - - - - - -
Finally, doing this query over all places and repeating the same conditions, is cumbersome, so we will use a local scope in Author model
Finally, doing this query over all places and repeating the same conditions, is cumbersome, so we will use a local scope in Author model
public function scopeWithWhereHas($query, $relation, $constraint){
return $query->whereHas($relation, $constraint)
->with([$relation => $constraint]);
}Now, our code is much cleaner by calling it this way:
Author::withWhereHas('books', fn($query) =>
$query->where('title', 'like', 'PHP%')
)->get();select * from authors WHERE EXISTS(SELECT * FROM authors WHERE authors.id = books.author_id and books.title like 'PHP%');
select * from books where `books`.`author_id` in (1, 5, 11, 22, 25, 27, 35, 39, 46, 62, ….) and books.title like 'PHP%');Since this query builder may be needed in many Models we will create a macro for it so all Models will have that scope, all you need is to add this snippet under *AppServiceProvider::boot() *
use Illuminate\Database\Eloquent\Builder;
Builder::macro('withWhereHas', fn($relation, $constraint) =>
$this->whereHas($relation, $constraint)->with([$relation => $constraint]);
);Well, you reach the end of this topic, I hope all is clear and if you have any similar thoughts or ideas, I would be happy if you share them with us.
Thank you
Thank you
59