How to have BelongsToMany with multiple relations in the backend?

Hello,
I have models domain, domain_view and view.

In domain I have this realations

public $belongsToMany = [

        'views' => [
            \QBUT\Manager\Models\View::class,
            'table' => 'qbut_manager_domain_view',
            'pivot' => ['id', 'position', 'position_detail', 'active'],
            'pivotSortable' => 'sorting',
            'timestamps' => true
        ],
    ];

In the backend when I add a View model with ID 1 to the a Domain model I can only add that ID 1 View once. Why is that? Why can’t I have multiple record of the same View?
Maybe I used the wrong relationship?
I tried to add the same record manually in the database and it works and I don’t see any errors. However when I delete one type all the same View types get deleted, so there’s obviously a constraint there.

I think this is not related to how the database or relationships are set but more about how the backend is set?
Still trying to figure out how and why this the default behavior?
Why does it assume I can only have one specific View model related for each Domain model?

Hi sandros87

one unusual thing I see in your relationshop configuration, is that you included the ‘id’ in your pivot columns list. Remove that in your Domain model and the counterpart, your View model, and check if it works now.

Hi, it doesn’t change the behavior whatsoever. I can still add (in the UI) one view model once.

Well, then I would like to see the rest of your configuration:

  • relationship definitions in the models
  • relation controller definition
  • table schema for both model tables and the intermediary table

because the problem should be somewhere in there.

Just to make sure: you’re on the latest OCMS version, right?

So you think this isn’t a standard behavior and could not happen?
Just wanna add that when I select a Model View I can’t select it anymore from the list (disappears) so it’s totally intended behavior.
I’m running 3.5.5.

Here’s the configuration
Domain Model

    public $belongsToMany = [
        'views' => [
            \qbut\manager\Models\View::class,
            'table' => 'qbut_manager_domain_view',
            'pivot' => ['position', 'position_detail', 'active'],
            'pivotSortable' => 'sorting',
            'timestamps' => true
        ]
    ];

View Model

No relation definition

Domain View Model

    public $belongsTo = [
        'domain' => [
            \qbut\manager\Models\Domain::class,
        ],
        'view' => [
            \qbut\manager\Models\View::class,
        ],
    ];

Domain Controller

class Domain extends Controller
{
    public $implement = [        'Backend\Behaviors\ListController',        'Backend\Behaviors\FormController', 'Backend\Behaviors\RelationController'    ];
    
    public $listConfig = 'config_list.yaml';
    public $formConfig = 'config_form.yaml';
    public $relationConfig = 'config_relation.yaml';
}

Domain config_relation.yaml

views: 
  structure:
    showReorder: true
    showTree: false
  label: Views
  view: 
    list: 
      columns:
        name: 
          label: Name
        pivot[position]:
          label: Position
          type: text
        pivot[position_detail]:
          label: Position detail
          type: text
        pivot[active]:
          label: Active
          type: switch
  manage:
    list: $/qbut/manager/models/view/columns.yaml
  pivot:
    form:
      fields:
        name: 
          label: Name
          disabled: true
        pivot[position]:
          label: Position
          span: full
          type: text
          default: head
        pivot[position_detail]:
          label: Position detail
          span: full
          type: dropdown
          options:    
            prepend: prepend
            append: append
        pivot[active]:
          label: Active
          default: 1
          span: left
          type: checkbox
    

Schemas

mysql> describe qbut_manager_domain;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int unsigned | NO   | PRI | NULL    | auto_increment |
| domain_name | varchar(255) | NO   |     | NULL    |                |
| created_at  | timestamp    | YES  |     | NULL    |                |
| updated_at  | timestamp    | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
mysql> describe qbut_manager_domain_view; (intermediate)
+-----------------+--------------------------+------+-----+---------+----------------+
| Field           | Type                     | Null | Key | Default | Extra          |
+-----------------+--------------------------+------+-----+---------+----------------+
| position        | varchar(255)             | NO   |     | head    |                |
| active          | tinyint(1)               | NO   |     | 1       |                |
| sorting         | int                      | YES  |     | NULL    |                |
| view_id         | int                      | NO   |     | NULL    |                |
| domain_id       | int                      | NO   |     | NULL    |                |
| created_at      | timestamp                | YES  |     | NULL    |                |
| updated_at      | timestamp                | YES  |     | NULL    |                |
| id              | int unsigned             | NO   | PRI | NULL    | auto_increment |
| position_detail | enum('prepend','append') | NO   |     | prepend |                |
+-----------------+--------------------------+------+-----+---------+----------------+
mysql> describe qbut_manager_view;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int unsigned | NO   | PRI | NULL    | auto_increment |
| name            | varchar(255) | NO   |     | NULL    |                |
| filename        | varchar(255) | NO   |     | NULL    |                |
| created_at      | timestamp    | YES  |     | NULL    |                |
| updated_at      | timestamp    | YES  |     | NULL    |                |
| category_id     | int          | YES  |     | NULL    |                |
| is_cookiebanner | tinyint(1)   | NO   |     | 0       |                |
| auto_create     | tinyint(1)   | NO   |     | 0       |                |
+-----------------+--------------+------+-----+---------+----------------+

Ok ok, I think I see what is going on.

Domain View Model is intended to act as a Pivot Model to connect Domain Model and View Model.
I think you’re set up now is somewhere inbetween, because its not all propery declared.

Check out the docs here to see how to use a custom pivot model: Relationships - October CMS - 3.x

If you would like to define a custom model to represent the intermediate table of your relationship, you may use pivotModel attribute when defining the relationship. Custom many-to-many pivot models should extend the October\Rain\Database\Pivot class while custom polymorphic many-to-many pivot models should extend the October\Rain\Database\MorphPivot class.

And I would also set up the inverse relationship in the View model. I’m honestly never not doing inverse relationships, so im not actually sure if it’s really neccessary. But shouldn’t hurt.

1 Like

Hi,
ok so now that I created a custom pivot Model and used that in relationship what should I expect? Cause I’m seeing the same behavior.

Domain.php

    public $belongsToMany = [
        'views' => [
            \qbut\manager\Models\View::class,
            'pivotModel' => \qbut\manager\Models\DomainViewPivot::class,
            'table' => 'qbut_manager_domain_view',
            'pivot' => ['position', 'position_detail', 'active'],
            'pivotSortable' => 'sorting',
            'timestamps' => true
        ],
    ];

DomainViewPivot.php

<?php namespace qbut\manager\Models;

/**
 * Model
 */
class DomainViewPivot extends \October\Rain\Database\Pivot
{
    use \October\Rain\Database\Traits\Validation;

    /**
     * @var string The database table used by the model.
     */
    public $table = 'qbut_manager_domain_view';

    /**
     * @var array Validation rules
     */
    public $rules = [
    ];
}

Still don’t understand what is the advantage (database wise) for this constrain.

You maybe meant that once I have the custom Pivot model I can make custom behaviors?

@daft can you help here with some insight? I have no clue at this point.

I think me and my team decided we need to use the Premium Support to fix this “simple” issue with no documentation whatsoever on why it’s the default behavior.
Not a good sign you can get any support from free forums (and reddit). Is October project about to die?

Hi,

It’s evident from the consistent activity in weekly discussions that October is far from becoming obsolete.

Your situation appears to be particularly intricate, especially considering the assistance provided by marco.grueter in various posts.

Hence, it may be beneficial for you to invest some personalized time in articulating your issue to ensure you receive the most relevant answer. In this scenario, utilizing premium support, seems like a prudent choice.

If you need it done quickly, that’s exactly what premium support is for.

I’ll get the ball rolling here. Following on from the reddit post. Some research/notes here:

In the file modules/backend/behaviors/relationcontroller/HasManageMode.php the following code prevents duplicates from being selected:

// Exclude existing relationships
$widget->bindEvent('list.extendQuery', function ($query) {
    // Where not in the current list of related records
    $existingIds = $this->findExistingRelationIds();
    if (count($existingIds)) {
        $query->whereNotIn($this->manageModel->getQualifiedKeyName(), $existingIds);
    }
});

However, removing/disabling this code doesn’t help since October wants to replace the existing record, instead of allowing duplicates.

This can be tested in the test plugin (GitHub - octobercms/test-plugin: Test Suite & Playground for October CMS) by navigating to Users → Pivot Model tab.

Next inside the following file modules/backend/behaviors/relationcontroller/HasPivotMode.php we can actually see that there was provisions to allow multiple records with the same ID.

// Two methods are used to synchronize the records, the first inserts records in
// bulk but may encounter collisions. The fallback adds records one at a time
// and checks for collisions with existing records.
try {
    $this->relationObject->attach($foreignIds, $pivotData);
}
catch (Exception $ex) {
    $this->relationObject->sync(array_fill_keys($foreignIds, $pivotData), false);
}

When the attempt to add a second record fails, it simply updates the existing one. Removing that exception handling SQL returns the collision error stopping us (Integrity constraint violation):

"SQLSTATE[23000]: 
Integrity constraint violation: 1062
Duplicate entry '2-3' for key 'PRIMARY' 
(SQL: insert into `october_test_users_roles` (`clearance_level`, `country_id`, `created_at`, `is_executive`, `role_id`, `salary`, `updated_at`, `user_id`) values (34343334, ?, 2024-05-14 00:20:05, 0, 3, ?, 2024-05-14 00:20:05, 2))
" on line 760 of ~\vendor\laravel\framework\src\Illuminate\Database\Connection.php

Now, we need to remove that primary key constraint from the database, and voila! we have two records with the same ID attached.

A new problem emerges: the pivot data is not unique between the records. Both entries are updated when we update one pivot data (Clearance Level). We need to look into why this happens…

1 Like

Here we can see why it happens… the SQL has no way to uniquely identify each pivot record

update `october_test_users_roles`
set
    `clearance_level` = '3333',
    `october_test_users_roles`.`updated_at` = '2024-05-14 00:28:02'
where
    `user_id` = 2 and `role_id` = 3 

The above SQL updates two records in the database since they are both using user_id = 2 and role_id = 3 as duplicates.

So to answer your question @sandros87, this is a design issue and we need to understand how Laravel proposes a solution. Most likely, it is the wrong relationship type to use.

It might be best to create an intermediary model instead of using a Pivot model.

Model <-> Model <-> Model

Where the inner model belongs to the outer models. This way, the inner model will have its own primary key and can be selected as a target for updates.

I hope this helps.

1 Like

Hello @daft,
very detailed answer.
However I’m not sure I understand how to set the three model method and if I can use this method the same way the Pivot was intended for? I used the pivot relationship for a specific reason.
I’ll try to to play with the intended plugin and see if I can find a similar solution.

Hi @sandros87

We may be able to continue down this path. The following Laravel issue suggests that Laravel does indeed support auto-incrementing IDs on the pivot model.

My Pivot model (DomainView) already has auto-incrementing IDs working. As you said the problem is how the SQL query is performed on updates not the database structure.
Sorry I’m a noob with October. I also still don’t understand why @marco.grueter suggested I create a custom Pivot Model if I get the same results.

No worries. This scenario is new to me also.

You will definitely need the custom Pivot model to instruct it to use the incrementing primary key instead of the composed key, which is the default.

class DomainView extends \October\Rain\Database\Pivot
{
    public $incrementing = true;
}

Performing the core modifications mentioned above should theoretically work since this is the missing piece of the puzzle.

Once confirmed, we can adjust the logic to detect an incrementing pivot model and allow duplicates.

Hi again @sandros87

This use case has been added to the test plugin:

More work still needs to be done to add compatibility for it. I’ll let you know when it is ready.

1 Like