Laravel Eloquent whereDoesntHave() documentation confuses me

Question

Referring to https://laravel.com/docs/7.x/eloquent-relationships#querying-relationship-absence the query

use Illuminate\Database\Eloquent\Builder;

$posts = App\Post::whereDoesntHave('comments.author', function (Builder $query) {
    $query->where('banned', 0);
})->get();

is described as "retrieve all posts with comments from authors that are not banned". This seems incorrect to me, as that seems to imply that if a post has one comment from a banned author and another comment from a non banned author, that post would be in the resultant collection - after all, it has comments from authors that are not banned.

In actuality, such a post would not be included in the collection due to the existence of a comment from a banned author, regardless of the other comments that post may have.

It seems more accurate to say "retrieve all posts that have comments, none of which are from banned authors".

Am I mistaking something?

Answer 1

Actually, both are incorrect. it would be correct to say:

"retrieve all posts that have no comments, or where ALL comments are from banned authors"

I've created this pull request for the Laravel docs to correct the wording.

The sql generated from such a call will look like this:

SELECT 
    *
FROM
    `posts`
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            `comments`
        WHERE
            `posts`.`id` = `comments`.`postId`
                AND EXISTS( SELECT 
                    *
                FROM
                    `authors`
                WHERE
                    `comments`.`authorId` = `authors`.`id`
                        AND `banned` = 0))

Breaking that down, the inner query says "find me all the comments associated to each post, where that comment was written by an author that is not banned.

            SELECT 
                *
            FROM
                `comments`
            WHERE
                `posts`.`id` = `comments`.`postId`
                    AND EXISTS( SELECT 
                        *
                    FROM
                        `authors`
                    WHERE
                        `comments`.`authorId` = `authors`.`id`
                            AND `banned` = 0)

Then, the top level query says, "now, give me all posts where the above subquery returned no rows"

ie, all the authors were banned, or there were no comments at all.

  SELECT 
        *
    FROM
        `posts`
    WHERE
        NOT EXISTS(
          {.. sub query above here }
       )

---

If you want to test this yourself,

Models:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Post extends Model
{
    public function comments(): HasMany
    {
        return $this->hasMany(Comment::class, 'postId');
    }
}


<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasOne;

class Comment extends Model
{
    public function author(): HasOne
    {
        return $this->hasOne(Author::class, 'id', 'authorId');
    }

}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Author extends Model
{

}

Create table Sql:

CREATE TABLE `posts` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
);
CREATE TABLE `comments` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `postId` BIGINT(20) NOT NULL,
    `authorId` BIGINT(20) NOT NULL,
    `content` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `authors` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
);

Content sql:

# crate a post
INSERT INTO `posts` (`id`) VALUES ('1');




# add a banned and not banned author 
INSERT INTO `authors` (`name`, `banned`) VALUES ('Jack', '0');
INSERT INTO `authors` (`name`, `banned`) VALUES ('Jill', '1');


# add a comment from a banned and not banned author 
INSERT INTO `comments` (`postId`, `authorId`, `content`) VALUES ('1', '1', 'a');
INSERT INTO `comments` (`postId`, `authorId`, `content`) VALUES ('1', '2', 'b');

Now run the code:

$post = \App\Models\Post::whereDoesntHave('comments.author', function ( $query) {
     $query->where('banned', 0);
})->get();

You'll get back 0 results.

Now run this to make both authors not banned:

UPDATE `authors` SET `banned`='0';

You'll still get back 0 results.

Now run this to make both authors banned:

UPDATE `authors` SET `banned`='1';

You will now get back 1 result.

Now run this to make both authors not banned again but delete their comments:

UPDATE `authors` SET `banned`='0';
DELETE FROM `comments`;

You will now get back 1 result.

This proves that the actual behavior is to "retrieve all posts that have no comments, or where ALL comments are from banned authors"