DEV Community

Cover image for Laravel WhereHas() and With()
Othmane Nemli
Othmane Nemli

Posted on • Updated on

Laravel WhereHas() and With()

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

Top comments (12)

Collapse
 
hoangmoli profile image
HoangMoli • Edited

The macro should be

Builder::macro(
    'withWhereHas',
    function ($relation, $constraint) {
        return $this
            ->whereHas($relation, $constraint)
            ->with($relation, $constraint);
    }
);
Enter fullscreen mode Exit fullscreen mode

if not using lambda

Collapse
 
usamaxyz profile image
usamaxyz

Thanks. I solved the same problem with inner join which supposed to be more performance efficient.

Collapse
 
brunodiaz profile image
bdiazc90

Hello! 👋
JOIN could has better performance than WHERE EXISTS (with - eager loading), BUT:

The reason to use WITH, is for accessing models as related objects (with all their fields) instead of access a couple of fields specified in JOIN.

Collapse
 
othmane_nemli profile image
Othmane Nemli

Great, would you please share your Macro?

Collapse
 
jovialcore profile image
Chidiebere Chukwudi • Edited

Thanks for sharing. Worthy to note that laravel now has withWhereHas eloquent method by default

Unlike in your case, where withWhereHas is a scope.

Collapse
 
quyle92 profile image
quyle92

thanks for great article.
Now I know that with the withWhereHas() I can load the relationship with specific condition.

Collapse
 
zensabbah profile image
zensabbah

Hi Othmane Nemli, thanks for your article.
I have one doubt though, I didn't understand why you use the whereHas query if you just specified the condition in the first query.
Don't you get the desired result only with the "with" query+condition?

Thanks in advance

Collapse
 
othmane_nemli profile image
Othmane Nemli

Thanks for reading the article.

Well, using same condition in with() and whereHas() is to prevent N+1 and get only data that you asked for in the whereHas(), so as I demonstrate the issue that we may face, the solution was to combine with() and whereHas() with same condition ..

Collapse
 
maprangsoft profile image
Maprangsoft

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

Collapse
 
othmane_nemli profile image
Othmane Nemli

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.

Collapse
 
stalinwesley profile image
StalinWesley

thanks