DEV Community

loading...
Cover image for Laravel WhereHas() and With()

Laravel WhereHas() and With()

Othmane Nemli
Updated on ・4 min read

Quick recall:

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);
 }
}
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode
Query Results:
select * from books
select * from authors where id in (1, 2, 3, 4, 5, …)
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode
Query Result:
select * from authors WHERE EXISTS(SELECT * FROM authors WHERE authors.id = books.author_id and books.title like 'PHP%');
Enter fullscreen mode Exit fullscreen mode

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,

foreach ($authors as $author) {
 echo $author->book->title;
}
Enter fullscreen mode Exit fullscreen mode

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();
 
Enter fullscreen mode Exit fullscreen mode
Query Result:
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, ….)
Enter fullscreen mode Exit fullscreen mode

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.

[
 App\Author : {
 id: 1
 name: "author 1",
 …,
 books: [
 App\Books: {
 ….
 title: 'PHP'
 },
 App\Books: {
 ….
 title: 'Java'
 },
 App\Books: {
 ….
 title: 'How to use'
 },
 …
 ]
 }
 …
]
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode
Query Results:
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%');
Enter fullscreen mode Exit fullscreen mode

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:

[
 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'
 },
 …
 ]
 }
 …
]
Enter fullscreen mode Exit fullscreen mode

 - - - - - - 
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]);
}
Enter fullscreen mode Exit fullscreen mode

Now, our code is much cleaner by calling it this way:

Author::withWhereHas('books', fn($query) =>
 $query->where('title', 'like', 'PHP%')
)->get();
Enter fullscreen mode Exit fullscreen mode
Query Results:
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%');
Enter fullscreen mode Exit fullscreen mode

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]);
);
Enter fullscreen mode Exit fullscreen mode

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

Discussion (3)

Collapse
maprangsoft profile image
Maprangsoft

i use laravel 8 use \Illuminate\Database\Eloquent\Builder\Eloquent; not found

Collapse
psylogico profile image
Othmane Nemli Author

Thank you, I fixed the class name, you need to use Illuminate\Database\Eloquent\Builder instead

Collapse
maprangsoft profile image
Maprangsoft

thank you very much.