34

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?
Barry
  • 3,303
  • 7
  • 23
  • 42
Deric Lima
  • 1,972
  • 2
  • 24
  • 33

4 Answers4

36

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.

Community
  • 1
  • 1
Deric Lima
  • 1,972
  • 2
  • 24
  • 33
13

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.

vfsoraki
  • 2,186
  • 1
  • 20
  • 45
  • 3
    Actually, to execute a sql command the correct method to use would be ```DB::statement``` rather than ```DB::raw``` – Emanuel S. Mar 24 '21 at 10:07
10

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++;
}
Oliver Maksimovic
  • 3,204
  • 3
  • 28
  • 44
  • 10
    Generally, it is not a good idea to be using models in your migrations since that model can change in the future. That might break your migrations. Check Deric Lima's answer on how to use low-level queries without using Laravel models. – Patrik Fuhrmann Nov 06 '18 at 12:27
  • @PatrikFuhrmann what exactly is going to be changed from the above model usage? He uses two standard Eloquent Model methods which are `all` and `update` and these are not going to change nor be absent at any time. The official documentation even has model usage inside migrations as you can see here https://laravel.com/docs/9.x/migrations#column-method-foreignIdFor – Christos Lytras Oct 01 '22 at 22:50
8

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.

rh16
  • 1,056
  • 14
  • 24