MIGXdb create custom package with many to many relationship

Support Extras
1

I’m (still) struggeling a bit to create a correct schema for my custom package using migxdb. My goal is to show references from a client in certain categories. One reference can have multiple categories assigned and also one category can have multiple references linked, therefore my conclusion was a many to many setup:

show schema 
<?xml version="1.0" encoding="UTF-8"?>

<model package="Referenzen\Model\" baseClass="xPDO\Om\xPDOObject" platform="mysql" defaultEngine="InnoDB" phpdoc-package="" phpdoc-subpackage="" version="3.0">

  <object class="Kategorie" table="referenzen_kategorien" extends="xPDO\Om\xPDOSimpleObject">
    <field key="name" dbtype="varchar" phptype="string" precision="100" null="false" default="" index="index"/>
    <field key="description" dbtype="text" phptype="string" null="false" default="" />
    <field key="image" dbtype="text" phptype="string" null="false" default="" />

    <composite alias="Referenzen" class="Referenzen\Model\KategorieReferenz" local="id" foreign="kategorie_id" cardinality="many" owner="local" />

    <index alias="name" name="name" primary="false" unique="false" type="BTREE">
      <column key="name" length="" collation="A" null="false" />
    </index>
  </object>

  <object class="Referenz" table="referenzen" extends="xPDO\Om\xPDOSimpleObject">
    <field key="title" dbtype="varchar" phptype="string" precision="190" null="false" default="" index="index" />
    <field key="description" dbtype="text" phptype="string" null="false" default="" />
    <field key="images" dbtype="text" phptype="string" null="false" default="" />

    <composite alias="KategorienZuReferenzen" class="Referenzen\Model\KategorieReferenz" local="id" foreign="referenz_id" cardinality="many" owner="local" />

    <index alias="title" name="title" primary="false" unique="false" type="BTREE">
      <column key="title" length="" collation="A" null="false" />
    </index>
  </object>

  <object class="KategorieReferenz" table="referenzen_kategorie_referenz" extends="xPDOSimpleObject">
    <field key="referenz_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />
    <field key="kategorie_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />

    <aggregate alias="Referenz" class="Referenzen\Model\Referenz" local="referenz_id" foreign="id" cardinality="one" owner="foreign" />
    <aggregate alias="Kategorie" class="Referenzen\Model\Kategorie" local="kategorie_id" foreign="id" cardinality="one" owner="foreign" />

    <index alias="referenz_id" name="referenz_id" primary="false" unique="false" type="BTREE">
      <column key="referenz_id" length="" collation="A" null="false" />
    </index>
    <index alias="kategorie_id" name="kategorie_id" primary="false" unique="false" type="BTREE">
      <column key="kategorie_id" length="" collation="A" null="false" />
    </index>
  </object>

</model>

I’ve read through these docs which helped me creating the above schema, but now I’m stuck when I actually try to link the two parts together in the CMP.

Do I have to create an additional field under references where I can store the IDs of the selected categories? To be clear, I know how to create a dropdown of the values from the category table using a @CHUNK input with a migxLoopCollection call.

What I don’t yet understand is how this works in the background as I would assume my connecting table from the “KategorieReferenz” class should be populated somehow.

MODX 3.0.5
MIGX 3.0.2-beta1

2

A common way to do this is to use the aftergetfields and aftersave hooks.

You already did this once. See this thread.

I also created a Youtube video that explains the process.

It’s for MODX 2.x (but it should still mostly work the same.)

3

Yes, I saw this as well but couldn’t really make sense of it anymore. :sweat_smile:

Thank you, I think I understand the process now better, also thanks to your great video!

Do I have to adjust any of the pathnames according to the new MODX3 update?
I’ve adjusted your snippets now to this setup, but I can’t save any items in the CMP.

Here’s the updated snippets:

// referenzen_aftergetfields

$base_path = $modx->getOption('core_path').'components/referenzen/';
$modx->addPackage('referenzen', $base_path.'model/');

if ($object) {
    $record_fields = $object->get('record_fields');
    $referenz_id = $record_fields['object_id'];
    
    $category_ids = array();
    $result = $modx->getCollection('KategorieReferenz', array('referenz_id' => $referenz_id));
    foreach($result as $res){
        $category_ids[] = $res->get('category_id');
    }
    
    $record_fields['kategorien'] = implode("||",$category_ids);
    $object->set('record_fields',$record_fields);
}

return '';

// referenzen_aftersave

$base_path = $modx->getOption('core_path').'components/referenzen/';
$modx->addPackage('referenzen', $base_path.'model/');

$object = & $modx->getOption('object',$scriptProperties,null);
$postvalues = $modx->getOption('postvalues',$scriptProperties,array());

$config = array();
$config['link_classname'] = 'KategorieReferenz';
$config['link_alias'] = 'Kategorien';
$config['postfield'] = 'kategorien';
$config['id_field'] = 'referenz_id';
$config['link_field'] = 'kategorie_id';

$modx->migx->handleRelatedLinks($object, $postvalues, $config);

Edit: I can create (open) the CMP entry now, but I can’t save it. If it’s not a new MODX3 pathing issue, I guess I mixed up some fields in the aftersave snippet?

4

In MODX 3, the package should already get added in the bootstrap.php file in core\components\<package_name>. So this isn’t necessary anymore.

In MODX 3, you have to used the full class name (with the namespace) instead of just KategorieReferenz → Referenzen\Model\KategorieReferenz

5

Thank you, after sorting out some naming issues, I got it to work now!

Updated, working solution

Schema

<?xml version="1.0" encoding="UTF-8"?>

<model package="Referenzen\Model\" baseClass="xPDO\Om\xPDOObject" platform="mysql" defaultEngine="InnoDB" phpdoc-package="" phpdoc-subpackage="" version="3.0">

  <object class="Kategorie" table="referenzen_kategorien" extends="xPDO\Om\xPDOSimpleObject">
    <field key="name" dbtype="varchar" phptype="string" precision="100" null="false" default="" index="index"/>
    <field key="description" dbtype="text" phptype="string" null="false" default="" />
    <field key="image" dbtype="text" phptype="string" null="false" default="" />

    <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" index="index" />
    <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />

    <composite alias="KategorienZuReferenzen" class="Referenzen\Model\KategorieReferenz" local="id" foreign="kategorie_id" cardinality="many" owner="local" />

    <index alias="name" name="name" primary="false" unique="false" type="BTREE">
      <column key="name" length="" collation="A" null="false" />
    </index>
    <index alias="pos" name="pos" primary="false" unique="false" type="BTREE">
      <column key="pos" length="" collation="A" null="false" />
    </index>
  </object>

  <object class="Referenz" table="referenzen" extends="xPDO\Om\xPDOSimpleObject">
    <field key="title" dbtype="varchar" phptype="string" precision="190" null="false" default="" index="index" />
    <field key="description" dbtype="text" phptype="string" null="false" default="" />
    <field key="images" dbtype="text" phptype="string" null="false" default="" />

    <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" index="index" />
    <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />

    <composite alias="KategorienZuReferenzen" class="Referenzen\Model\KategorieReferenz" local="id" foreign="referenz_id" cardinality="many" owner="local" />

    <index alias="title" name="title" primary="false" unique="false" type="BTREE">
      <column key="title" length="" collation="A" null="false" />
    </index>
    <index alias="pos" name="pos" primary="false" unique="false" type="BTREE">
      <column key="pos" length="" collation="A" null="false" />
    </index>
  </object>

  <object class="KategorieReferenz" table="referenzen_kategorie_referenz" extends="xPDOSimpleObject">
    <field key="referenz_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />
    <field key="kategorie_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />

    <aggregate alias="Referenz" class="Referenzen\Model\Referenz" local="referenz_id" foreign="id" cardinality="one" owner="foreign" />
    <aggregate alias="Kategorie" class="Referenzen\Model\Kategorie" local="kategorie_id" foreign="id" cardinality="one" owner="foreign" />

    <index alias="referenz_id" name="referenz_id" primary="false" unique="false" type="BTREE">
      <column key="referenz_id" length="" collation="A" null="false" />
    </index>
    <index alias="kategorie_id" name="kategorie_id" primary="false" unique="false" type="BTREE">
      <column key="kategorie_id" length="" collation="A" null="false" />
    </index>
  </object>

</model>

Snippets

// referenzen_aftergetfields

if ($object) {
  $record_fields = $object->get('record_fields');
  $referenz_id = $record_fields['object_id'];
  
  $kategorie_ids = array();
  $result = $modx->getCollection('Referenzen\Model\KategorieReferenz', array('referenz_id' => $referenz_id));
  foreach($result as $res){
    $kategorie_ids[] = $res->get('kategorie_id');
  }
  
  $record_fields['kategorien'] = implode("||",$kategorie_ids);
  $object->set('record_fields',$record_fields);
}

return '';

// referenzen_aftersave

$object = & $modx->getOption('object',$scriptProperties,null);
$postvalues = $modx->getOption('postvalues',$scriptProperties,array());

$config = array();
$config['link_classname'] = 'Referenzen\Model\KategorieReferenz';
$config['link_alias'] = 'KategorienZuReferenzen';
$config['postfield'] = 'kategorien';
$config['id_field'] = 'referenz_id';
$config['link_field'] = 'kategorie_id';

$modx->migx->handleRelatedLinks($object, $postvalues, $config);

One additional question:

Is there an easy way to show the selected category names in the columns section of the CMP?

6

You can use a this.renderChunk renderer for the column.

There is an example in the Youtube video I linked above (around the 10 minutes mark).

1 Like
7

I quickly want to chime in here again as I noticed an issue while using the renderChunk together with another column which has the this.renderClickCrossTick renderer for the published field.

When I click the crosstick to publish or unpublish, my set categories get deleted. I assume this has something to do with the snippets that might not get fired doing this? Is there an easy fix for this? If not I could go back to just using the this.renderCrossTick renderer I guess.

8

Is this in a MIGXdb CMP or in a MIGX TV?

In a CMP, when you click the field with the this.renderClickCrossTick renderer, an AJAX request (with action = "mgr/migxdb/update") runs and then all the data in the grid is reloaded (AJAX request with action = "mgr/migxdb/getlist") the same way as when you click the reload button (↻) under the grid.
All the renderChunk chunks should then be parsed again in this getlist processor.

9

This is in a migxdb CMP, not a TV.

10

Not sure what the problem is then.

If you can provide your MIGX configuration, I can take a look at it.

11

Sure!

Config 
{
  "formtabs":[
    {
      "MIGX_id":2,
      "caption":"Referenz",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":6,
          "field":"id",
          "caption":"ID",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"none",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        },
        {
          "MIGX_id":7,
          "field":"title",
          "caption":"Titel",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":2
        },
        {
          "MIGX_id":13,
          "field":"longtitle",
          "caption":"Langer Titel",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":3
        },
        {
          "MIGX_id":8,
          "field":"description",
          "caption":"Beschreibung",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"textarea",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":4
        },
        {
          "MIGX_id":12,
          "field":"kategorien",
          "caption":"Kategorie",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"listbox-multiple",
          "validation":"",
          "configs":{
            "typeAhead":true,
            "typeAheadDelay":250
          },
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"@SELECT `name`, `id` FROM `mx_client_kategorien`",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":5
        },
        {
          "MIGX_id":9,
          "field":"image",
          "caption":"Bild",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"imageplus",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":6
        },
        {
          "MIGX_id":10,
          "field":"pos",
          "caption":"Position",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"none",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":7
        },
        {
          "MIGX_id":11,
          "field":"published",
          "caption":"Aktiv",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"checkbox",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"Ja==1",
          "default":"0",
          "useDefaultIfEmpty":"0",
          "pos":8
        }
      ],
      "pos":1
    }
  ],
  "contextmenus":"update||duplicate||publish||unpublish||remove",
  "actionbuttons":"addItem",
  "columnbuttons":"publish||unpublish||remove",
  "filters":"",
  "extended":{
    "migx_add":"Referenz hinzuf\u00fcgen",
    "disable_add_item":"",
    "add_items_directly":"",
    "formcaption":"Neue Referenz",
    "update_win_title":"Referenzen",
    "win_id":"referenzen",
    "maxRecords":"",
    "addNewItemAt":"bottom",
    "media_source_id":"",
    "multiple_formtabs":"",
    "multiple_formtabs_label":"",
    "multiple_formtabs_field":"",
    "multiple_formtabs_optionstext":"",
    "multiple_formtabs_optionsvalue":"",
    "actionbuttonsperrow":4,
    "winbuttonslist":"",
    "extrahandlers":"",
    "filtersperrow":4,
    "packageName":"Client\\Model\\","classname":"Client\\Model\\clientReferenz","task":"","getlistsort":"pos","getlistsortdir":"ASC","sortconfig":"","gridpagesize":25,"use_custom_prefix":"0","prefix":"","grid":"dragdrop","gridload_mode":1,"check_resid":1,"check_resid_TV":"","join_alias":"","has_jointable":"yes","getlistwhere":"","joins":"","hooksnippets":{"aftergetfields":"referenzen_aftergetfields","aftersave":"referenzen_aftersave"},"cmpmaincaption":"Architekturb\u00fcro Ro\u00dfbach","cmptabcaption":"Referenzen","cmptabdescription":"Referenzen","cmptabcontroller":"","winbuttons":"","onsubmitsuccess":"","submitparams":""},"permissions":{"apiaccess":"","view":"","list":"","save":"","create":"","remove":"","delete":"","publish":"","unpublish":"","viewdeleted":"","viewunpublished":""},"fieldpermissions":"","columns":[{"MIGX_id":8,"header":"ID","dataIndex":"id","width":10,"sortable":true,"show_in_grid":1,"customrenderer":"","renderer":"","clickaction":"","selectorconfig":"","renderchunktpl":"","renderoptions":"","editor":""},{"MIGX_id":9,"header":"Position","dataIndex":"pos","width":"","sortable":true,"show_in_grid":"0","customrenderer":"","renderer":"","clickaction":"","selectorconfig":"","renderchunktpl":"","renderoptions":"","editor":""},{"MIGX_id":2,"header":"Titel","dataIndex":"title","width":"","sortable":true,"show_in_grid":1,"customrenderer":"","renderer":"","clickaction":"","selectorconfig":"","renderchunktpl":"","renderoptions":"","editor":""},{"MIGX_id":11,"header":"Langer Titel","dataIndex":"longtitle","width":"","sortable":true,"show_in_grid":1,"customrenderer":"","renderer":"","clickaction":"","selectorconfig":"","renderchunktpl":"","renderoptions":"","editor":""},{"MIGX_id":6,"header":"Kategorie","dataIndex":"kategorien","width":"","sortable":"false","show_in_grid":1,"customrenderer":"","renderer":"this.renderChunk","clickaction":"","selectorconfig":"","renderchunktpl":"[
      [
        migxLoopCollection?\n  &packageName=`Client\\Model\\`\n  &classname=`Client\\Model\\clientKategorie`\n  &joins=`[
          {
            \"alias\": \"ReferenzKategorien\"
          }
        ]`\n  &where=`{
          \"`ReferenzKategorien`.`referenz_id`\": [
            [
              +id
            ]
          ]
        }`\n  &tpl=`@CODE: {
          {
            +name
          }
        }`\n  &outputSeparator=`,
         `\n
      ]
    ]","renderoptions":"","editor":""},{"MIGX_id":5,"header":"Aktiv","dataIndex":"published","width":20,"sortable":true,"show_in_grid":1,"customrenderer":"","renderer":"this.renderCrossTick","clickaction":"","selectorconfig":"","renderchunktpl":"","renderoptions":"","editor":""}],"category":""}
Schema 
<?xml version="1.0" encoding="UTF-8"?>

<model package="Client\Model\" baseClass="xPDO\Om\xPDOObject" platform="mysql" defaultEngine="InnoDB" phpdoc-package="" phpdoc-subpackage="" version="3.0">

  <!-- REFERENZ -->
  <object class="clientReferenz" table="client_referenzen" extends="xPDO\Om\xPDOSimpleObject">
    <field key="title" dbtype="varchar" phptype="string" precision="190" null="false" default="" index="index" />
    <field key="longtitle" dbtype="varchar" phptype="string" precision="190" null="false" default="" />
    <field key="description" dbtype="text" phptype="string" null="false" default="" />
    <field key="image" dbtype="text" phptype="string" null="false" default="" />
    <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" index="index" />
    <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />

    <composite alias="ReferenzKategorien" class="Client\Model\clientReferenzKategorie" local="id" foreign="referenz_id" cardinality="many" owner="local" />

    <index alias="title" name="title" primary="false" unique="false" type="BTREE">
      <column key="title" length="" collation="A" null="false" />
    </index>
    <index alias="pos" name="pos" primary="false" unique="false" type="BTREE">
      <column key="pos" length="" collation="A" null="false" />
    </index>
  </object>

  <!-- KATEGORIE -->
  <object class="clientKategorie" table="client_kategorien" extends="xPDO\Om\xPDOSimpleObject">
    <field key="name" dbtype="varchar" phptype="string" precision="100" null="false" default="" index="index"/>
    <field key="description" dbtype="text" phptype="string" null="false" default="" />
    <field key="image" dbtype="text" phptype="string" null="false" default="" />
    <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" index="index" />
    <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />

    <composite alias="ReferenzKategorien" class="Client\Model\clientReferenzKategorie" local="id" foreign="kategorie_id" cardinality="many" owner="local" />

    <index alias="name" name="name" primary="false" unique="false" type="BTREE">
      <column key="name" length="" collation="A" null="false" />
    </index>
    <index alias="pos" name="pos" primary="false" unique="false" type="BTREE">
      <column key="pos" length="" collation="A" null="false" />
    </index>
  </object>

  <!-- KATEGORIE-REFERENZ -->
  <object class="clientReferenzKategorie" table="client_referenzkategorie" extends="xPDOSimpleObject">
    <field key="referenz_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
    <field key="kategorie_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />

    <aggregate alias="Referenz" class="Client\Model\clientReferenz" local="referenz_id" foreign="id" cardinality="one" owner="foreign" />
    <aggregate alias="Kategorie" class="Client\Model\clientKategorie" local="kategorie_id" foreign="id" cardinality="one" owner="foreign" />

    <index alias="referenz_id" name="referenz_id" primary="false" unique="false" type="BTREE">
      <column key="referenz_id" length="" collation="A" null="false" />
    </index>
    <index alias="kategorie_id" name="kategorie_id" primary="false" unique="false" type="BTREE">
      <column key="kategorie_id" length="" collation="A" null="false" />
    </index>
  </object>

</model>
12

Ok, I found the issue.

The problem is that the aftersave hook snippet is also executed when the item is published/unpublished. But because there is no data for the field “kategorien” available on these events, the current code assumes that “kategorien” is just empty and deletes all existing database entries.

To fix it, you have to adjust the aftersave hook snippet.
Here is a possible solution, that checks if “kategorien” exists, before calling $modx->migx->handleRelatedLinks().

<?php
// referenzen_aftersave

$object = & $modx->getOption('object',$scriptProperties,null);
$postvalues = $modx->getOption('postvalues',$scriptProperties,array());

$config = array();
$config['link_classname'] = 'Client\Model\clientReferenzKategorie';
$config['link_alias'] = 'ReferenzKategorien';
$config['postfield'] = 'kategorien';
$config['id_field'] = 'referenz_id';
$config['link_field'] = 'kategorie_id';

// NEW CODE | Check if there is any data for the field "kategorien" available
if (!isset($postvalues[$config['postfield']])){
    return;
}

$modx->migx->handleRelatedLinks($object, $postvalues, $config);

Other minor issues:

  • Under “MIGXdb-Settings” you only need to fill in the field “Package” if you have custom processors or renderers in files. In that case use client instead of Client\Model\ as the value. Otherwise you can leave the field empty.
  • When calling migxLoopCollection in the renderChunk renderer, you don’t need the property &packageName.
1 Like
13

Thank you very much for the research and the additional notes on my config! Works great now!

14

This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.