Eloquent get data using last relation model status value

I have two models, user, and books the last one has a status column that can obtain different values active, inactive, deleted, so the user can have multiple books and the book belongs to the user.

so the question is how could I get only users that have the last book with status deleted.

I did this simple Query but seems not working, because it gets all users with status deleted but not taking the consideration the last one that must be with status deleted (should I use last() or something?)

return $user->whereHas('books', function (Builder $query) {
            $query->where('status', '=', 'deleted');
});

User books hasMany method

public function books()
{
    return $this->hasMany(
            Book::class,
            Book::USER_ID_COLUMN_NAME,
            self::PRIMARY_KEY_COLUMN_NAME
    )->orderBy(Book::PRIMARY_KEY_COLUMN_NAME, 'desc');
}

Book User belongTo method

public function user() : BelongsTo
{
    return $this->belongsTo(User::class, self::USER_ID_COLUMN_NAME, User::PRIMARY_KEY_COLUMN_NAME);
}

via Chebli Mohamed