migxLoopCollection filter results by single-select dropdown in frontend (also how to setup a man2many custom table)

I’m basically trying to do what this older thread is asking. I have a single-select dropdown in the frontend and depending on which entry is selected I want to display the elements of a MIGXdb custom table which contain the selected value.

In the old thread it looks like the desired result got achieved by using Formit and implementing migxLoopCollection as a hook? How do I specifiy the migxLoopCollection-parameters in a Formit-call? Here is how my general migxLoopCollection call should look like:

[[migxLoopCollection?
  &packageName=`trainerteam`
  &classname=`Trainer`
  &tpl=`trainer`
  &where=`{"teams:find_in_set":"[[+selectedValueHere]]"}`]]

Also my teams field can contain an array (id values with delimiter) so I try to check for the selected value by using field_in_set like I was suggested while using getImageList in this thread. It seems like migxLoopCollection doesn’t work with this, though. Is there a different way to achieve this within the call or do I have to either create a custom snippet or do some outputfilter magic for this?

This is not a good idea. For a clean solution you should create a second data base table. A joining table with the fields trainer_id and team_id to handle the many-to-many relationship between trainers and teams.

1 Like

would you like to post your XPDO schema, which you have now?

I was just about to try to create a team table and connect it with the trainer one, so here is the schema for that. (Thanks to @halftrainedharry for some guidance)

<?xml version="1.0" encoding="UTF-8"?>
<model package="trainerteam" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
    <object class="Trainer" table="trainerteam" extends="xPDOSimpleObject" >
        <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" />
        <field key="email" dbtype="varchar" precision="255" phptype="string" null="false" />
        <field key="text" dbtype="mediumtext" phptype="string" null="false" />
        <field key="bild" dbtype="varchar" precision="255" phptype="string" null="false" />
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="1" />
        <field key="createdby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true" />
        <field key="editedby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
        <field key="editedon" dbtype="datetime" phptype="datetime" null="true" />
        <field key="deletedon" dbtype="datetime" phptype="datetime" null="true" />
        <field key="deletedby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
        <field key="publishedon" dbtype="datetime" phptype="datetime" null="true" />
        <field key="publishedby" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
        <composite alias="TrainerZuMannschaften" class="TrainerMannschaften" local="id" foreign="trainer_id" cardinality="many" owner="local" />
    </object>

    <object class="TrainerMannschaften" table="trainer_mannschaften" extends="xPDOSimpleObject">
      <field key="mannschaft_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />
      <field key="trainer_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />

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

      <aggregate alias="Trainer" class="Trainer" local="trainer_id" foreign="id" cardinality="one" owner="foreign" />
      <aggregate alias="Mannschaft" class="modResource" local="mannschaft_id" foreign="id" cardinality="one" owner="foreign" />
    </object>
</model>

I was just about to parse it and see where to go from there. Would you say this is the correct schema?

Now that you created a second database table you will have to change your MIGX-configuration to still be able to manage your trainers in the backend.

Add this code in the tab MIGXdb-Settings in the field Hook Snippets:

{"aftergetfields":"trainerteam_aftergetfields","aftersave":"trainerteam_aftersave"}

Then create 2 new snippets trainerteam_aftergetfields and trainerteam_aftersave.

trainerteam_aftergetfields

<?php
$base_path = $modx->getOption('core_path').'components/trainerteam/';
$modx->addPackage('trainerteam', $base_path.'model/');

if ($object) {
    $record_fields = $object->get('record_fields');
    $trainer_id = $record_fields['object_id'];
    
    $team_ids = array();
    $result = $modx->getCollection('TrainerMannschaften', array('trainer_id' => $trainer_id));
    foreach($result as $res){
        $team_ids[] = $res->get('mannschaft_id');
    }
    
    $record_fields['mannschaften'] = implode("||",$team_ids);
    $object->set('record_fields',$record_fields);
}

return '';

This snippets reads all the team-ids for a specific trainer from the table trainer_mannschaften, combines them into a string (delimited with ||) and stores them in a field called mannschaften so that it can be used with a listbox-multiple dropdown.

trainerteam_aftersave

<?php
$base_path = $modx->getOption('core_path').'components/trainerteam/';
$modx->addPackage('trainerteam', $base_path.'model/');

$object = & $modx->getOption('object',$scriptProperties,null);
$trainer_id = $object->get('id');

//query everything that is already saved in the database
$teams_old = array();
$result= $modx->getCollection('TrainerMannschaften', array('trainer_id' => $trainer_id));
foreach($result as $res){
    $teams_old[] = $res->get('mannschaft_id');
}

$str_teams = $object->get('mannschaften');
$teams_new = explode('||',$str_teams);
foreach($teams_new as $team_id){
    if ($team_id === ''){
        //no teams selected for this trainer
        break;
    }
    
    $team_id = (int)$team_id;
    $pos = array_search($team_id, $teams_old);
    if (false !== $pos){
        //team-id is already saved in the database
        unset($teams_old[$pos]);
    } else {
        //add new team-id
        $new_t = $modx->newObject('TrainerMannschaften');
        $new_t->set('mannschaft_id',$team_id);
        $new_t->set('trainer_id',$trainer_id);
        $new_t->save();
    }
}

//delete remaining team-ids from the database
foreach($teams_old as $team_id){
    $entry = $modx->getObject('TrainerMannschaften',array('trainer_id' => $trainer_id, 'mannschaft_id' => $team_id));
	$entry->remove();
}

This snippets breaks the string (delimited with ||) with all the team-ids into the individual ids and makes sure, they match the data in the table trainer_mannschaften.

2 Likes

@halftrainedharry Mate, this is incredible, thank you so much!

Looks like it’s working already as well! I have the new table trainer_mannschaften where it now lists all the trainer_ids asigned to the team_ids. I will continue with this further tomorrow and report back on my progress!

the aftersave snippet could maybe also be simplyfied by using this helper-method (handleRelatedLinks)

1 Like

So now that your backend is working, it is time to answer your initial question. :wink:

Even though you could use FormIt for your dropdown in the frontend, it is not necessary. You can read the submitted value yourself in a snippet.

Snippet "getTeamID"

<?php
if (isset($_POST['team'])) {
    $team_id = intval($_POST['team']);
    $modx->setPlaceholder('team_id',$team_id);
}

This snippet reads the POST-parameter team and saves it in a placeholder team_id.

Template

<form method="post" action="[[~[[*id]]]]" id="team_form">
    <select name="team" id="team">
        <option value="1">Team 1</option>
        <option value="2">Team 2</option>
    </select>
    <input type="submit" value="Load data">
</form>

[[!getTeamID]]

[[!+team_id:notempty=`[[!migxLoopCollection? &packageName=`trainerteam` &classname=`Trainer` &tpl=`trainer` &where=`{"TrainerZuMannschaften.mannschaft_id":[[!+team_id]]}` &joins=`[{"alias":"TrainerZuMannschaften"}]`]]`]]

First the snippet getTeamID gets called to set the placeholder. Then migxLoopCollection is executed, if the placeholder is not empty. The call now contains a &joins-property to connect your 2 database tables.

The select in the form should obviously be filled dynamically and you probably want to use some javascript-code to react to a change-event in the select and reload the page without clicking the button.

Good to know. This simplifies the snippet a lot.
I assume, this is how it would be used in this case:

<?php
$base_path = $modx->getOption('core_path').'components/trainerteam/';
$modx->addPackage('trainerteam', $base_path.'model/');

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

$config = array();
$config['link_classname'] = 'TrainerMannschaften';
$config['link_alias'] = 'TrainerZuMannschaften';
$config['postfield'] = 'mannschaften';
$config['id_field'] = 'trainer_id';
$config['link_field'] = 'mannschaft_id';

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

1 Like

Thank you so much! It works great, I just did a few adjustments. I’m populating my select options with pdoResources and added on the top an option to show all trainers with an empty value:

<option value="">Show all</option>

Therefor I adjusted the migxLoopCollection call to:

[[!+team_id:isnot=``:then=`
    [[!migxLoopCollection? &packageName=`trainerteam` &classname=`Trainer` &tpl=`trainer` &where=`{"TrainerZuMannschaften.mannschaft_id":[[!+team_id]]}` &joins=`[{"alias":"TrainerZuMannschaften"}]`]]
  `:else=`
    [[!migxLoopCollection? &packageName=`trainerteam` &classname=`Trainer` &tpl=`trainer`]]
`]]

I also added onchange="this.form.submit()" to the select so the user doesn’t have to click another button.

Now to make it perfect, I would like the select field to keep the currently displayed option. Right now it obviously jumps back to “Show all” when the page reloads.

To achieve this you have to compare the value in the <option> to the current value of [[!+team_id]].

Move the call to [[!getTeamID]] above the form.

[[!getTeamID]]
<form method="post" action="[[~[[*id]]]]" id="team_form">
    <select name="team" id="team">
        [[!pdoResources? ... &tpl=`tplTeamOption`]]
    </select>
    <input type="submit" value="Load data">
</form>

Then append the template you use for the pdoResources-call.

tplTeamOption

<option value="[[+id]]" [[!+team_id:eq=`[[+id]]`:then=`selected="selected"`]]>[[+pagetitle]]</option>

If you have the extra “FormIt” installed, you can also use the output modifier FormItIsSelected:

<option value="[[+id]]" [[!+team_id:FormItIsSelected=`[[+id]]`]]>[[+pagetitle]]</option>
1 Like

That works great! I just had to define a value for the “Show all” option (set it to 999) and adjust the migxLoopCollection call so it checks against :isnot=`999`

Thank you again for all the support!

1 Like

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.