Change from columns to rows during old to new migration

I'm trying to convert an old database into a new format using migration (switching to Laravel motivated me to normalize data)

My old table is for "picks" for a litter, here's the columns:

id | litter_id | 1_1 | 2_1 | 3_1 | 4_1 | 5_1 | 1_2 | 2_2 | 3_2 | 4_2 | 5_2

( _1 is for females, _2 is for males )

Under each 1_1 or equivalent column, has a user_id to it (or null if nobody is assigned)

New table should be:

id | litter_id | user_id | pick_id | gender_id

Here's my migration file so far:

$oldPicks = DB::connection('old')->table('picks')->get();

    foreach($oldPicks as $query)
    {
        Pick::updateOrCreate([
            'litter_id' => $query->litter_id,
            'user_id' => $query->notsure,
            'pick_spot' => $query->notsure,
            'gender_id' => $query->notsure,
            'updated_at' => $query->updated_at,
        ]);
    }

I put "notsure" in spots where I have no clue how to convert into the new spot

Thank you in advanced

CodePudding user response：

I luckily had pick_spot and chosen_gender in my users table, that mimicked the data in my picks table. So I can just foreach every old user and insert into the new picks table

Other then that, I have no clue what would be easy to move my old to the new lol

CodePudding user response：

with DB::connection('mysql') you can change the type of database like : mysql, sqlite , ... not database name.

to change the table columns and structure using migration you can create an migration file and use code :

 public function up()
    {
        Schema::table('picks', function (Blueprint $table) {
         $table->dropColumn(['column_namw1','column_namw2']);
         $table->foreignId('user_id');
         $table->foreignId('litter_id');
         $table->foreignId('pick_id');
         $table->foreignId('gender_id');
        });
    }

in this way you can change the tables in recent database . if you want to create new database you can create new directory with new migration and run command php artisan migrate --path=/app/database/migrations/dir to migrate new migrations . be aware that you need to change the database name in .env file to migration new migrations to new database. I hope this work