onlyTrashed() is including rows where value = '0000-00-00' (MySQL/MariaDB dummy date)

[georgewells-focus]

Laravel Version

11.37.0

PHP Version

8.3.19

Database Driver & Version

MariaDB 10.6.21 for Ubuntu 24.04 LTS on x86

Description

MySQL/MariaDB permits the use to store a "zero" value of '0000-00-00' as a dummy date (https://dev.mysql.com/doc/refman/8.4/en/using-date.html), which can be treated as NULL. This is very useful for adding soft deletes to established tables that make use of unique indexes, as the deleted_at column can be included as part of the unique index. That way, only one unique non-deleted row can exist. However, the column default can't be NULL because this breaks the unique logic, so a value must be used.

By using a default value of '0000-00-00', this can be included in the unique index and everything works as expected. However, onlyTrashed() doesn't take this into consideration, therefore it believes every row is deleted.

If I modify Illuminate/Database/Eloquent/SoftDeletingScope.php, function addOnlyTrashed(), with the following logic, it works:

`
protected function addOnlyTrashed(Builder $builder)
{
$builder->macro('onlyTrashed', function (Builder $builder) {
$model = $builder->getModel();

        $qualifiedDeletedAtColumn = $model->getQualifiedDeletedAtColumn();

        $builder->withoutGlobalScope($this)->where(function ($query) use ($qualifiedDeletedAtColumn) {
            $query->whereNotNull($qualifiedDeletedAtColumn);
            $query->where($qualifiedDeletedAtColumn, '!=', '0000-00-00 00:00:00');
        });

        return $builder;
    });
}

`

Steps To Reproduce

Create a database column with the following migration code:

$table->softDeletesDatetime()->nullable(false)->default('0000-00-00 00:00:00')->after('updated_at')->index();

Ensure use SoftDeletes; is added to model.

Run tinker, and get a count of all the rows in the table with onlyTrashed()->count(). This returns the same number as if you just did 'count()', meaning onlyTrashed() is thinking every row is deleted.

[macropay-solutions]

Side note, we used nullable columns on unique combination constraints in mysql and it worked.

[darynvanv]

You should be able to override the scoped logic for onlyTrashed by overriding the SoftDeleting scope.

Something along these lines:

class CustomSoftDeletingScope extends SoftDeletingScope implements Scope{
    protected function addOnlyTrashed(Builder $builder){
        $builder->macro('onlyTrashed', function (Builder $builder) {
            $model = $builder->getModel();
            $builder->withoutGlobalScope($this)->where($model->getQualifiedDeletedAtColumn(), '!=', '0000-00-00 00:00:00');
            return $builder;
        });
    }
    protected function addWithoutTrashed(Builder $builder){
        $builder->macro('withoutTrashed', function (Builder $builder) {
            $model = $builder->getModel();
            $builder->withoutGlobalScope($this)->where($model->getQualifiedDeletedAtColumn(), '=', '0000-00-00 00:00:00');
            return $builder;
        });
    }
}

And then override the SoftDeletes trait:

trait CustomSoftDeletesTrait
{
    use SoftDeletes;

    public static function bootSoftDeletes()
    {
        static::addGlobalScope(new CustomSoftDeletingScope);
    }
}

Set your model to use the new trait, and your onlyTrashed should return the correct results.

Tested on a simple users model with 10 rows.

9 rows had deleted_at set to '0000-00-00 00:00:00'
1 row had deleted_at set to '2012-01-01 05:00:00'

Within tinker, the following results:

> User::count()                                                                                                                                                                       
= 9

> User::onlyTrashed()->count()                                                                                                                                                        
= 1

> User::withTrashed()->count()                                                                                                                                                        
= 10

[macropay-solutions]

addOnlyTrashed could also be added in the same manner.