Update table and add data in a Laravel 5 Migration

Question

I need to add a new column in my laravel Project, no problem for this, I used the Schema::table() to update and it's ok. Now I need to find out how many records I have on this table and update with some value.

I have the table Warrants:

Schema::create('warrant_grants', function(Blueprint $table) {
    $table->increments('id');
    $table->integer('warrant_plan_id');
    $table->integer('shareholder_id');
});

So I created the new field with a new migration file:

Schema::table('warrant_grants',function ($table) {
    $table->string('name',100);
});

Now I need to update this field name in the table with some values, for example if the table has 100 records, then I need to insert in every row the value "Warrant-X" where X is a number starting with 1 to 100. For example:

Warrant-1, Warrant-2, ....Warrant-100.

I spent hours looking for some way to do this using Seeds but I didn't found. So basically i have two questions:

• Can I use Seeds in Laravel 5 to update values or I can just insert them?
• Can I create some SQL inside the Seeds (or migrations) to do this update for me?

Solution 1

Based on this link i found the answer: https://stackoverflow.com/a/23506744/4650792

Schema::table('warrant_grants',function ($table){
        $table->string('name',100)->after('id')->nullable();
    });

    $results = DB::table('warrant_grants')->select('id','name')->get();

    $i = 1;
    foreach ($results as $result){
        DB::table('warrant_grants')
            ->where('id',$result->id)
            ->update([
                "name" => "Warrant-".$i
        ]);
        $i++;
    }

Thanks for the help anyway guys.

Solution 2

Other answers are correct. But note that if you have a lot of records, updating all of them with ORM can take time. Use raw SQL queries to do that faster.

Schema::table('warrant_grants',function ($table){
        $table->string('name',100)->after('id')->nullable();
    });
DB::raw("UPDATE warrant_grants SET name=name+id");

The SQL query is not exact, and you have to make it for your own DB, but you get the point.

Solution 3

Yes, you can perform updates/inserts/whatever in your migrations. For example:

Schema::table('warrant_grants', function($table) {
    $table->string('name', 100);
});

$i = 1;
foreach (WarrantGrants::all() as $warrant_grant) {
    $warrant_grant->update([
      'name' => 'Warrant-' . $i
    ]);

    $i++;
}

Solution 4

Another possible syntax to achieve this:

DB::table('warrant_grants')
   ->where('id',$result->id)
   ->update([
      "name" => DB::raw("'Warrant-' + `name`")
   ]);

This allows the update to be done as one batch rather than iterating over results, and retains most of the familiar Eloquent syntax rather than falling back to just using raw SQL.

The string concatenation syntax may need to be changed depending on the SQL variant used.

View more solutions