如何在 Laravel 中使用 PostgreSQL 的 daterange 字段类型？

Laravel
在本文中，我们将讨论 PostgreSQL 的 daterange类型以及如何在Laravel中轻松使用它。 MySQL 当前不支持范围类型

我发现在许多项目中，我都不得不好好想想，如何构建一个符合日期范围标准的数据库查询搜索记录。让我解释一下我的意思。

假设您有一个由以下迁移表示的 Contract 模型：

Schema::create('contracts', function (Blueprint $table) {
    $table->id();
    $table->date('valid_from');
    $table->date('valid_to');
});

通常，您只想列出有效的合同。您最终会像这样创建查询：

Contract::query()
    ->where('valid_from', '<=', Carbon::now()->toDateString())
    ->where('valid_to', '>=', Carbon::now()->toDateString())
    ->get();

这是一个简单的例子。但是有时候事情要复杂得多，因为：

如果 valid_to 可为空，意味着合同只有开始日期但可以无限期有效怎么办
或如果排除了范围的一个（或两个）范围（例如，您想使用 <而不是 <=）怎么办？
如果使用 timestamp 类型，该怎样处理？
还是说您有其他类型的范围（例如整数范围）？

介绍范围类型

PostgreSQL 提供了一个非常干净的标识表示这种范围类型的方法，并且附带了一些简便的操作符.

文档中关于范围类型的描述非常清楚:

范围类型是表示某种元素类型的值范围的数据类型。

….（它们）之所以有用，是因为它们在单个范围值中表示许多元素值，并且可以清楚地表示类似重叠范围之类的概念。将时间和日期范围是最明显的例子，价格范围，仪器的测量范围等等也可能有用。

范围类型解决了上面提到的所有问题：

范围可以有两个界限-上限和下限
这两个边界都是可选的（因此它支持开区间）
开区间由 [ 表示，闭区间由 ( 表示
标准化的测试开区间或闭区间的方法

让我们回到例子中，首先让我们为 daterange 类型定义一个新的有效范围。不幸的是，Laravel不支持范围类型，因此，我们在迁移文件中使用原始SQL语句创建列：

public function up()
{
    Schema::create('contracts', function (Blueprint $table) {
        $table->id();
    });    DB::statement("
        ALTER TABLE contracts
        ADD COLUMN valid_range daterange NOT NULL;
    ");
}

现在我们可以使用PostgreSQL提供的功能强大的运算符，例如测试合同在特定日期（2020年1月1日）的有效性（使用 contains 运算符）。

SELECT * FROM contracts
WHERE valid_range @> '2020-01-01'::date;

我们还可以检索所有2020年1月有效的合同（使用区间操作符）：

SELECT * FROM contracts
WHERE valid_range && '[2020-01-01, 2020-02-01)';

注意我们如何使用互斥边界。另请注意，这个语句会返回开始于2020年1月之前，但最后一次在2020年1月期间的合同。它们甚至可能具有更长的有效期限（目前有效）。

如果您只想查找在2020年1月开始和结束的合同，则可以这样使用操作符：

SELECT * FROM contracts
WHERE valid_range <@ '[2020-01-01, 2020-02-01)';

Indexing

假设您有数百万份合同，并且需要快速查找在特定时间内有效的合同。您可能需要使用 GIST索引:

DB::statement("
    CREATE INDEX ON contracts USING GIST (valid_range);
");

这可能会显著提高基于 valid_range 的查询数据性能。

Uniqueness

有时，您想确保在数据库级别上同一时间不会存在两个合同（即任何合同都不能相互重叠）。

没有表使用GIST 索引更好的方法了:

DB::statement("
    ALTER TABLE contracts
    ADD EXCLUDE USING GIST (valid_range WITH &&);
");

如果每个客户只需要一份有效合同（无重叠）怎么办？使用 btree_gist 扩展名，您可以非常轻松地做到这一点。但首先，您需要确保使用此命令开启扩展（注意：这个命令会为整个数据库启用扩展名，因此我不建议将此命令包括在迁移文件中）。

CREATE EXTENSION IF NOT EXISTS btree_gist;

现在，您可以像这样设置唯一性约束：

DB::statement("
    ALTER TABLE contracts
    ADD EXCLUDE USING GIST (client_id WITH =, valid_range WITH &&);
");

Laravel 支持

直到现在，我们都在讨论原生SQL语句，但是我们该如何在Laravel中更流畅地使用此功能呢？

查询

让我们从查询数据库中的数据开始。最自然的方法是使用 whereRaw 方法：

Contract::query()
    ->whereRaw('valid_range @> ?::date', [
        Carbon::now()->toDateString()
    ])
    ->get();

这是一种非常简单的方法，并且没有任何问题。它可能完全符合您的需求。

但是，如果您有一个更大的项目，并且经常使用 daterange 运算符，则扩展 Contract 模型的 QueryBuilder 以提供更流畅的API可能会很方便，因此您可以像这样构建查询：

Contract::query()
    ->whereRangeContains('valid_range',
        Carbon::now()->toDateString()
    )
    ->get();

很整洁吧？那你该怎么做。首先，您需要重写 Contract 模型中的 new EloquentBuilder（） 方法：

public function newEloquentBuilder($query): ExtendedQueryBuilder
{
    return new ExtendedQueryBuilder($query);
}

然后定义这个新的构建器类：

<?php
namespace App;

use Illuminate\Database\Eloquent\Builder;

class ExtendedQueryBuilder extends Builder
{
    public function whereRangeContains($column, $value)
    {
        $this->query->whereRaw($column . ' @> ?::date', [$value]);
        return $this;
    }
}

就是这样。当然，您可以为正在使用的所有其他 daterange （或其他 range）运算符提供流利的API。

访问范围属性

到目前为止，我们主要讨论的是合同集合。让我们专注于 Contract 模型本身并实际访问 valid_range 属性。

如果直接访问 valid_range 属性:

"[2020-02-18,2020-03-16)"

返回一个字符串，使用时很不方便(请参阅 PostgreSQL 默认情况下如何自动将 canonicaliz 的上限自动设置为闭区间)。

一种简单的解决方法是利用generated columns功能。而不是直接使用 daterange，而是创建生成的列。其值将始终从源列中计算：

public function up()
{
    Schema::create('contracts', function (Blueprint $table) {
        $table->id();
        $table->date('valid_from');
        $table->date('valid_to');
    });    DB::statement("
        ALTER TABLE contracts
        ADD COLUMN valid_range daterange NOT NULL
        GENERATED ALWAYS AS (
            daterange(valid_from, valid_to, '[]')
        ) STORED;
    ");    DB::statement("
        CREATE INDEX ON contracts USING GIST (valid_range);
    ");
}

这样，我们将使用原生列 valid_from 和 valid_to 来访问和操作数据，但是我们利用生成的 valid_range 列来简化查询（更流畅的API），从而确保数据完整性检查（唯一性）和优化（建立索引）（请注意，即使生成的列也可能具有索引）。

另一个更健壮的想法是使用 Laravel 7 提供的Custom Casts功能。我们可以创建一个新的 DateRange 类，并告诉我们的 Contract 模型自动将有效范围字段转换为该 DateRange 类。

看看下面的案例:

Contract.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Contract extends Model
{
    public $timestamps = false;

    protected $casts = [
        'valid_range' => \App\Casts\DateRangeCast::class,
    ];

    public function newEloquentBuilder($query): ExtendedQueryBuilder
    {
        return new ExtendedQueryBuilder($query);
    }
}

DateRange.php

<?php

namespace App;

use Carbon\CarbonImmutable;
use Carbon\CarbonInterface;

class DateRange
{
    private ?CarbonInterface $from;

    private ?CarbonInterface $to;

    public function __construct($from = null, $to = null, $fromBound = "[", $toBound = "]")
    {
        $this->from = is_string($from) ? $this->parseFrom($from) : $from;
        $this->to = is_string($to) ? $this->parseTo($to) : $to;

        // when exclusive bound is set,
        // let's canonicalize it to inclusive bounds
        if ($fromBound === '(') {
            $this->from = $this->from->addDay();
        }

        if ($toBound === ')') {
            $this->to = $this->to->subDay();
        }
    }

    private function parseFrom(string $from) : CarbonInterface
    {
        return CarbonImmutable::parse($from);
    }

    private function parseTo(string $to) : CarbonInterface
    {
        return CarbonImmutable::parse($to);
    }

    public function from(): ?CarbonInterface
    {
        return $this->from;
    }

    public function to(): ?CarbonInterface
    {
        return $this->to;
    }
}

DateRangeCast.php

<?php

namespace App\Casts;

use Illuminate\Contracts\Database\Eloquent\CastsAttributes;

class DateRangeCast implements CastsAttributes
{
    public function get($model, $key, $value, $attributes)
    {
        preg_match('/([\[\(]{1})(.*)\,(.*)([\]\)]{1})/', $attributes['valid_range'], $matches);

        return new \App\DateRange($matches[2], $matches[3], $matches[1], $matches[4]);
    }

    public function set($model, $key, $value, $attributes)
    {
        return [
            'valid_range' => $this->serializeRange($value)
        ];
    }

    private function serializeRange($range)
    {
        return "[" .
            optional(optional($range)->from())->toDateString() .
            "," .
            optional(optional($range)->to())->toDateString() .
            "]";
    }
}

现在我们试着访问 valid_range 属性:

>>> App\Contract::find(1)->valid_range;=> App\DateRange {#3018}

我们拿到 DateRange 对象, 因此我们可以更轻松地对范围类型进行操作：

>>> App\Contract::find(1)->valid_range->from();=> Carbon\CarbonImmutable @1581984000 {#3030
     date: 2020-02-18 00:00:00.0 UTC (+00:00),
     timezone: "UTC",
   }

结论

在本文中，我试图解释如何在Laravel中使用 daterange （或任何其他 range 类型）PostgreSQL 列类型。使用此类型而不是仅使用两个简单值作为边界的主要优点是：

流畅的查询-您无需创建复杂的条件，例如针对多种情况进行测试（例如，半开放范围），而只需使用标准的数学运算符（范围重叠，范围包含元素，范围包含范围等），
性能优化
更容易的数据完整性检查（约束）

本文中的所有译文仅用于学习和交流目的，转载请务必注明文章译者、出处、和本文链接
我们的翻译工作遵照 CC 协议，如果我们的工作有侵犯到您的权益，请及时联系我们。

原文地址：https://medium.com/@palypster/ranges-in-...

译文地址：https://learnku.com/laravel/t/42930

本文为协同翻译文章，如您发现瑕疵请点击「改进」按钮提交优化建议改进本文

0 人点赞

你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程，JWT 概念及使用和 API 开发相关的进阶知识。

以构建论坛项目 LaraBBS 为线索，展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。

讨论数量: 0

(=￣ω￣=)··· 暂无内容！

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！

帮助

如何在 Laravel 中使用 PostgreSQL 的 daterange 字段类型？

介绍范围类型

Indexing

Uniqueness

Laravel 支持

查询

访问范围属性

结论

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

如何在 Laravel 中使用 PostgreSQL 的 daterange 字段类型？

介绍范围类型

Indexing

Uniqueness

Laravel 支持

查询

访问范围属性

结论

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录