Laravel change String Boolean value to Int Boolean in migration

Question

I'm working with a Laravel project that has two existing columns that are set to strings and hold a visually pleasing Boolean value of true or false.

I'm trying to change these columns from string to boolean in a Laravel migration, but need to convert the existing values in the columns, for instance, if the column value for a record has the value of false, it needs to become 0.

My current migration to modify these columns doesn't seem to acheive this automatically, how can I achieve this?

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddColumnsToNotificationContactsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('notification_contacts', function (Blueprint $table) {
            $table->boolean('canNotify')->change();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('notification_contacts', function (Blueprint $table) {
            //
        });
    }
}

I get:

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: 'false' for column 'canNotify' at row 1 (SQL: ALTER TABLE notification_contacts CHANGE canNotify canNotify TINYINT(1) NOT NULL)

Does this answer your question? Laravel data migrations

mighty_dev
– mighty_dev

2021-08-02 17:59:24 +00:00
Commented Aug 2, 2021 at 17:59 — mighty_dev
– mighty_dev, Commented Aug 2, 2021 at 17:59

Mohsen Nazari · Accepted Answer · 2021-08-02 17:57:13Z

2

Do it in multiple steps instead of running it in a single operation.

Steps are:

Add a new temp Column canNotifyTemp
Populate it with the old data from Column canNotify
Delete the old column
Rename the temp column

class UpdateClientsTableAddRedesignColumns extends Migration
{
    public function up()
    {
        // step 1
        Schema::table('notification_contacts', function(Blueprint $table) {
            $table->boolean('canNotifyTemp')->default(0);
        });

        // step 2
        DB::table('notification_contacts')
            ->where('canNotify', 'true')
            ->update(["canNotifyTemp" => true]);

        DB::table('notification_contacts')
            ->where('canNotify', 'false')
            ->update(["canNotifyTemp" => false]);

        // step 3
        Schema::table('notification_contacts', function(Blueprint $table) {
            $table->dropColumn('canNotify');
        });

        // step 4
        Schema::table('notification_contacts', function(Blueprint $table) {
            $table->renameColumn('canNotifyTemp', 'canNotify');
        });
    }

    public function down()
    {
        // step 4 rollback
        Schema::table('notification_contacts', function (Blueprint $table) {
            $table->renameColumn('canNotify', 'canNotifyTemp');
        });

        // step 3 rollback
        Schema::table('notification_contacts', function (Blueprint $table) {
            $table->string('canNotify')->default('false');
        });

        // step 2 rollback
        DB::table('notification_contacts')
            ->where('canNotifyTemp', true)
            ->update(["canNotify" => 'true']);

        DB::table('notification_contacts')
            ->where('canNotifyTemp', false)
            ->update(["canNotify" => 'false']);

        // step 1 rollback
        Schema::table('notification_contacts', function (Blueprint $table) {
            $table->dropColumn('canNotifyTemp');
        });
    }
}

Note: I did not run the migration so if there is any problem, tell me.

answered Aug 2, 2021 at 17:57

Mohsen Nazari

1,3491 gold badge6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ryan H Over a year ago

This is great, works as expected, the only issue here is with indexes though, I originally had an index on the canNotify column, so when I create the temp column, I'm doing: $table->boolean('can_notify_temp')->after('email')->index(); but when the column is renamed, the index Keyname says: notification_contacts_can_notify_temp_index. I tried renaming the index but it didn't work: $table->renameIndex('can_notify_temp', 'can_notify');

Mohsen Nazari Over a year ago

@RyanH you can delete the old indexes and add new indexes after the 4th step on the table.

Collectives™ on Stack Overflow

Laravel change String Boolean value to Int Boolean in migration

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related