Laravel Migration: Combine multiple values in a new colum

Question

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

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

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 

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

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

Answer 1

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

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