Laravel Eloquent supporting MariaDb dynamic column

Question

For Dynamic column supported in Maria-DB and in MySQL we have JSON column type. For one of our projects, we should be implementing a database for Maria-DB (not Mysql).

The Dynamic Column is supported using yii2-dynamic-ar package.

how can can override Eloquent orm in Laravel to add dynamic-columns. in the Yii package which added this feature to ActiveRecord this classes can override ActiveRecord class

implementations classes in Yii framework to support in ActiveRecord ORM:

1. DynamicActiveRecord.php
2. DynamicActiveQuery.php

Answer 1

I just created package for handling MariaDB dynamic Column using eloquent and query builder.

To install package run this command:

composer require halalsoft/laravel-dynamic-column

You can start using the package by adding the HasDynamicColumn trait and use Dynamic as attribute cast to your models.

An example:

use Illuminate\Database\Eloquent\Model;
use Halalsoft\LaravelDynamicColumn\Dynamic;
use Halalsoft\LaravelDynamicColumn\HasDynamicColumn;

class MyModel extends Model
{
    use HasDynamicColumn;
    protected $casts
        = [
            'the_column' => Dynamic::class,
        ];
}

Now You can use dynamic column like json column using eloquent or query builder:

$modelData = MyModel::find(1);

$columnData = $modelData->the_column;

$columnData['data1'] = 'value';
$columnData['data2'] = 'value2';


$modelData->the_column = $columnData;

$modelData->save();

You can also create data field as array

$newData = MyModel::create([
    'other_column' => 'this just another column data',
    'the_column' => ['data1'=>'value1','data2'=>'value2']
]);

to update a json field/key you use, you may use the -> operator when calling the update method:

$page->update(['content->data1' => 'value1new']);

or you can still update whole column using normal array:

$page->update(['content' => ['data1'=>'value1new','data2'=>'value2new']]);

You can set as array using other method like updateOrCreate(), firstOrCreate(), etc.

This package also support query builder using:

Model::query()->where('the_column->data1', 'value1')->first();

This package is still new, if any issue or request just go to github issue

Answer 2

You can have a cast defined for the column in the Model class

//Model class

protected $casts = ['my_column' => 'array];

You can define the datatype for the column as text if you want or json, with the cast defined, you will be able to work with the column data as associative array.

There's also a package to add json datatype in migration for mariadb - it may be of help https://github.com/ybr-nx/laravel-mariadb