Support array columns types

[max13fr]

Hello,

First, thanks for your really useful package !

I would like to add a field with the type integer[] (array of integer) but the type doesn't seems to exist in the columns types supported, I'm missing something ?

Thanks in advance, Max

[tpetry]

Array types are not supported. @jaulz worked on it with #29, but adding it is very complicated.

But I am right now working on adding integerArray to the package, this will be the only supported array type. For every other type, JSON should be used.

For now, you can add the column by using the domain type:

$query->domain('mycol', 'int[]');

Can you share why you need the integer array type? What do you want to do with it? I could directly add some functionality for your use case.

[max13fr]

I would like to store the list of zipcodes on each french cities. The equivalent of :

CREATE TABLE cities (
    id serial primary key;
    name VARCHAR(255) NOT NULL;
    insee_code VARCHAR(10) NOT NULL UNIQUE;
    postal_codes integer[] NOT NULL;
);

I'm not sure to completely understand #29 but just add main types as following should do the job, no ?

vendor/tpetry/laravel-postgresql-enhanced/src/Schema/BlueprintTypes.php

    /**
     * Create a new integer array column on the table.
     */
    public function integerArray(string $column): ColumnDefinition
    {
        return $this->addColumn('integerArray', $column);
    }

    /**
     * Create a new string array column on the table.
     */
    public function stringArray(string $column, ?int $length = null)
    {
        $length = $length ?: Builder::$defaultStringLength;

        return $this->addColumn('stringArray', $column, compact('length'));
    }

    /**
     * Create a new integer array column on the table.
     */
    public function textArray(string $column): ColumnDefinition
    {
        return $this->addColumn('textArray', $column);
    }
}

vendor/tpetry/laravel-postgresql-enhanced/src/Schema/Grammars/GrammarTypes.php

    /**
     * Create the column definition for a integer array type.
     */
    protected function typeIntegerArray(Fluent $column): string
    {
        return 'int[]';
    }

    /**
     * Create the column definition for a string array type.
     */
    protected function typeStringArray(Fluent $column): string
    {
        return blank($column['length']) ? 'varchar' : "varchar({$column['length']})";
    }

    /**
     * Create the column definition for a text array type.
     */
    protected function typeTextArray(Fluent $column): string
    {
        return 'text[]';
    }    

One advantage of the array type vs the jsonb type is that you have a all constraints already set from your type (ex: integer[] allows only integer inside). With a JSON you can have all types inside (object, array, integer, string, boolean, ...) even with a constraint :

CREATE TABLE test (
    tab jsonb NOT NULL CHECK (jsonb_typeof(tab) = 'array')
);

INSERT INTO test VALUES ('[1, "2", true, {}, null]');

select * from test;
=> [1, "2", true, {}, null]

With an integer array, you can only insert integer inside (number as string are automatically converted in addition) :

CREATE TABLE test (
    tab integer[] NOT NULL
);

INSERT INTO test VALUES ('{1, "2", 3}');

select * from test;
=> {1,2,3}

Max

[max13fr]

Ah ok I understood, the issue : doctrine don't know how to manage the integer array field :

Exception: Unknown database type _int4 requested, Doctrine\DBAL\Platforms\PostgreSQL100Platform may not support it.
Could not analyze class App\Models\City.

[tpetry]

Yeah, that's the problem. I need to build doctrine types for everything. For now, you can do the domain(...) trick but you may got problems with some stuff.

I may be able to work on it sometime in the next 2 weeks. I'll need that by myself currently.

[max13fr]

It's a doctrine issue, I will try to create an issue on doctrine repo.

vendor/doctrine/dbal/src/Platforms/PostgreSQLPlatform.php

    protected function initializeDoctrineTypeMappings()
    {
        $this->doctrineTypeMapping = [
            'bigint'           => 'bigint',
            'bigserial'        => 'bigint',
            'bool'             => 'boolean',
            'boolean'          => 'boolean',
            'bpchar'           => 'string',
            'bytea'            => 'blob',
            'char'             => 'string',
            'date'             => 'date',
            'datetime'         => 'datetime',
            'decimal'          => 'decimal',
            'double'           => 'float',
            'double precision' => 'float',
            'float'            => 'float',
            'float4'           => 'float',
            'float8'           => 'float',
            'inet'             => 'string',
            'int'              => 'integer',
            'int2'             => 'smallint',
            'int4'             => 'integer',
            'int8'             => 'bigint',
            'integer'          => 'integer',
            'interval'         => 'string',
            'json'             => 'json',
            'jsonb'            => 'json',
            'money'            => 'decimal',
            'numeric'          => 'decimal',
            'serial'           => 'integer',
            'serial4'          => 'integer',
            'serial8'          => 'bigint',
            'real'             => 'float',
            'smallint'         => 'smallint',
            'text'             => 'text',
            'time'             => 'time',
            'timestamp'        => 'datetime',
            'timestamptz'      => 'datetimetz',
            'timetz'           => 'time',
            'tsvector'         => 'text',
            'uuid'             => 'guid',
            'varchar'          => 'string',
            'year'             => 'date',
            '_varchar'         => 'string',
            // new line =>
            '_int4'            => 'string',
        ];
    }

With that patch integer[] are working fine (except we get a string {1, 2, 3} instead of an array in eloquent). It's seem that they already fix it for varchar array apparently.

All array are the type with underscore as prefix (even multidimensional array, for example: integer[][] => _int4)

[max13fr]

Just added a specific cast and it's working 🎉

app/Casts/PostgresArray.php (the implementation is ugly mainly for string but it's ok for integer array, it's just as demo) :

<?php

namespace App\Casts;

use Illuminate\Contracts\Database\Eloquent\CastsAttributes;
use Illuminate\Database\Eloquent\Model;

class PostgresArray implements CastsAttributes
{
    /**
     * Cast the given value.
     *
     * @param  array<string, mixed>  $attributes
     */
    public function get(Model $model, string $key, mixed $value, array $attributes): mixed
    {
        return json_decode(str_replace(['{', '}'], ['[', ']'], $value));
    }

    /**
     * Prepare the given value for storage.
     *
     * @param  array<string, mixed>  $attributes
     */
    public function set(Model $model, string $key, mixed $value, array $attributes): mixed
    {
        return str_replace(['[', ']'], ['{', '}'], json_encode($value));;
    }
}

app/Models/City.php

<?php

namespace App\Models;

use App\Casts\PostgresArray;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class City extends Model
{
    use HasFactory;

    protected $casts = [
        'postal_codes' => PostgresArray::class,
    ];
}

routes/web.php

Route::get('/test', function () {
    $city = City::first();
    dump($city->postal_codes);
    $city->postal_codes = [...$city->postal_codes, 12345];
    $city->save();
    dump($city->postal_codes);
});

[max13fr]

For the encode/decode we can inspire from Yii implementation for example :

• https://github.com/yiisoft/yii2/blob/master/framework/db/pgsql/ArrayParser.php
• https://github.com/yiisoft/yii2/blob/master/framework/db/pgsql/ArrayExpressionBuilder.php)

[tpetry]

The integer array type is now supported with 0.31.0. There is additionally a cast for easier usage and a whereIntegerArrayMatches query builder function to use all of the potentials of this marvelous data type.