laravel migration re-organising column order

Question

When you create a new column in a table you can use the ->after('column name') to dictate where it goes. How can I create a migration that re-orders the columns in the right order I want?

Answer 1

If you want to do it without destroying data, you could migrate the data across at the same time you do the schema update:

use DB;

public function up()
{
    //Give the moving column a temporary name:
    Schema::table('users', function($table)
    {
        $table->renameColumn('name', 'name_old');
    });

    //Add a new column with the regular name:
    Schema::table('users', function(Blueprint $table)
    {
        $table->string('name')->after('city');
    });

    //Copy the data across to the new column:
    DB::table('users')->update([
        'name' => DB::raw('name_old')   
    ]);

    //Remove the old column:
    Schema::table('users', function(Blueprint $table)
    {
        $table->dropColumn('name_old');
    });
}

Answer 2

Very Important Note

Use the following solution only if you still in the development phase and you haven't launched your app yet as the following solution will delete the column and all data stored in it and will create a new empty column with the same name after the column you determine.

---

Suppose your column name is address and you want to reorder its position so that it comes after another column called city, and your table name is employees.

In your terminal type the next command:

php artisan migrate:make reorganize_order_of_column_address --table=employees

You may only change reorganize_order_of_column_address and employees according to your needs, but keep the rest of the command as it is.

This will generate a migration file in app/database/migrations folder, open it and put your code inside the up() function like this:

public function up()
{
    Schema::table('employees', function(Blueprint $table)
    {
        $table->dropColumn("address");
    });

    Schema::table('employees', function(Blueprint $table)
    {
        $table->string('address')->after("city");
    });
}

Answer 3

I would suggest a DB::query('.. raw sql query ..'); and use the query from the answer "How to move columns in a MySQL table?"

Answer 4

Try this, hope it help you to find right solution:

public function up()
{

    DB::statement("ALTER TABLE example MODIFY COLUMN foo DATE AFTER bar");

}

public function down()
{

    DB::statement("ALTER TABLE example MODIFY COLUMN foo DATE AFTER bar");

}