0x00 背景

Laravel 自带软删除使用特殊值 NULL 表示此记录未被删除。由此引出一个问题。

设想,某表内需要建立索引,索引字段为 name, deleted_at

假设此索引条件必定唯一,可建立 Uniuqe 索引。然而 deleted_at 允许 NULL 存在,导致索引无效,在查询时降级为 Index

因此,在绝大多数场景下,我们查询未被删除(deleted_at IS NULL)的数据,唯一索引都没有卵用。

mysql> EXPLAIN SELECT * FROM `my_tbl`;
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | my_tbl | NULL | index | NULL | name_deleted_at | 38 | NULL | 2 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

于是,一个想法在脑海里浮现 —— 重写软删除字段为整型时间戳。

0x01 准备

Check list:

  • Migrations
  • SoftDeletingScope
  • SoftDeletes Trait
  • ???

0x02 Migrations

简单。

新建一个 Service Provider,或者直接在 AppServiceProviderboot 方法内添加:

Blueprint::macro('mySoftDeletes', function () {
$this->unsignedBigInteger('deleted_at')->default(0);
});

在 Migration 中使用 Schema Facade 创建表时,回调函数的参数会被传入一个 Blueprint 实例;而 Blueprint 使用了 Macroable Trait,所以我们可以任意扩展宏方法;详细可阅读 Laravel 5.6 给 Blueprint 增加自定义方法

0x03 SoftDeletingScope

新建一个 Class,实现 Scope 接口即可。

namespace App\Scopes;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;

class MySoftDeletingScope implements Scope
{
/**
* All of the extensions to be added to the builder.
*
* @var array
*/
protected $extensions = ['Restore', 'WithTrashed', 'WithoutTrashed', 'OnlyTrashed'];

/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->where($model->getQualifiedDeletedAtColumn(), '=', 0);
}

/**
* Extend the query builder with the needed functions.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @return void
*/
public function extend(Builder $builder)
{
foreach ($this->extensions as $extension) {
$this->{"add{$extension}"}($builder);
}

$builder->onDelete(function (Builder $builder) {
$column = $this->getDeletedAtColumn($builder);

return $builder->update([
$column => $builder->getModel()->freshTimestamp()->timestamp,
]);
});
}

/**
* Get the "deleted at" column for the builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @return string
*/
protected function getDeletedAtColumn(Builder $builder)
{
if (count((array) $builder->getQuery()->joins) > 0) {
return $builder->getModel()->getQualifiedDeletedAtColumn();
}

return $builder->getModel()->getDeletedAtColumn();
}

/**
* Add the restore extension to the builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @return void
*/
protected function addRestore(Builder $builder)
{
$builder->macro('restore', function (Builder $builder) {
$builder->withTrashed();

return $builder->update([$builder->getModel()->getDeletedAtColumn() => 0]);
});
}

/**
* Add the with-trashed extension to the builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @return void
*/
protected function addWithTrashed(Builder $builder)
{
$builder->macro('withTrashed', function (Builder $builder, $withTrashed = true) {
if (! $withTrashed) {
return $builder->withoutTrashed();
}

return $builder->withoutGlobalScope($this);
});
}

/**
* Add the without-trashed extension to the builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @return void
*/
protected function addWithoutTrashed(Builder $builder)
{
$builder->macro('withoutTrashed', function (Builder $builder) {
$model = $builder->getModel();

$builder->withoutGlobalScope($this)->where(
$model->getQualifiedDeletedAtColumn(),
'=',
0
);

return $builder;
});
}

/**
* Add the only-trashed extension to the builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @return void
*/
protected function addOnlyTrashed(Builder $builder)
{
$builder->macro('onlyTrashed', function (Builder $builder) {
$model = $builder->getModel();

$builder->withoutGlobalScope($this)->where(
$model->getQualifiedDeletedAtColumn(),
'<>',
0
);

return $builder;
});
}
}

SoftDeletingScope 的作用,是在模型的查询内加入「未删除」或「已删除」等特定条件,详情可参考 官方文档

Writing your own global scopes can provide a convenient, easy way to make sure every query for a given model receives certain constraints. - Laravel 5.7 Documentation

另外,这里我并没有继承 Eloquent 的 SoftDeletingScope,原因有三:

  • 原代码改动不方便,几乎所有方法都需要覆盖重写。
  • 项目后期升级框架版本,不希望框架代码的改动影响此处。
  • 根据官方文档对于 Global Scope 的解释,软删除即是利用它实现。所以实际上 Scope 是一项明确开放的特性;你可以想象为我们正在利用 Scope 自行实现软删除。

0x04 SoftDeletes Trait

新建一个 Trait,代码比较长,如下。

namespace App\Traits;

use App\Scopes\MySoftDeletingScope;
use Illuminate\Database\Eloquent\SoftDeletes;

trait MySoftDeletes
{
use SoftDeletes;

/**
* Boot the soft deleting trait for a model.
*
* @return void
*/
public static function bootSoftDeletes()
{
static::addGlobalScope(new MySoftDeletingScope());
}

/**
* Perform the actual delete query on this model instance.
*
* @return void
*/
protected function runSoftDelete()
{
$query = $this->newModelQuery()->where($this->getKeyName(), $this->getKey());

$time = $this->freshTimestamp();

$columns = [$this->getDeletedAtColumn() => $time->timestamp];

$this->{$this->getDeletedAtColumn()} = $time;

if ($query->update($columns)) {
$this->syncOriginal();
}
}

/**
* Restore a soft-deleted model instance.
*
* @return bool|null
*/
public function restore()
{
// If the restoring event does not return false, we will proceed with this
// restore operation. Otherwise, we bail out so the developer will stop
// the restore totally. We will clear the deleted timestamp and save.
if ($this->fireModelEvent('restoring') === false) {
return false;
}

$this->{$this->getDeletedAtColumn()} = 0;

// Once we have saved the model, we will fire the "restored" event so this
// developer will do anything they need to after a restore operation is
// totally finished. Then we will return the result of the save call.
$this->exists = true;

$result = $this->save();

$this->fireModelEvent('restored', false);

return $result;
}

/**
* Determine if the model instance has been soft-deleted.
*
* @return bool
*/
public function trashed()
{
return $this->{$this->getDeletedAtColumn()} != 0;
}
}

SoftDeletes 的作用是赋予模型查询时,使用更加语义化命名的方法,查询「未删除」、「已删除」等特定条件的模型;同时,在 bootSoftDeletes 方法内,挂载上一步定义的 Global Scope

注意:Trait 不可以继承,但可以嵌套、覆盖。所以此处我 use SoftDeletes,并重写其中部分方法实现。

0x05 Surprise…

Check list 的最后一条,Laravel 给我们留了个惊喜。

SoftDelete 特性并不是完全解耦的。除了刚刚几处非常明显需要修改的地方之外,还有一处隐藏很深的暗坑。

今天在使用 HasManyThrough 时,发现一个奇葩问题,查询的 SQL 经过调试输出,发现依然有 IS NULL 的查询条件。

经过一番排查,发现在 Illuminate\Database\Eloquent\Relations\HasManyThrough 内有一方法 throughParentSoftDeletes 🤔。

又有两方法 performJoingetRelationExistenceQueryForSelfRelation 利用如上方法,判断模型是否 use SoftDeletes;若已 use,则 whereNull

好气啊!

于是,你还需要进行两步。

  1. 在模型内(或者你可以创建个公共基类,或者也可以写在单独的 Trait 中)重写 newHasManyThrough 方法,创建自定义的模型关系类。

    protected function newHasManyThrough(Builder $query, Model $farParent, Model $throughParent, $firstKey, $secondKey, $localKey, $secondLocalKey)
    {
    return new MyHasManyThrough($query, $farParent, $throughParent, $firstKey, $secondKey, $localKey, $secondLocalKey);
    }
  2. 新建如上 MyHasManyThrough 类,重写那两个坑爹的方法,替换 whereNull

    use Illuminate\Database\Eloquent\Builder;
    use Illuminate\Database\Eloquent\Relations\HasManyThrough;

    class MyHasManyThrough extends HasManyThrough
    {
    protected function performJoin(Builder $query = null)
    {
    $query = $query ?: $this->query;

    $farKey = $this->getQualifiedFarKeyName();

    $query->join($this->throughParent->getTable(), $this->getQualifiedParentKeyName(), '=', $farKey);

    if ($this->throughParentSoftDeletes()) {
    $query->where($this->throughParent->getQualifiedDeletedAtColumn(), '=', 0);
    }
    }

    public function getRelationExistenceQueryForSelfRelation(Builder $query, Builder $parentQuery, $columns = ['*'])
    {
    $query->from($query->getModel()->getTable().' as '.$hash = $this->getRelationCountHash());

    $query->join($this->throughParent->getTable(), $this->getQualifiedParentKeyName(), '=', $hash.'.'.$this->secondLocalKey);

    if ($this->throughParentSoftDeletes()) {
    $query->where($this->throughParent->getQualifiedDeletedAtColumn(), '=', 0);
    }

    $query->getModel()->setTable($hash);

    return $query->select($columns)->whereColumn(
    $parentQuery->getQuery()->from.'.'.$this->localKey, '=', $this->getQualifiedFirstKeyName()
    );
    }
    }

0xFF 感想

终于,在修改这么多类之后,我们实现了用整型时间戳代替 DATETIME + NULL 实现软删除。

若后期发现有其它的坑,填满再继续回来补充。