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.
Advertisement
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));
Now, just run php artisan db:seed --class=YourSeedClassName
and it should work.