belamov/postgres-range

Laravel package for PostgreSQL range types support

postgres-range's Issues

Support of [tstzrange]

Currently PostgreSQL 12 supports tstzrange, which is Range of timestamp with time zone.

I think implementation should be not an hassle since it's basically tsrange with timezone. Just two new letters. Even the official documentation names it only once.

Extend range objects api for checking boundaries existence

extend api of range objects with ->hasUpperBoundary() and ->hasLowerBoundary()

it could be useful and can make client code cleaner (based on #12)

improve macros security

currently macros are registered like that:

Builder::macro(
    "where{$operatorName}",
    fn ($left, $right) => $this->whereRaw(
        sprintf('%s %s %s',
            $left instanceof Range ? $left->forSql() : $left,
            $operator,
            $right instanceof Range ? $right->forSql() : $right
        )
    )
);

https://github.com/belamov/postgres-range/blob/master/src/Macros/QueryBuilderMacros.php#L25

it is not sanitized in any way, so there's potential sql injection scenario, if you're using macros with unvalidated parameters

the better approach is to use PDO's bindings, like so:

Builder::macro(
    "where{$operatorName}",
    fn ($left, $right) => $this->whereRaw(
        "? $operator ?", [
            $left instanceof Range ? $left->forSql() : $left,
            $right instanceof Range ? $right->forSql() : $right 
        ]            
    )
);

or

Builder::macro(
    "where{$operatorName}",
    fn ($left, $right) => $this->where(
            $left instanceof Range ? $left->forSql() : $left,
            $operator
            $right instanceof Range ? $right->forSql() : $right             
    )
);

macros are tested like this:

$query = "where$macroMethod";
$items = Range::$query($columnName, $rangeObject)->get();

https://github.com/belamov/postgres-range/blob/master/tests/Feature/MacrosTest.php#L59

with current implementation there is no errors and every macro is working as expected

but with whereRaw and bindings approach we get sql generated like so:

select * from "ranges" where timestamp_range @> '[2020-04-24 06:52:19,2020-04-25 06:52:19)'::tsrange

and Ambiguous function: 7 ERROR:

operator is not unique: unknown @> unknown

and with where approach we get sql generated like so:

select * from "ranges" where "timestamp_range" @> '[2020-04-24 06:48:52,2020-04-25 06:48:52)'::tsrange

and Invalid text representation: 7 ERROR:

malformed range literal: "'[2020-04-24 06:48:52,2020-04-25 06:48:52)'::tsrange"

in both cases range is transformed as expected and if we run this generated queries with sql, there is no errors. so my guess is that somewhere in value binding to pdo statement this parameters are transformed in some way that pdo driver is not correctly casting types

would be great if somebody know what's the issue is and can help me out with this

Try to add support of range types within Blueprint class

It would be cool if we can use this code

Schema::create(
            'table',
            static function (Blueprint $table) {
                $table->id();
                $table->dateRange('date_range');
                $table->timestampRange('timestamp_range');
                // all other range types
            }
        );

Instead of SqlGenerator class

Useful links:

• https://medium.com/@daniilromazanov/how-to-extend-query-grammar-in-laravel-fb3d2d6de6d4
• https://github.com/rafis/schema-extended

Laravel 8 compatibility

Earlier this week Laravel 8 was released.

I would really appreciate it if you could make this package compatible with Laravel 8.

How to check if my number within the range of the IntegerRange object

Hi! Thanks for package.

For example I have code:

$range = new IntegerRange('1', 1_000, '[', ']');

and I want to get a true or false answer, whether my value is in this range like this:

$range->check(10); \\ true
$range->check(10000); \\ false

but I did not find such a method to do it. It will be very helpful!

Write documentation

Query builder macro error with generated SQL

Cool package! I'm just starting to use PostgreSQL date ranges and thought I would try this out.

Describe the bug
Using the query builder macros throws an operator is not unique error.

App\Reservation::whereRangeOverlaps('during', $range)->get();

Ambiguous function: 7 ERROR:  operator is not unique: unknown && unknown
LINE 1: select * from "room_reservation" where $1 && $2
                                                  ^
HINT:  Could not choose a best candidate operator. 
You might need to add explicit type casts. (SQL: select * from "room_reservation" where during && [2010-01-07,2010-01-10))'

To Reproduce
Database: PostgreSQL 11.6
Laravel: 7.6.2
Steps to reproduce the behavior:

1. Create a table with a daterange field, e.g. during
2. Insert data to daterange field, e.g. [2010-01-01,2010-01-08)
3. Add to model to $protected casts, e.g. 'during' => DateRangeCast::class
4. Create a range to use in the query:

$from = Carbon\Carbon::parse('2010-01-07');
$to = Carbon\Carbon::parse('2010-01-09');
$range = new DateRange($from, $to, '[', ']');

4. Run whereRangeOverlaps('during', $range)->get();
5. Expected the inserted row to be returned by the query.

I have not tried the other builder macros yet.

Potential solution
I think we can replace the whereRaw call with a standard where function. Laravel's grammar contains valid Postgres operators. Example,

Builder::macro(
    'whereRangeOverlaps',
    function ($left, $right) {
       //return $this->whereRaw('? && ?', [$left, $right]);
        return $this->where($left, '&&', $right);
    }
);

I will see how this works with the other macros, run some tests and try and submit a PR but I won't be able to do this right away.

Thanks!

Conflicts with package laravel-adjacency-list

Describe the bug
After installation via composer, it seems to break the package laravel-adjacency-list. Even without using the postgres-range package, I get an Illuminate\Database\QueryException:

SQLSTATE[42P01]: Undefined table: 7 ERROR: Relation »laravel_cte« doesn't exist

To Reproduce
Steps to reproduce the behavior:

1. install staudenmeir/laravel-adjacency-list via composer
2. use staudenmeir/laravel-adjacency-list on model 'X'
3. install belamov/postges-range
4. run database migration
5. define a cast for datarange column in model 'Y'
6. access a laravel route where model 'X' is involved
7. see error above

Expected behavior
Installation of postgres-range should not affect another package

Additional context
I guess it might be related to hints on postgres grammar, but unfortunately I'm not familiar with CTE and don't know how to deal with it.

Any help and advice is very appreciated. Thanks in advance.

Make timerange type name and comparing function configurable

DateRangeCast returns Carbon object with current date&time instead of null for [2020-08-31,)

First of all thanks for this package, it is very useful.

Describe the bug
PHP: 7.4.9
PostgreSQL: 11.9 and 12.4
Laravel: 7.26.1

When from or to is set to null (e.g. [2020-08-31,)), some sort of default value is returned from RangeCast instance (e.g. Carbon object with current date&time).

To Reproduce
Steps to reproduce the behavior:

1. Create database column validity via migration

$table->dateRange('validity');

2. Set casts in model

protected $casts = ['validity' => DateRangeCast::class]

3. Store value in the database

$model->update(['validity' => new DateRange(Carbon::parse('2020-08-31'), null)])

4. Fetch value from database

$model->validity->to()

Expected behavior
I would expect to get null value from step 4, but I get Carbon object with current date&time.

Additional context
Similar result is with Integer or Float RangeCast. When from or to is set to null, 0 or 0.0 is returned.

On the other hand, this works as expected.

$validity = new DateRange(Carbon::parse('2020-08-31'), null);
$validity->to(); // null

PHP 8 support

PHP 8 has been released: https://www.php.net/releases/8.0/en.php

Laravel 8 supports it, so it would be great if this package could provide support for it as well.

I realize that it hasn't been out for very long, but it would be good to check if anything in this project is preventing support for PHP 8.