How to set value for additional column in pivot table in OctoberCMS?

Question

I have doctor and specialization table, and have doctor_specialization_pivot table. In my pivot table I have the following columns:

| doctor_id | additional_data | specialization_id |

additional_data is from the doctor model along with the doctor_id.

In my doctor model file, I have this relationship:

public $belongsToMany = [
    'specialization' => [
        'path\to\specialization\model', 
        'table' => 'doctor_specialization_pivot', 
        'parentKey' => 'doctor_id', 
        'otherKey' => 'specialization_id',
    ]
];

Now during submit of form, I'm getting this error:

SQLSTATE[HY000]: General error: 1364 Field 'additional_data' doesn't have a default value (SQL: insert into doctor_specialization_pivot (doctor_id, specializations_id) values (1, 3))"

I tried adding to my relationship 'pivot' => ['additional_data']. But still getting the same error.

I checked the submitted data and additional_data is not empty. I checked from OctoberCMS forums but not getting straight forward answers such as this and this.

Answer 1

Okay. I found the answer to my own question.

I'll answer in detail to help everyone. After digging and blind shooting. According to this documentation here, we can use the method attach() to attach a role to a user by inserting a record in the intermediate table that joins the models.

What confuse me in the documentation is that it uses a $roleId variable and I didn't understand where the $roleId came from. If it's the id of the parent table or the id of other table.

Sample from the link:

$user = User::find(1);

$user->roles()->attach($roleId);

So what I did in my doctor model, I hook to the event beforeSave, use the relationship ($this->specialization) as the first parameter instead of the id in the docs. The $this->specialization() is the relationship too defined in belongsToMany.

Answer:

public function beforeSave()
{
    $this->specialization()->attach($this->specialization,['additional_data' => 'additional data from doctor table']);
}

Answer 2

The implementation is pretty much like this video from Watch Learn (Ivan). You can learn a lot about OctoberCMS just by watching his guide on it. Here is the documentation on it as well. This is the example info that I have done.

WARNING Another known flaw is you can't apply this to a model record that isn't created yet. Unlike the standard relation widget which waits until it is saved before attaching records this attaches records in a separate overlay form.

Here is my model.php relationship:

public $belongsToMany = [
    'equipments' => [
        'Brandon\Pixelrpg\Models\Equipments',
        'table' => 'brandon_pixelrpg_equipment_inventory',
        'key' => 'inventory',
        'otherKey' => 'equipment',
        'pivot' => ['quantity']
    ]
];

Here is my controller.php:

public $implement = [        
    'Backend\Behaviors\ListController',
    'Backend\Behaviors\FormController',
    'Backend\Behaviors\ReorderController',
    'Backend\Behaviors\RelationController'
];

public $listConfig = 'config_list.yaml';
public $formConfig = 'config_form.yaml';
public $reorderConfig = 'config_reorder.yaml';
public $relationConfig = 'config_relation.yaml';

Here is my config_relation.yaml:

equipments:
    label: Equipments
    view:
        list:
            columns:
                id:
                    label: ID
                    type: number
                    searchable: true
                    sortable: true
                name:
                    label: Name
                    type: text
                    searchable: true
                    sortable: true
                value:
                    label: Value
                    type: number
                    searchable: true
                    sortable: true
                updated_at:
                    label: Updated
                    type: datetime
                    searchable: true
                    sortable: true
                pivot[quantity]:
                    label: Quantity
                    type: number

    pivot:
        form:
            fields:
                pivot[quantity]:
                    label: Quantity
                    type: number
                    default: 0

Answer 3

I am just going to make a new answer and assume is what you need because you have yet to show any code on how your form works. This is how I would update the pivot information from a frontend form.

Relationship in model.php:

public $belongsToMany = [
    'specialization' => [
        'path\to\specialization\model', 
        'table' => 'doctor_specialization_pivot', 
        'parentKey' => 'doctor_id', 
        'otherKey' => 'specialization_id',
        'pivot' => ['additional_data'] //This is required
    ]
];

Then in some php code lets call it onAddSpecialization():

public function onAddSpecialization() {
    //Calling a function to get the doctor id maybe from the signed in user
    $doctor = Doctors::find($this->$doctorId());

    //We get our Specialization from an input
    $specialization = Specialization::find(Input::get('specialization_id'));

    //We get our additional data from an input
    $additional_data = Input::get('additional_data');

    //Now we are going to attach the information
    $doctor->specialization()->attach($specialization, ['additional_data' => $additional_data]);
}

Now an example of updating our additional data:

public function onUpdateAdditionalData() {
    //Calling a function to get the doctor id maybe from the signed in user
    $doctor = Doctors::find($this->$doctorId());

    //If you get specialization by id from an input. I believe you need to go through the relationship in order to access the correct pivot information.
    $specialization = $doctor->specialization->where('id', Input::get('specialization_id'))->first();

    //Insert the new pivot information
    $specialization->pivot->additional_data = $new_additional_data;

    //Save
    $specialization->pivot->save();
}