Laravel Migration: Combine multiple values in a new colum

i’m trying to make a migration using Laravel. The idea is to get the values of columns ‘code’ and ‘id’ in different tables, and merge them into a column ‘name’ with ‘-‘ separators. So in part it is a SQL problem.

Currently i’m trying something like this.

First migration to create the new column ‘name’

public function up()
{
    Schema::table('work_order', function (Blueprint $table) {
        $table->string('name')->nullable();
    });
}

​x
 
public function up(){    Schema::table('work_order', function (Blueprint $table) {        $table->string('name')->nullable();    });}​

(works just fine)

And second migration to populate the new column with values

class AlterColumnNameWorkOrder extends Migration
{
    public function up()
    {
        
        $company_code = DB::statement("SELECT code FROM company");
        $campaign_code = DB::statement("SELECT code FROM campaign");
        $work_order_number = DB::statement("SELECT id FROM work_order");

        DB::statement("UPDATE work_order SET name = $company_code + '-' + $campaign_code + '-' + $work_order_number");
    }

 
class AlterColumnNameWorkOrder extends Migration{    public function up()    {                $company_code = DB::statement("SELECT code FROM company");        $campaign_code = DB::statement("SELECT code FROM campaign");        $work_order_number = DB::statement("SELECT id FROM work_order");​        DB::statement("UPDATE work_order SET name = $company_code + '-' + $campaign_code + '-' + $work_order_number");    }​

and i’m getting this error

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for integer: "-"  
  LINE 1: UPDATE work_order SET name = 1 + '-' + 1 + '-' + 1

 
SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for integer: "-"    LINE 1: UPDATE work_order SET name = 1 + '-' + 1 + '-' + 1 ​

I’m thinking that i’m not getting the values but the index instead.

UPDATE: I solved the problem by replacing + for ||. But now in column ‘name’ all my values are “1-1-1”. The values are not being represented. What am i missing?

UPDATE2: I noticed that i was defining an unnecessary variable $work_order_number. As it belongs to the same table to be updated. So I removed it and put the field “id” directly in the statement.

DB::statement("UPDATE work_order SET name = $company_code || '-' || $campaign_code || '-' || id");

 
DB::statement("UPDATE work_order SET name = $company_code || '-' || $campaign_code || '-' || id");​

Now the third value is shown correctly. And this reduces my problem to getting values from another table into an column.

Answer

First of all, you shouldn’t use migrations to populate the database. Read the documentation about seeds.

Also, i think your select query is wrong. Isn’t you missing a where clause?

Run php artisan migrate to migrate your database, then create a new seed, and add this to the run method:

$company_code = DB::table('company')->select('code')->where('id', some_id)->first()->code;

$campaing_code = DB::table('campaing')->select('code')->where('id', some_id)->first()->code;

$work_order_number = DB::table('work_number')->select('id')->where('id', some_id)->first()->id;

$sql = "UPDATE work_order SET name = concat('{$company_code}', '-', '{$campaign_code}', '-', '{$work_order_number}');

DB::statement(DB::raw($sql));

 
$company_code = DB::table('company')->select('code')->where('id', some_id)->first()->code;​$campaing_code = DB::table('campaing')->select('code')->where('id', some_id)->first()->code;​$work_order_number = DB::table('work_number')->select('id')->where('id', some_id)->first()->id;​$sql = "UPDATE work_order SET name = concat('{$company_code}', '-', '{$campaign_code}', '-', '{$work_order_number}');​DB::statement(DB::raw($sql));​

Now, just run php artisan db:seed --class=YourSeedClassName and it should work.

Advertisement

Answer