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?

Handrail

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

}

Compressive

Worth noticing that the DATE in foo DATE should be changed to whatever data type you're working with. – Hennessey

Remember to add the size for VARCHAR columns, for example: DB::statement("ALTER TABLE example MODIFY COLUMN foo VARCHAR(32) AFTER bar"); – Darbee

If you want to move a foreign_id column, all you've to do, specify the type after column name, like: alter table student_ieps modify column iep_topic_id bigint unsigned after student_id – Embryectomy

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');
    });
}

Gilstrap

This is a mighty fine hack that is working even now. Thanka sir – Dick

Is there any advantage to doing it this way over the method in the other answer? This way seems like it'd take a lot more processing power and time, and yet has almost as many votes. – Aludel

@KyleChallis becuase you don't lose the database abstraction. – Learn

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

Sy

Good idea, but I recommend this: DB::statement('.. raw sql query ..'); – Dinghy

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.

Ramey

Answer 5

In newer laravel versions, this will do the job.

    public function up(): void
    {
        Schema::table('example', function (Blueprint $table) {
            $table->date('foo')->after('bar')->change();
        });
    }

Pallas

Answer 6

If you have a large of columns you need to re-arrange you cab use phpMyAdmin. There is a Move columns option add the bottom of the Structure tab.

Before hitting the Go button use the Preview SQL button and copy the query.

Next create a new migration and put this in up function:

DB::statement(".... (copy the SQL here).... ");

Fast and easy

Poly

Answer 7

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

Noelnoelani

Migrations that are destructive to data seems like bad practice. – Starlastarlene