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

**URL:** <https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059>\
**Category:** Extras\
**Tags:** formit, migx\
**Created:** [September 20, 2020, 12:46pm UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059 "2020-09-20T12:46:23Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![vibedesign](https://community.modx.com/user_avatar/community.modx.com/vibedesign/32/741_2.png) [@vibedesign](https://community.modx.com/u/vibedesign)\
**Post date:** [September 20, 2020, 12:46pm UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/1 "2020-09-20T12:46:23Z")

</div>

I’m basically trying to do what [this older thread](https://community.modx.com/t/migx-search-form-with-filters-in-frontend/1396) 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:

```auto
[[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](https://community.modx.com/t/getimagelist-select-all-migx-entries-with-same-id-as-current-resource/3035/4). 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?

---

<div class="post-metadata">

**Author:** ![halftrainedharry](https://community.modx.com/user_avatar/community.modx.com/halftrainedharry/32/1063_2.png) [@halftrainedharry](https://community.modx.com/u/halftrainedharry)\
**Post date:** [September 20, 2020, 1:17pm UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/2 "2020-09-20T13:17:12Z")

</div>

> [@vibedesign](#):
>
> Also my `teams` field can contain an array (id values with delimiter)

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.

---

<div class="post-metadata">

**Author:** ![bruno17](https://community.modx.com/user_avatar/community.modx.com/bruno17/32/71_2.png) [@bruno17](https://community.modx.com/u/bruno17)\
**Post date:** [September 20, 2020, 3:13pm UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/3 "2020-09-20T15:13:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![vibedesign](https://community.modx.com/user_avatar/community.modx.com/vibedesign/32/741_2.png) [@vibedesign](https://community.modx.com/u/vibedesign)\
**Post date:** [September 20, 2020, 3:42pm UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/4 "2020-09-20T15:42:41Z")

</div>

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)

```auto
<?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?

---

<div class="post-metadata">

**Author:** ![halftrainedharry](https://community.modx.com/user_avatar/community.modx.com/halftrainedharry/32/1063_2.png) [@halftrainedharry](https://community.modx.com/u/halftrainedharry)\
**Post date:** [September 20, 2020, 7:32pm UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/5 "2020-09-20T19:32:12Z")

</div>

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`:

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

```

Then create 2 new snippets `trainerteam_aftergetfields` and `trainerteam_aftersave`.

**trainerteam\_aftergetfields**

```auto
<?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**

```auto
<?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`.

---

<div class="post-metadata">

**Author:** ![vibedesign](https://community.modx.com/user_avatar/community.modx.com/vibedesign/32/741_2.png) [@vibedesign](https://community.modx.com/u/vibedesign)\
**Post date:** [September 20, 2020, 8:02pm UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/6 "2020-09-20T20:02:52Z")

</div>

@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!

---

<div class="post-metadata">

**Author:** ![bruno17](https://community.modx.com/user_avatar/community.modx.com/bruno17/32/71_2.png) [@bruno17](https://community.modx.com/u/bruno17)\
**Post date:** [September 20, 2020, 8:18pm UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/7 "2020-09-20T20:18:43Z")

</div>

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

> <https://github.com/Bruno17/MIGX/blob/ac79d1dbc15bc067c240aa8a7b8e99428218e294/core/components/migx/model/migx/migx.class.php#L2512>

---

<div class="post-metadata">

**Author:** ![halftrainedharry](https://community.modx.com/user_avatar/community.modx.com/halftrainedharry/32/1063_2.png) [@halftrainedharry](https://community.modx.com/u/halftrainedharry)\
**Post date:** [September 20, 2020, 8:38pm UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/8 "2020-09-20T20:38:18Z")

</div>

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

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”**

```auto
<?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**

```auto
<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.

---

<div class="post-metadata">

**Author:** ![halftrainedharry](https://community.modx.com/user_avatar/community.modx.com/halftrainedharry/32/1063_2.png) [@halftrainedharry](https://community.modx.com/u/halftrainedharry)\
**Post date:** [September 20, 2020, 9:21pm UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/9 "2020-09-20T21:21:05Z")

</div>

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

```auto
<?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);

```

---

<div class="post-metadata">

**Author:** ![vibedesign](https://community.modx.com/user_avatar/community.modx.com/vibedesign/32/741_2.png) [@vibedesign](https://community.modx.com/u/vibedesign)\
**Post date:** [September 21, 2020, 9:55am UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/10 "2020-09-21T09:55:49Z")

</div>

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:

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

```

Therefor I adjusted the `migxLoopCollection` call to:

```auto
[[!+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.

---

<div class="post-metadata">

**Author:** ![halftrainedharry](https://community.modx.com/user_avatar/community.modx.com/halftrainedharry/32/1063_2.png) [@halftrainedharry](https://community.modx.com/u/halftrainedharry)\
**Post date:** [September 21, 2020, 11:09am UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/11 "2020-09-21T11:09:18Z")

</div>

> [@vibedesign](#):
>
> Now to make it perfect, I would like the `select` field to keep the currently displayed option.

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.

```auto
[[!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**

```auto
<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`:

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

```

---

<div class="post-metadata">

**Author:** ![vibedesign](https://community.modx.com/user_avatar/community.modx.com/vibedesign/32/741_2.png) [@vibedesign](https://community.modx.com/u/vibedesign)\
**Post date:** [September 21, 2020, 11:36am UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/12 "2020-09-21T11:36:27Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![system](https://community.modx.com/user_avatar/community.modx.com/system/32/2_2.png) [@system](https://community.modx.com/u/system)\
**Post date:** [September 23, 2020, 11:36am UTC](https://community.modx.com/t/migxloopcollection-filter-results-by-single-select-dropdown-in-frontend-also-how-to-setup-a-man2many-custom-table/3059/13 "2020-09-23T11:36:30Z")

</div>

Closed on September 23, 2020, 11:36am UTC

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