php - Laravel migration array type (store array in database column)

Question

I want to store an array of integers in my table and I can't find any type that supports array in Documentation, any suggestion.

Migration:

public function up()
{
    Schema::create('pickups', function (Blueprint $table) {
        $table->increment('id');
        $table->boolean('default');
        $table->integer('shifts');  <<--------- HERE I want to store an array of integers
        $table->integer('status_id');

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

question from:https://stackoverflow.com/questions/32954424/laravel-migration-array-type-store-array-in-database-column

Answer 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 = AppPickup::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 = AppPickup::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
]