How to add a random string column to laravel migration

Question

I have a table called 'threads' in my laravel app, to which i want to add a new column named key with random strings of length 5. I ran the php artisan migrate:make add_key_to_threads on the CLL to create the migration file. In my generated migration file, the up function, i then used

Schema::table('threads', function($table){
            $table->str_random('key', 5);
        })

to add the new column, after which i ran php artisan migrate on my CLL. Obviously, The method str_random() does not exist (found it in a resource while googling around) as i keep getting the error "[BadMethodCallException] Method str_random does not exist". Can someone kindly guide me in doing this correctly.

Answer 1

Schema::table is only to update you Database table, without using to coresponding SQL statments. In SQL there is no column type that automatically fills your table with a random string. Therefor the column type hast to be string. See all types that exit in Laravel 5.5 https://laravel.com/docs/5.5/migrations#columns

$table->string('key', 5);

The next question is if the key must be unique, if so then you can append the unique() function:

$table->string('key', 5)->unique();

If you have already data than you have to to this in a secound step, after, you fild in all your keys.

You have two options to fill random data in your column, with PHP (Laravel) or with SQL. With SQL you have to write a function and the function can be different depending on your SQL Server Type e.g.: Default random 10 character string value for SQL Server column

With Laravel Eloqent you don't have to think about what SQL Server you have.

$threads      = new Threads();
$threads->key = str_random(5);
$threads->save();

If you used the ->unique() function this could throw an exception if your APP create a key twice. Then you have to catch the Exception and try it again.

/* only if you need a unique Key */
$threads     = new Threads();

while (true)
{
    try
    {
        $threads->key = str_random(5);
        $threads->save();
        //  We could save now we break out of the loop
        break;
    }
    catch (Exception $e)
    {
        //  Could not save, try it again
    }
}

For you existing rows you can change the migration as follows:

public function up()
{
    Schema::table('threads', function (Blueprint $table)
    {
        $table->string('key', 5);
    });

    $existing_threads = Threads::where('key', '')->get();

    foreach($existing_threads as $threads)
    {
        $threads->key = str_random(5);
        $threads->save();                
    }
}

The Model Threads must exit before you do the migration

Answer 2

As far as I know it's possible. In migration you need to create ordinary string column like this:

$table->string('key', 5);

and then when creating new model, you should run:

$model->key = str_random(5);

Assuming you use Eloquent when creating your model you can use the following syntax:

$thread = new Thread();
// below are sample fields you normally save for this thread
$thread->field_1 = 'value1';
$thread->field_2 = 'value2';
// this is the random key you set
$thread->key = str_random(5);
// now you save thread in database
$thread->save();

Answer 3

Here you go:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AddRandomStringKeyToThreadsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('threads', function (Blueprint $table) {
            $table->string('key');
        });

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('threads', function (Blueprint $table) {
            $table->dropColumn('key');
        });
    }
}

update

I have changed the $table->string('key')->default(str_random(5)); to this $table->string('key');

And then make this route just to update the existing rows... This worked for me...maybe you will need to modify this for your table. But at least you will get an idea how to do it.

Route::get('/update-table', function () {
    $count = DB::table('threads')->count();

    while ($count > 1){
        DB::table('threads')
            ->where('id', '=', $count)
            ->update(['key' => str_random(5)]);
        $count--;
    }

    return 'DB updated';
});

And then you need to create a random string for every new thread.

Answer 4

Simplely use $this to use your random string generator in migration. Example:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class Projects extends Migration
{

    public function up()
    {
        Schema::create('example', function (Blueprint $table) {
            $table->bigIncrements('id');
            // use $this to access class function
            $table->string($this->randString(5));
        });

    }

    // just a random string generator
    public function randString($length){
        $characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $charactersLength = strlen($characters);
        $randomString = '';
        for ($i = 0; $i < $length; $i++) {
            $randomString .= $characters[rand(0, $charactersLength - 1)];
        }
        return $randomString;

    }


}