Refactor database schema on migration

This is my initial migration

 Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->String('customer_name');
    $table->String('customer_phone');
    //others
 });

Now, the easy time ended and now I have to move name and phone to customer table. This is my create customer migration. I don’t know where to do this operation so I just throw all in the migration.

Schema::create('customers', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('phone', 15);
    //others
});

$users = DB::table('orders')->select(['customer_name', 'customer_phone'])->distinct()->get();

foreach ($users as $user) {
    $attr['name'] = $user->customer_name;
    $attr['phone'] = $user->customer_phone;
    Customer::create($attr);
}

And alter order migration

Schema::table('orders', function (Blueprint $table) {
    $table->unsignedBigInteger('customer_id')->nullable()->after('id');
    $table->foreign('customer_id')->references('id')->on('customers');
});

Now I’m stuck with empty customer_id. I have no clue what to do next, except manually insert it by referring to two table, which can take too much time to update hundreds of rows.

Answer

Try use this statement on up function.

DB::statement("UPDATE `orders` o SET `customer_id` = (SELECT `id` FROM `customers` WHERE `name` = o.customer_name AND `phone` = o.customer_phone)");

And you might want to drop those two column. Throw this too.

Schema::table('orders', function (Blueprint $table) {
    $table->dropColumn(['customer_name', 'customer_phone']);
});