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);
 }
}

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;
}

Query Results:

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();

Query Result:

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,

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();
 

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, ….)

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'
 },
 …
 ]
 }
 …
]

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();

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%');

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'
 },
 …
 ]
 }
 …
]

 - - - - - - 
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();

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%');

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

Comments

[HoangMoli]

The macro should be

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

if not using lambda

[usamaxyz]

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

[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.

[Othmane Nemli]

Great, would you please share your Macro?

[Chidiebere Chukwudi]

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

Unlike in your case, where withWhereHas is a scope.

[quyle92]

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

[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

[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 ..

[Maprangsoft]

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

[Othmane Nemli]

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

[Maprangsoft]

thank you very much.

[StalinWesley]

thanks