CODEWITHSUNDEEP
laravelmariadb
mpen
mpen

Reputation: 282895

How to add a virtual column with the schema builder?

I'm creating a table like this,

Schema::create('booking_segments', function (Blueprint $table) {
    $table->increments('id');

    $table->datetime('start')->index();
    $table->integer('duration')->unsigned();
    $table->string('comments');
    $table->integer('booking_id')->unsigned();
    $table->foreign('booking_id')->references('id')->on('bookings')->onDelete('cascade');
});

But I want to add one extra column. It looks like this in raw SQL:

ALTER TABLE booking_segments ADD COLUMN `end` DATETIME AS (DATE_ADD(`start`, INTERVAL duration MINUTE)) PERSISTENT AFTER `start`

How can I add it in my migration? I will also need to create an index on it.

Upvotes: 14

Views: 19905

Answers (3)

lukasgeiter
lukasgeiter

Reputation: 152890

I don't think you can do it with the schema builder (someone please correct me if I'm wrong) but you can always "fall back" to raw SQL:

DB::statement('
    ALTER TABLE booking_segments
    ADD COLUMN `end` DATETIME AS (DATE_ADD(`start`, INTERVAL duration MINUTE)) PERSISTENT AFTER `start`
');

Upvotes: 1

Marco Caggiano
Marco Caggiano

Reputation: 317

You could also use Laravel Events to achieve the same result without using storedAs or virtualAs. For reference: Laravel: performing some task on every insert/update when using Query Builder or Eloquent ORM

This is how i've done it:

class MyModel extends Model
{
    /** .... **/

    /**
     * The "booted" method of the model.
     *
     * @return void
     */
    protected static function booted()
    {
        static::creating(function ($option) {
            $option->column1 = $option->column2 + 2;
        });

        static::updating(function ($option) {
            $option->column1 = $option->column2 + 2;
        });
    }
}

Upvotes: 1

Angelos Koufakis
Angelos Koufakis

Reputation: 416

I know this is an old question, but there is a way to do it using the schema builder since Laravel 5.3, so I thought I would put it here for completeness.

You can use laravel 5.3 column modifiers virtualAs or storedAs.

So, to create a virtual generated column to be computed at every query you would create the column like this:

$table->dateTime('created_at')->virtualAs( 'DATE_ADD(`start`, INTERVAL duration MINUTE)' );

To create a stored generated column you would create the column like this instead:

$table->dateTime('created_at')->storedAs( 'DATE_ADD(`start`, INTERVAL duration MINUTE)' );

Upvotes: 40

Related Questions