MIGXdb combobox Db-Filters for joined table

Development
1

I have a MIGXdb CMP with a grid of Events and am attempting to add a combobox Db-Filter to show Events belonging to a specific Family. The main Events table stores the Family id and has an alias to a second table which stores the Family name.

The commbobox lists the Family names however selecting one does not filter the Events grid. I can see a network request to connector.php with payload getFamilyName: Concert which I think is correct - I expected this to match the joined field Family_name.

I tried setting getlist-where: to getlist-where: {"Family_name":"[[+getFamilyName]]"} and getlist-where: {"name":"[[+getFamilyName]]"}, also getlist-where: {"Family":"[[+getFamilyName]]"} (because Family is the Header of the field Family_name in the grid Columns) but none is correct.

There are no errors in the MODX Error Log.

Can anyone help me with the correct Db-Filters configuration please?

My Db-Filters configuration is

filter Name: getFamilyName
Label: Family Name
Empty Text: --filter by name–
Filter Type: combobox
getlist-where: {“Family_name”:“[[+getFamilyName]]”}
getcombo processor: getcombo
getcombo textfield: name
getcombo classname (optional): VenueEvent
getcombo packageName (optional): venue
getcombo joins (optional): [{“alias”:“Family”}]

Schema is below (I removed several Objects to save space)

<?xml version="1.0" encoding="UTF-8"?>
<model package="venue" baseClass="xPDOObject" platform="mysql" defaultEngine="INNODB" version="1.1">

    <object class="VenueFamily" table="venue__family" extends="xPDOSimpleObject">
        <field key="name" dbtype="varchar" precision="100" phptype="string" null="false" default="" />

        <!-- used by MIGX -->        
        <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="0" />
        <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" />

        <!-- good practice for tracking -->       
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        
        <!-- track createdby and editedby -->
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign" />
        <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign" />

        <composite alias="Parents" class="VenueParent" local="id" foreign="family" cardinality="many" owner="local" />
        <composite alias="Experiences" class="VenueExperience" local="id" foreign="family" cardinality="many" owner="local" />
        <composite alias="Events" class="VenueEvent" local="id" foreign="family" cardinality="many" owner="local" />
    </object>

    <object class="VenueParent" table="venue__parent" extends="xPDOSimpleObject">
        <field key="family" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="name" dbtype="varchar" precision="100" phptype="string" null="false" default="" />

        <!-- used by MIGX -->        
        <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="0" />
        <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" />

        <!-- good practice for tracking -->       
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="editedon"  dbtype="datetime" phptype="datetime" null="true"/>
        <field key="editedby"  dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />


        <index alias="family" name="family" primary="false" unique="false" type="BTREE">
            <column key="family" length="" collation="A" null="false" />
        </index>
        <index alias="name" name="name" primary="false" unique="false" type="BTREE">
            <column key="name" length="" collation="A" null="false" />
        </index>
        
        <!-- track createdby and editedby -->
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>

        <aggregate alias="Family" class="VenueFamily" local="family" foreign="id" cardinality="one" owner="foreign"/>
        <composite alias="Event" class="VenueEvent" local="id" foreign="parent" cardinality="many" owner="local" />
    </object>

    <object class="VenueEvent" table="venue__event" extends="xPDOSimpleObject">
        <field key="parent" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="family" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />

        <field key="title"                 dbtype="varchar" precision="150"  phptype="string"   null="false" default="" />
        <field key="date"                  dbtype="datetime"                 phptype="datetime" null="true"  default="2022-12-01 00:00:00" />
        <field key="host"                  dbtype="varchar" precision="150"  phptype="string"   null="false" default="" />
        <field key="host_img"              dbtype="varchar" precision="255"  phptype="string"   null="false" default="" />
        <field key="guest"                 dbtype="varchar" precision="150"  phptype="string"   null="false" default="" />
        <field key="guest_img"             dbtype="varchar" precision="255"  phptype="string"   null="false" default="" />
        <field key="general_admission_url" dbtype="varchar" precision="255"  phptype="string"   null="false" default="" />

        <!-- used by MIGX -->        
        <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="0" />
        <field key="pos" dbtype="int" precision="10" phptype="integer" null="false" default="0" />

        <!-- good practice for tracking -->       
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <field key="editedon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="editedby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        
        <index alias="parent" name="parent" primary="false" unique="false" type="BTREE">
            <column key="parent" length="" collation="A" null="false" />
        </index>
        <index alias="family" name="family" primary="false" unique="false" type="BTREE">
            <column key="family" length="" collation="A" null="false" />
        </index>
        <index alias="title" name="title" primary="false" unique="false" type="BTREE">
            <column key="title" length="" collation="A" null="false" />
        </index>
        <index alias="date" name="date" primary="false" unique="false" type="BTREE">
            <column key="date" length="" collation="A" null="false" />
        </index>

        <!-- track createdby and editedby -->
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="EditedBy" class="modUser" local="editedby" foreign="id" cardinality="one" owner="foreign"/>

        <aggregate alias="Family" class="VenueFamily" local="family" foreign="id" cardinality="one" owner="foreign"/>
        <aggregate alias="Parent" class="VenueParent" local="parent" foreign="id" cardinality="one" owner="foreign"/>

        <composite alias="Experiences" class="VenueEventExperience" local="id" foreign="event" cardinality="many" owner="local"/>
    </object>
    
</model>

Related Docs and Forum posts I’ve found are

2

isn’t it the family - id, which is send with the ajax request?

3

With the above configuration it was the text version of the Family name that was sent to connector.php. But you’re correct, it should have been the id.

Looking again the answer is in @snowcreative’s How to learn advanced MIGX capabilities? that keeps showing in my search results.

@halftrainedharry answered the Db-Filters query in there with

For example, if you have a config that displays all items from CompanyStaff and you want to filter them by the column “company_id”, then these settings should work:

  • filter Name: companyfilter
  • Label: Company Filter
  • Empty Text: All companies
  • Filter Type: combobox
  • getlist-where: {"company_id":"[[+companyfilter]]"}
  • getcombo processor: getcombo
  • getcombo textfield: name
  • getcombo idfield: id
  • getcombo classname: CompanyCompany

The processor reads all the rows from CompanyCompany and displays the “name” column in the combobox. When you select a value in the combobox, the setting from getlist-where is used to filter the rows from CompanyStaff.

The configuration required for my Package is

filter Name: getFamilyName
Label: Family Name
Empty Text: --filter by name--
Filter Type: combobox
getlist-where: {"family":"[[+getFamilyName]]"}
getcombo processor: getcombo
getcombo textfield: name
getcombo idfield: family
getcombo classname: VenueEvent
getcombo packageName (optional): venue
getcombo joins (optional): [{“alias”:“Family”}]

getlist-where: {“Family_name”:“[[+getFamilyName]]”} should have been getlist-where: {"family":"[[+getFamilyName]]"} and to set the value of the Ajax request getcombo idfield: family was missing.

I’m struggling to fully understand the purpose of some of the Db-Filters fields and the multiple locations the term family appears through the schema. I was in the habit of prefixing some values with a short identifier eg. rel_id instead of id to help my understanding but was (rightly) encouraged to do it properly.

Thanks again @bruno17.