Best way to display a list, Many-To-Many

Need Help
#1

Hi,

How can I display a list in my controller ‘Contacts’ for a Many-To-Many table ?

user_id | company_id
1 1
1 2

What I need :

User First Name | User Last Name | Company Name | Company Postal Code
Darrell | Sparks | Car Progressive LTD | K8C 9V0
Darrell | Sparks | Collision Xpert | L1K 2X3

Do I need to :

  1. add a PivotModel to the relationship (users belongs to many companies)
  2. add a RelationController behavior / relation_config.yaml in my Contacts controller
  3. display the relation instead of <?= $this->listRender() ?>

Not sure if that’s the easiest/smartest way to go.

I also thought to override listExtendRecords to purge the collection and create a new one from scratch (which is probably not the best)…

Thank you.

#2

Ok easier then what I stated above. Here is what I did in case it helps someone later on.

I simply created a new model ‘Contact’ with the pivot table.

class Contact extends Model
{
    use \October\Rain\Database\Traits\Validation;

    public $table = 'author_plugin_users_companies';

From there, I added relations :

public $belongsTo = [
        'user' => \RainLab\User\Models\User::class,
        'company' => \Author\Plugin\Models\Company::class
    ];

And I can use in my columns.yaml :

columns:

    name:
        label: rainlab.user::lang.user.name
        relation: user
        select: name
        searchable: true

    surname:
        label: rainlab.user::lang.user.surname
        relation: user
        select: surname
        searchable: true

    company_name:
        label: ...
        searchable: ...
        relation: company
        select: name

    company_postal_code:
        label: ...
        searchable: ...
        relation: company
        select: postal_code

Have a good day/night lol

1 Like
#3

Update 2022-11-09 :

You will encounter an issue - the checkboxes won’t work. The table ‘author_plugin_users_companies’ don’t contain an ID field but a composite key. So if you try to click there, it won’t work as expected (there is no ID for each row). If you inspect the HTML, you will see there is no value for the differents inputs / labels.

image

I tried to implement this solution Creating model without 'id' and with composite primary keys - October CMS but it was causing me issues with the relations.

Solution :

Override the listview (Lists - October CMS - 2.x)

  1. Add customViewPath in plugins/authorname/plugin/controllers/contacts/config_list.yaml :

...
# Displays the list column set up button
showSetup: true

# Toolbar widget configuration
toolbar:

    # Partial for toolbar buttons
    buttons: list_toolbar

    # Search widget configuration
    search:
        prompt: backend::lang.list.search_prompt

customViewPath: $/author/plugin/controllers/contacts/list

  1. create a folder ‘list’ → plugins/authorname/plugin/controllers/contacts/list

  2. copy this file :
    modules\backend\widgets\lists\partials_list_body_checkbox.php

  3. paste it here :
    plugins/authorname/plugin/controllers/contacts/list/_list_body_checkbox.php

  4. modify this file plugins/authorname/plugin/controllers/contacts/list/_list_body_checkbox.php

<td class="list-checkbox nolink">
    <div class="checkbox custom-checkbox nolabel">
        <input
            type="checkbox"
            name="checked[]"
            id="<?= $this->getId('checkbox-' . $record->user_id . '_' . $record->company_id) ?>"
            value="<?= $record->user_id . '_' . $record->company_id ?>"
            <?= $this->isRowChecked($record) ? 'checked' : '' ?>
            autocomplete="off" />
        <label class="storm-icon-pseudo" for="<?= $this->getId('checkbox-' . $record->user_id . '_' . $record->company_id) ?>"><?= e(trans('backend::lang.list.check')) ?></label>
    </div>
</td>

The idea is to change the $record->getKey() with $record->your_first_key . ‘_’ . $record->your_second_key.

Now, if you refresh the list, you will be able to select the records you want because they have unique IDs and more importantly, be able to export (and you will know which record).

If you inspect the checkboxes :

image

1 Like