Laravel migration array type (store array in database column)

Solution 1:

The array datatype is not present in all database systems and because Laravel's Schema Builder is database agnostic, it doesn't offer methods to create non-common datatype columns. So you have two options:

1. Use a raw SQL statement to add the column, something like the statement below I think should work. Although I'm not sure if the Query Builder or Eloquent can handle these types of columns correctly:

DB::statement('ALTER TABLE pickups ADD COLUMN shifts integer[]');

2. Use Eloquent's available workaround by using attribute casting. In your migration create the column as json like so:

public function up()
{
    Schema::create('pickups', function (Blueprint $table) {
        $table->increment('id');
        $table->boolean('default');
        $table->json('shifts');
        $table->integer('status_id');

        $table->timestamps();
    });
}

Then you can setup your Pickup model (if you haven't done so already) and use the $casts property:

class Pickup extends Model
{
    protected $casts = [
        'shifts' => 'array'
    ];
}

This will let Eloquent know that when it fetches data from the database it will have to convert the shifts column value to an array. This is only emulating an actual array, as at the database level the column is of type TEXT and the array is serialized. However when unserializing the column value, Eloquent returns an actual array of integers for you to use in your code. Below is an example use case:

// Create a new Pickup entry
$pickup = App\Pickup::create([
    'default' => true,
    'shifts' => '[1, 5, 7]', // you can easily assign an actual integer array here
    'status_id' => 1
]);

Assuming the above generated an entry with id equal to 1 when you later retrieve the entry:

$pickup = App\Pickup::find(1);
dump($pickup->shifts);

The dump() from the code above will output an actual array of integers:

array:3 [▼
  0 => 1
  1 => 5
  2 => 7
]

Solution 2:

There is another more complicated approach, but it allows to create truly native arrays using the schema builder.

Example for PostgreSQL.

1. Register new int_array type which will resolve into int[] by extending the existing DB Schema Grammar:

\DB::connection()->setSchemaGrammar(new class extends PostgresGrammar {
    protected function typeInt_array(\Illuminate\Support\Fluent $column)
    {
        return 'int[]';
    }
});

You can put this code right inside the migration if you need it just once or into AppServiceProvider to make it available in whole project.

2. Now you can use this type in your migrations:

Schema::table('users', function (Blueprint $table) {
    $table->addColumn('int_array', 'group_ids')->nullable();
});

Solution 3:

I generally store array in the column as a comma-separated string and retrieve it with explode.

So in your case while storing I would dod something like this:

$arraystring = implode(',',$array);

$pickups->shifts = $arraystring; //$pickups is an instance of your Pickups model.

//Then while retrieving I would just use;

$array = $pickups->shifts ? explode(',',$pickups-shifts) : []; 
/*just to make sure it is not an empty string 
else you will get an error on explode.*/