How do I add a virtual column with a schema builder?
I am creating a table like this one,
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 more 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 to my migration? I will also need to create an index on it.
+3
source to share
2 answers
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'd put it here for completeness.
You can use laravel 5.3 column modifiers virtualAs or storedAs.
So, to create a virtual generated column for each query, you have to create a column like this:
$table->dateTime('created_at')->virtualAs( 'DATE_ADD(`start`, INTERVAL duration MINUTE)' );
To create the created column, you have to create a column like this:
$table->dateTime('created_at')->storedAs( 'DATE_ADD(`start`, INTERVAL duration MINUTE)' );
+7
source to share
I don't think you can do this with a schema builder (someone please correct me if I'm wrong), but you can always "fall back" to the raw SQL:
DB::statement('ALTER TABLE booking_segments ADD COLUMN `end` DATETIME AS (DATE_ADD(`start`, INTERVAL duration MINUTE)) PERSISTENT AFTER `start`');
+1
source to share