Filter (combobox) Migx DB CMP based on Value Existing in connected table

Hi All


I have a request which might be difficult to explain, So i have 3 tables in custom tables that all have relationships with each other. All of the CMPs were made in migxdb and work as expected with no issues what so ever.

The tables are structured as follows.

  • A Content table - content
  • A Sectors Table - sectors
  • Connecting table between the two content_sectors
<?xml version="1.0" encoding="UTF-8"?>
<model package="thing" baseClass="xPDOObject" platform="mysql" defaultEngine="InnoDB" phpdoc-package="thing" phpdoc-subpackage="model" version="1.1">

  <object class="content" table="ip_content" extends="xPDOSimpleObject">
    <field key="title" dbtype="varchar" precision="191" phptype="string" null="false" default=""/>
    <field key="type" dbtype="int" precision="10" phptype="integer" null="false" default=""/>
    <field key="deleted" dbtype="int" precision="10" phptype="integer" null="false" default=""/>
    <composite alias="sectors" class="content_sectors" local="id" foreign="content_id" cardinality="many" owner="local"/>

    <object class="content_sectors" table="ip_content_sectors" extends="xPDOSimpleObject">
    <field key="content_id" dbtype="int" precision="10" phptype="integer" null="false" default=""/>
    <field key="sector_id" dbtype="int" precision="10" phptype="integer" null="false" default=""/>
    <field key="deleted" dbtype="int" precision="1" phptype="boolean" null="false" default="0"/>
    <field key="editedon" dbtype="datetime" phptype="datetime" null="false"/>
    <aggregate alias="content_item" class="content" local="content_id" foreign="id" cardinality="one" owner="foreign"/>
    <aggregate alias="sector_item" class="sectors" local="sector_id" foreign="id" cardinality="one" owner="foreign"/>

    <object class="sectors" table="ip_sectors" extends="xPDOSimpleObject">
    <field key="name" dbtype="varchar" precision="50" phptype="string" null="false" default=""/>
    <field key="headimg" dbtype="text" phptype="string" null="true" default=""/>
    <field key="editedon" dbtype="datetime" phptype="datetime" null="false"/>
    <field key="deleted" dbtype="int" precision="10" phptype="integer" null="false" default="0"/>
    <composite alias="content" class="content_sectors" local="id" foreign="sector_id" cardinality="many" owner="local"/>

The Content table can have many sectors associated with it which works as expected in the CMP

This render chunk might clarify better how im displaying the multiple sectors associated with my content

&tpl=`@CODE:<li >[[+sector_item_name]]</li>`


The thing im trying to do now is to create a filter that will filter my content table and only show content in my CMP if a sector exists in the connecting “content_sectors” table for the related content_id

I have the Filter Dropdown already by pulling directly from the sectors table


What i need to do now is configure the getlist-where field in migxdb filter to only show content in the CMP when it finds elements in the connecting table that matches the sector_id that i pass through when picking an item in the filter.

most likely i will need to create a new processor for it in addition to the standard getcombo but any pointers or guidance will be massively appreciated.

If it helps anyone i have this functionality working fine in the rest API i created using the syntax below. In this scenario a user can pass through a comma delimited list of sector ids and it will only show the content for it if it finds that content_id existing in the connecting table

$c->leftJoin("content_sectors", "content_sectors", " = content_sectors.content_id");

if ($sssid = (string)$this->getProperty("sectors"))
            $sectorids = explode(",", $sssid);
            $c->where(["content_sectors.sector_id:IN" => $sectorids, "content_sectors.deleted" => 0]);

Maybe you can make it work with a subquery in the “getlist-where” field of the filter:

EXISTS (SELECT 1 FROM `modx_ip_content_sectors` WHERE content_id = `content`.id AND sector_id = [[+contenttype]])

Otherwise you probably have to write a custom getlist processor.

That Literally worked straight out of the box copy and paste. Looks like i was closer than i thought Just made sure the placeholder was correct [[+sectoritem]] not [[+contenttype]] (that was from my query yesterday so many thanks for that also)

EXISTS (SELECT 1 FROM `modx_ip_content_sectors` WHERE content_id = `content`.id AND sector_id = [[+sectoritem]])

I think my biggest issue was not knowing what structure the getlist-where field takes. I was not sure if it takes straight SQL or a JSON structure.

I had a look at the documentation but it looks like it has never been finished at :frowning:


It works with straight SQL or JSON. If the first character is a curly bracket {, it assumes it’s JSON.

Out of curiosity how would one structure that same query as JSON, im not going to change it because your solution worked well but i would definitely like to know from a JSON perspective how that would be structured for future reference and for anyone else that would come across this in the future

I’m not sure if that is even possible in this case.

I think with pdoResources, you can use something like this &where=`["EXISTS (SELECT 1 FROM ...)"]` (the subquery is a string in an array), but here this wouldn’t be recognized as JSON, because there is no curly bracket {.

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