What is the MySQL datatype SET equivalent in Larav

Question

Laravel Schema has a command for ENUM equivalent to the table. What is the SET equivalent to the table?

Answer 1

Roman Nazarkin's method works almost perfectly however there is a small issue with table prefixes (which this method does not account for) it is simple however to make this suggestion work with table prefixes:

$grammar = DB::connection()->withTablePrefix(new ExtendedMySqlGrammar());
// set new grammar class
DB::connection()->setSchemaGrammar($grammar);

// get custom schema object
$schema = DB::connection()->getSchemaBuilder();

// bind new blueprint class
$schema->blueprintResolver(function($table, $callback) {
    return new ExtendedBlueprint($table, $callback);
});

// then create tables 
$schema->create('table name', function(ExtendedBlueprint $table)
{
    $table->increments('id');
    $table->text('sentence');
    $table->string('author')->nullable();
    $table->string('source')->nullable();
    $table->set('difficulty', range(1, 10)); // use our new mysql type 
    $table->boolean('enabled')->default(true);
});

Answer 2

According to the Laravel API, I don't think it is possible to create a set using the Schema Builder.

Source: http://laravel.com/api/class-Illuminate.Database.Schema.Blueprint.html

Answer 3

Extending laravel database schema methods is not too hard. Like Roman wrote, instead of extending, you can as well update your

vendor/laravel/framework/src/Illuminate/Database/Schema/Grammers/MysqlGrammer.php

/**
 * Create the column definition for an set type.
 *
 * @param  \Illuminate\Support\Fluent  $column
 * @return string
*/
protected function typeSet(Fluent $column){
    return "set('".implode("', '", $column->allowed)."')";
}

vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php

/**
     * Create a new set column on the table.
     *
     * @param  string  $column
     * @param  array   $allowed
     * @return \Illuminate\Support\Fluent
    */
    public function set($column, array $allowed){
        return $this->addColumn('set', $column, compact('allowed'));
    }

After this, terminate your server by pressing Ctrl + C. Then type php artisan serve to start the laravel.

Answer 4

Step 1. Extend default classes(add this code to your migration file after use sections):

class ExtendedBlueprint extends Blueprint {

    /**
     * Create a new set column on the table.
     *
     * @param  string  $column
     * @param  array   $allowed
     * @return \Illuminate\Support\Fluent
     */
    public function set($column, array $allowed)
    {
        return $this->addColumn('set', $column, compact('allowed'));
    }

}


class ExtendedMySqlGrammar extends Illuminate\Database\Schema\Grammars\MySqlGrammar {

    /**
     * Create the column definition for an set type.
     *
     * @param  \Illuminate\Support\Fluent  $column
     * @return string
     */
    protected function typeSet(\Illuminate\Support\Fluent $column)
    {
        return "set('".implode("', '", $column->allowed)."')";
    }

}

Step 2. Then, we need to change default grammar and blueprint classes to our custom:

// set new grammar class
DB::connection()->setSchemaGrammar(new ExtendedMySqlGrammar());

// get custom schema object
$schema = DB::connection()->getSchemaBuilder();

// bind new blueprint class
$schema->blueprintResolver(function($table, $callback) {
    return new ExtendedBlueprint($table, $callback);
});

// then create tables 
$schema->create('table name', function(ExtendedBlueprint $table)
{
    $table->increments('id');
    $table->text('sentence');
    $table->string('author')->nullable();
    $table->string('source')->nullable();
    $table->set('difficulty', range(1, 10)); // use our new mysql type 
    $table->boolean('enabled')->default(true);
});

This method will also work after composer update, because we did not edited any framework code.

Answer 5

As of now Laravel Schema Builder does not support SET datatype for columns. So, here is an alternative solution until someone add those code to Laravel.

Step 1: Create the table, use ENUM instead of SET.

Schema::create('schools', function($table)
{
    $table->increments('id');
    $table->char('id_number', 6);
    $table->string('school_name');
    $table->enum('level', array('Preschool', 'Kindergarten', 'Primary', 'Secondary'))->index(); // *** fix this
    $table->string('phone');
    $table->string('email');
    $table->string('location');
    $table->smallInteger('city')->unsigned()->index();
    $table->smallInteger('country')->unsigned()->index();
    $table->smallInteger('head_teacher')->unsigned()->index();
    $table->smallInteger('director')->unsigned()->index();
    $table->smallInteger('created_by')->unsigned();
    $table->smallInteger('modified_by')->unsigned();
    $table->timestamps();
});

Step 2: Now change ENUM to SET.

$table_prefix = DB::getTablePrefix();
DB::statement("ALTER TABLE `" . $table_prefix . "schools` CHANGE `level` `level` SET('Preschool','Kindergarten','Primary','Secondary');");

If you have a better solution, then please let me know.