How to have BelongsToMany with multiple relations in the backend?

Need Help
#6

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       |                |
+-----------------+--------------+------+-----+---------+----------------+
#7

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
#8

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.

#9

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

#10

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

#11

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?

#12

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.

#13

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

#14

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.

image
image1094×228 11.3 KB

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
#15

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
#16

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.

#17

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.

#18

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.

#19

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.

#20

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
#21

The first core modification works (I can now see existing IDs on the list) but it just updates the existing one when adding a new one. However I don’t have the other line in modules/backend/behaviors/relationcontroller/HasPivotMode.php.

OK I’ll wait for your update :slight_smile:

#22

Ok, this has been fixed in v3.6.19. The following has been added to the documentation.

Allowing Duplicate Relations

In some cases you may want to associate the same relationship twice or more, using different pivot data for each attached record. Below is an example that shows a database table structure that has an incrementing primary key on the join table instead of a composed primary key.

Schema::create('role_user', function($table) {
    $table->increments('id');
    $table->integer('user_id')->unsigned();
    $table->integer('role_id')->unsigned();
});

A custom pivotModel must be used for this configuration, and the pivotKey property is set to the incrementing column name (id).

public $belongsToMany = [
    'roles' => [
        \Acme\Blog\Models\Role::class,
        'pivotModel' => \Acme\Blog\Models\UserRolePivot::class,
        'pivotKey' => 'id'
    ]
];

The pivot model definition should set the $incrementing property to true to enable the incrementing primary key.

class UserRolePivot extends \October\Rain\Database\Pivot
{
    public $incrementing = true;
}
2 Likes
#23

Hi, so cool! Thanks. How long till this version is available on update channel?
We just renewed the license that expired months ago.

#24

Hi @daft,
is there a way to upgrade without the UI update channel?

#25

This has been released in v3.6.19 today.

Yes, partners have access to the development repo in GitHub. Send us an email with your GitHub username and we can add you to it.

1 Like