Many_many relationship ORM on custom table field and write, delete

[Bagus]

Silverstripe Version: 4.x

Question:
I’m trying to create a many_many relationship between member and leftmenu table.

LeftMenu.php

use SilverStripe\ORM\DataObject;
use SilverStripe\Security\Member;

class LeftMenu extends DataObject {
    private static $db = [
        'AppID' => 'Int',
        'AppName' => 'Varchar(255)',
        'ServiceUrl' => 'Varchar(255)'
    ];
    private static $many_many = [
        'Members' => Member::class,
    ];
    private static $many_many_extraFields = [
        'Members' => [
            'Rank' => 'Int'
        ]
    ];
}

Would it be possible to create a relationship with another field in member table?
I added a field in the member table:
MyMemberExtension.php

use SilverStripe\Forms\FieldList;
use SilverStripe\Forms\TextField;
use SilverStripe\ORM\DataExtension;

class MyMemberExtension extends DataExtension {
    private static $db = [
        'VsMemberID' => 'Int'
    ];
    static $indexes = array(
        'name' => 'VsMemberIDindex',
        'type' => 'unique',
        'value' => 'VsMemberID'
    );
    public function updateCMSFields(FieldList $fields)
    {
        $fields->push(new TextField('VsMemberID'));
    }
}

something like:

    private static $many_many = [
        'Members' => Member::class.VsMemberID,
    ];

Anyway I’m trying to manipulate the pivot table with:

$leftMenu = LeftMenu::get_by_id($id);
// How do I set the memberID??
$members = $leftMenu->Members();
$members->LeftMenuID = $AppId;
$members->write();
...

What I am trying to acheive is insert, update and delete on the leftmenu_members pivot table.

[Bagus]

Hi
I managed to solve the insert:

$member = Member::get_by_id($memberID);
$leftMenus = LeftMenu::get_by_id($AppId);
$leftMenus->Members()->add($member);

for delete I used DB query:

$query = SQLDelete::create()
->setFrom('"leftmenu_members"')
->setWhere(['"leftmenu_members"."MemberID"' => $member->ID]);
$query->execute();

I would still like to use my custom VsMemberID field in the member table for the relationship.
Is that possible?

[ntd]

I’m failing really hard to understand what you are requesting, but the code you posted looks wrong:

1. you have $many_many but not $belongs_many_many;
2. you added a $db field ending with ID: I would avoid that like the plague;
3. you are manually handling $indexes, and in the wrong way;
4. your deletion code uses hardcoded SQL, discouraged and I think unneeded.

Relations are much easier than that! I would suggest to read (or re-read) the official guide, specifically the provided examples.

[Bagus]

Hi
thank you for your help. I wrongfully understood $many_many and $belongs_many_many…

I added the $belongs_to_many relationship

use SilverStripe\ORM\DataObject;
use SilverStripe\Security\Member;

class LeftMenu extends DataObject {
    private static $db = [
        'AppID' => 'Int',
        'AppName' => 'Varchar(255)',
        'ServiceUrl' => 'Varchar(255)'
    ];

    private static $many_many = [
        'Members' => Member::class,
    ];

    private static $many_many_extraFields = [
        'Members' => [
            'Rank' => 'Int'
        ]
    ];

    private static $belongs_many_many = [
        'LeftMenus' => LeftMenu::class,
    ];
}

And changed the field name

The indexes were just an attempt to add a unique constraint on VsMemberIND field

I need smth like:

ALTER TABLE `members` ADD UNIQUE (`VsMemberIND`);

As for the leftmenu - each member has multiple left menu items available.
I would like to sync (insert, update, delete) the leftmenu_members pivot table.
And I need the constraint on the leftmenu_members table too.

I need smth like:

ALTER TABLE `leftmenu_members` ADD UNIQUE (`LeftMenuID`,`MemberID`);

[Tim]

@Bagus

The indexes were just an attempt to add a unique constraint on VsMemberIND field

Adding indexes to the model is fair enough, but the issue you might find with doing it that way is validation. Adding a unique index at the database level may run into issues in the CMS when you’re editing records.

Instead, you may be better off looking at model validation to ensure that the field is set and unique, so you can display some informative error messages in the CMS.

[Tim]

@Bagus

As for the leftmenu - each member has multiple left menu items available.

Are there a lot of different menu items? If not, then it might be worth looking at permissions / groups to decide which menu options to show/hide. All the controls to manage this are already in the CMS, and it would avoid you having to do the extra database work yourself.

[ntd]

@Bagus

I wrongfully understood $many_many and $belongs_many_many…

This is still wrong: it seems to me you did not read the link I provided.

The changes are cosmetic while the problem is conceptual:

1. $belongs_many_many goes on the other side of the relation
2. I would remove the field ending with ID: this must be a relation
3. indexes are generated automatically when using relations, so no need for explicit $indexes
4. once the relations are properly defined, deleting a member will cascade-delete the linked leftmenu

[Bagus]

Hi,
I’m going to ask the stupid question. Where do I put the $belongs_many_many?

[ntd]

Does this example help you understand how many-many relations are implemented in Silverstripe?

use SilverStripe\ORM\DataObject;

class Team extends DataObject
{
    private static $many_many = [
        "Supporters" => Supporter::class,
    ];

    private static $many_many_extraFields = [
        'Supporters' => [
          'Ranking' => 'Int'
        ]
    ];
}

class Supporter extends DataObject
{
    private static $belongs_many_many = [
        "Supports" => Team::class,
    ];
}

[Bagus]

The $belongs_many_many relation would be set in Member class. As I originally understood. The class is defined in \vendor\silverstripe\framework\src\Security\Member.php which is written during composer update.

Is there an option to add or overwrite this like Wordpress does with child-theme? Just for the analogy.

[ntd]

@Bagus

Is there an option to add or overwrite this like Wordpress does with child-theme? Just for the analogy.

Yes, and you are already doing this with your MyMemberExtension class.

As a side note: $belongs_many_many is not required, it just gives you the $supporter->Supports() method (in the above example) to quickly access all teams supported by a specific supporter. In your case it will give you all LeftMenu linked to a specific Member.

[Bagus]

Just so I understand fully
If I extend a class through DataExtension like in my example
class MyMemberExtension extends DataExtension
I can add/edit - use ORM
If I extend a class from DataObject
class LeftMenu extends DataObject
then I cannot?

I think the relation LeftMenu => Member will come in handy down the line.

[ntd]

No.

Any Silverstripe model has DataObject in its hierarcy. DataExtension provides just a way to extend a model without subclassing it, and this comes in handy in some situation, most notably when the model is already used by some code that you cannot modify (e.g. the Member class).

Again, do yourself a favour and read some tutorial, in this case the introduction to the Silverstripe data model and how to extend DataObject.