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

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");

}

Answer 2

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 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

public function up()
{

    DB::statement("ALTER TABLE example CHANGE foo foo DATA_TYPE DATA_ATTRIBUTE(s) AFTER bar");
    DB::statement("ALTER TABLE example CHANGE foo foo INT(10) UNSIGNED NOT NULL AFTER bar");

}

Alternatively if you too lazy to figure out the SQL, you can visit your phpMyAdmin, click your database, click your table, click the Structure tab, besides the column you want to move, click the change button, edit the last Move column column, click the Save button & then copy the SQL.

Answer 5

VERY IMPORTANT NOTE

Use the following solution only if you haven't launched your app yet (i.e. it's not yet used by any real users) 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");
    });
}