2

I would like to add an un-nullable column to an existing table with some rows in Laravel migration. In SQL, I understand such an action should be performed inside a transaction in the order of

  1. adding a column
  2. initialising the column
  3. making it un-nullable

so as to guarantee

  • the initialisation to be performed without disrupting the DB integrity, and
  • ALTER TABLE not to violate the NOT NULL constraint,

The following is an example PostgreSQL code (assuming users table has a column old_col), referring to an answer:

BEGIN TRANSACTION;
  ALTER TABLE users ADD COLUMN new_col integer;
  UPDATE users SET new_col = old_col + 1;
  ALTER TABLE users ALTER COLUMN new_col SET NOT NULL;
COMMIT;

An ordinary Laravel migration file like this would not work.

public function up()
{
    Schema::table('users', function($table) {
        $table->integer('new_col');  // ->nullable(false) // later?
    });
}

How can one implement a SQL transaction or its equivalent in Laravel migration?

NOTE (edited):
If you want to set up the default value, and if you do not need to (absolutely simultaneously) update the column for the existing rows as a function of some values of each row, then you can simply specify ->default(0) or something like that in the migration file (and avoid all the tricks!). My intention of the question was not to set up the default for the column to add.

Masa Sakano
  • 1,921
  • 20
  • 32

3 Answers3

2

The solution with three queries:

DB::transaction(function () {
    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable();
    });

    App\Models\User::query()->update([
        'id_cloned' => DB::raw('id + 1'),
        'updated_at' => DB::raw('now()') // if you want to touch the timestamp
    ]);

    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable(false)->change();
    });
});

Alternative solution without DB::raw parts, but will generate separate update query for every record:

DB::transaction(function () {
    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable();
    });

    foreach (App\Models\User::all() as $row) {
        $row->id_cloned = $row->id + 1;
        $row->save();
    }

    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable(false)->change();
    });
});
Andrius Rimkus
  • 643
  • 5
  • 10
  • 1
    I see! I have confirmed `DB::transaction()` certainly works and the exclusive workflow is guaranteed! There are a few problems, though. First, you need `->nullable(false)` in the last statement. Second, the `updated_at` column is not updated (though it depends whether you want to update them or not). Third, `doctrine/dbal` must be installed. Fourth, the model should be `App\User`. Let me update and improve your answer. Thanks! – Masa Sakano Sep 14 '18 at 22:31
  • @MasaSakano, thanks for the review. I agree on the `->nullable(false)` part, sorry, didn't test properly. `updated_at` is a nice touch, but as you said, it depends. I cannot understand the `doctrine/dbal` part. Laravel has a dependency for this package, can't we assume it's there by default? For the `App\User` part, I imported the proper namespace, but probably being explicit in the example is better. No sure if this is the right place to comment on your proposed changes. Just wanted to note that while the solution with loop/update is nice, it's also much heavier on the DB. – Andrius Rimkus Sep 15 '18 at 15:03
  • 1
    Thank you for your reply and improved answer, Andrius! In fact, in order to test this, I made a clean install of the newest stable Laravel (Ver.5.7.3) with `composer create-project --prefer-dist laravel/laravel`, and found `doctrine/dbal` was not installed in default and also found `App\User` was the default path. Your last comment is absolutly correct! Let's hope Laravel will provide a nicer and DB-lighter way to deal with such cases in the future. – Masa Sakano Sep 16 '18 at 02:52
0

You need to set default value to whatever you want:

public function up()
{
    Schema::table('users', function($table) {
        $table->integer('new_col')->default(0); 
    });
}
Diogo Gomes
  • 2,135
  • 16
  • 13
  • At least on Postgres, column's default value cannot be derived from another column: https://stackoverflow.com/questions/16737738/postgresql-set-a-default-cell-value-according-to-another-cell-value – Andrius Rimkus Sep 13 '18 at 23:03
  • Sorry, I should have said I didn't want to set up the default value… Besides, as @AndriusRimkus rightly pointed out, you can not set the default value using another column. Having said that, it is possible to write statements immediately after the Schema statement to update the existing rows, if you are 100% sure there is no conpeting DB update requests during the migration. – Masa Sakano Sep 14 '18 at 22:20
0

you can your code in foreach like $methodName = 'item->get'.$method.'()';

class Item {

getFoo();...

getBar();...

}

$methods = ['Foo','Bar'];

foreach($methods as $method){

$methodName = 'item->get'.$method.'()';

echo $methodName;

}

user229044
  • 232,980
  • 40
  • 330
  • 338
ujjwal
  • 34
  • 1