Add support for saving pivot data directly via widgets

[RamonDiaz31]

I use WinterCMS for various projects. In one of them, I needed to save information to a model with a belongsToMany relationship, including pivot data.

The Behavior RelationController provided by WinterCMS was not suitable for the specific requirements of this project. Upon reviewing the default Relation Widget, I decided to replicate its behavior in a custom widget.

Using getSaveValue(), my widget returns a nested array, where the field name corresponds to the name of the belongsToMany relation. When no extra data is defined in the pivot table, it correctly saves the related IDs to the database.

However, once I added an extra column (columnA) to the pivot table, the system began throwing an error: "Field 'columnA' doesn't have a default value". Setting that column to allow null removed the error, but only the related IDs were stored, columnA was always saved as null.

After several trials and adjustments to the input name structure, I encountered a different error: "Invalid column name 'pivot'". At the time, the input name format I used was:

input name="[Model][Relation][relation_id][pivot][columnA]" value="A"

Changing it to:

input name="[Model][Relation][relation_id][columnA]" value="A"

Allowed the value of columnA to be saved successfully to the pivot table. This led me to believe that I could add multiple pivot columns using a similar approach.

Unfortunately, I was wrong. When I tried adding three columns like so:

input name="[Model][Relation][relation_id][columnA]" value="A"
input name="[Model][Relation][relation_id][columnB]" value="B"
input name="[Model][Relation][relation_id][columnC]" value="C"

WinterCMS threw the following error: "Nested Arrays may not be passed to whereIn method".

Eventually, I found the root cause in the file:

/vendor/winter/storm/src/Database/Relations/BelongsToMany.php

Specifically, the issue was in the setSimpleValue() function. This function uses whereIn() (which doesn't accept nested arrays) to set the relationship, and uses sync() (which support nested arrays for storing pivot data ) to save the data.

I made adjustments to this function, enabling widgets to save extra pivot data in belongsToMany relationships. I've successfully tested these changes over the course of a week, and I believe they can be useful to other WinterCMS developers facing similar issues.

For the project where I needed this functionality, I ultimately decided to handle pivot data manually (without a widget) for the time being.