Ranges in Laravel 7 using PostgreSQL

In many projects I repeatedly find myself in a position, when I have to think twice about how to build the correct database query searching rows that meet some date range criteria. Let me explain what I mean.

Imagine you have a Contract model represented by the following migration:

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

Typically, what you want to do is to list only active contracts. You end up creating query like this:

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

This is a simple example. But sometimes things are more complicated then this, because:

  • what if valid_to is nullable meaning the contract has only start date, but can be valid indefinitely
  • or what if one (or both) bounds of the range are excluded (i.e. you would use < instead of <=)
  • how the situation changes if you use timestamp type?
  • or even if you have a different type of ranges like range of an integer?

Introducing range types

PostgreSQL provides a very clean way of representing these range types and it comes with several handy operators.

The documentation is very clear about range types:

Range types are data types representing a range of values of some element type.

….(they) are useful because they represent many element values in a single range value, and because concepts such as overlapping ranges can be expressed clearly. The use of time and date ranges for scheduling purposes is the clearest example; but price ranges, measurement ranges from an instrument, and so forth can also be useful.

Range types solves all the concerns we have open above:

  • range can have two bounds – upper and lower
  • both bounds are optional (so it supports “open” ranges)
  • bounds can be inclusive (represented by [ ) or exclusive (represented by ( )
  • there is a standardised way of testing for overlaps or containments

Let’s get back to our example. First let’s define a new column of the daterange type. Unfortunately, Laravel does not support range types (we’ll come back to that later). For now, let’s create the column using raw SQL query within the migration file:

public function up()
{
    Schema::create('contracts', function (Blueprint $table) {
        $table->id();
    });

    DB::statement("
        ALTER TABLE contracts
        ADD COLUMN valid_range daterange NOT NULL;
    ");
}

Now we can use powerful operators PostgreSQL provides like for example testing contracts for validity for the specific date – January 1, 2020 (using contains operator). This is how our query would look like in raw SQL:

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

We can also retrieve all the contracts that were active throughout January 2020 (using overlaps operator):

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

Note how we are using exclusive upper bound. Also note that this returns also contracts that started before, but last during January 2020. They even may have upper bound open (and are valid in present).

If you want to find only contracts that both started and ended within January 2020, then you can use the is contained by operator:

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

Indexing

Imagine you have millions of contracts and you need to quickly find those that were valid within specific time. You may need to use GIST index:

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

This may significantly improve the performance of querying your data based on valid_range column.

Uniqueness

Sometimes you want to ensure, on a DB-level, you have no two contracts for the same time (so no contract can overlap with each other).

Couldn’t be done easier using GIST index:

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

But what if you need to have only one active contract (with no overlaps) per each client? With btree_gist extension you can do it very easily. But first, you need to ensure to use the extension using this command (Note: the extension is then enabled for the whole database, so I don’t recommend to include this line into the contracts migration file).

CREATE EXTENSION IF NOT EXISTS btree_gist;

Now you can extend our uniqueness constraint like this:

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

Laravel support

Until now, we’ve been talking a lot about the SQL. But how can we use this functionality in Laravel in a more fluent way?

Querying

Let’s start with querying the data from the database. The most natural approach would be to use whereRaw method:

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

This is a very simple approach, but it’s perfectly fine and there’s nothing wrong about it. It may perfectly fit your needs.

But if you have a bigger project and you use daterange operators very often, it might be convenient to extend your QueryBuilder of the Contract model to provide even more fluent API, so you can build query like this:

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

Pretty neat, huh? So how can you do this. First, you need to override the newEloquentBuilder() method in the Contract model:

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

And then define this new builder class:

<?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;
    }
}

And that’s it. You can of course provide a fluent API for all other daterange (or range in general) operators you are using.

Accessing range attribute

Until now we’ve been talking mainly about working with the collections of contracts. Let’s focus on the Contract model itself and actually access the valid_range attribute.

If we access the valid_range directly, we get:

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

as s string. Working with this is pretty inconvenient (see how PostgreSQL, by default, automatically canonicalize upper bound to always be exclusive).

One simple workaround is to make use of the feature called generated columns. Instead of using daterange directly, we create generated column instead. Its value will always be calculated from the source columns:

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);
    ");
}

This way we will be using native columns valid_from and valid_to for the accessing and manipulating the data, but we will make use of the generated valid_range column for easier querying (more fluent API), ensuring data integrity checks (uniqueness) and optimisation (indexing) (Note that even generated column may have an index).

The other, more robust idea is to use feature Laravel 7 provides called Custom Casts.

We can create new DateRange class and we tell our Contract model to automatically cast the valid_range column into this DateRange class.

Look at the following example:

<?php

namespace App\Casts;

use Illuminate\Contracts\Database\Eloquent\CastsAttributes;

class DateRange 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]);
    }

    /**
     * @param \Illuminate\Database\Eloquent\Model $model
     * @param string $key
     * @param \App\DateRange $value
     * @param array $attributes
     * @return array
     */
    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() . "]";
    }
}
<?php

namespace App;

use Carbon\CarbonImmutable;
use Carbon\CarbonInterface;

class DateRange
{
    private ?CarbonInterface $from;

    private ?CarbonInterface $to;

    /**
     * DateRange constructor.
     * @param CarbonInterface|string|null $from
     * @param CarbonInterface|string|null $to
     * @param string $fromBound
     * @param string $toBound
     */
    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;
    }
}
<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

/**
 * @property \App\DateRange valid_range
 */
class Contract extends Model
{
    public $timestamps = false;

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

}

When we try to access the valid_range attribute now:

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

we get DateRange object, so we can interact with it much easier:

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

Conclusion

In this article I try to explain how can you use daterange (or any other range type) PostgreSQL column type in Laravel. The main advantage of this approach (instead of using just two values as a boundaries) is:

  1. querying – you don’t need to create complicated conditions like testing for multiple scenarios (i.e. for half-open ranges), you just use standard math operators (ranges overlap, range contains element, range contains range, ….),
  2. performance optimisation
  3. easier data integrity checks (constraints)

Aktuálne chceme posilniť tím a hľadáme šikovného project managera. Si to ty? Napíš nám. Ak poznáš niekoho vhodného, pošli mu náš inzerát.

Áno, som to ja
Subscribe for newsletter

Get the freshest news and keep up with social media world.

Stay informed through the email

Your email is safe with us, read our terms.

Stay informed through the email

Stay informed through the email

Your email is safe with us, read our terms.