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

nadakbar · February 10, 2022, 12:47pm

Hi All

Context

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>

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

    <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"/>
  </object>
  
</model>

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

[[migxLoopCollection? 
&packageName=`thing`
&classname=`content_sectors`
&joins=`[{"alias":"sector_item"}]`
&sortConfig=`[{"sortby":"sector_item_name","sortdir":"ASC"}]`
&where=`{"content_id":"[[+id]]","deleted":"0"}`
&tpl=`@CODE:<li >[[+sector_item_name]]</li>`
]]

Issue

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.id = 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]);
        }

halftrainedharry · February 10, 2022, 2:34pm

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.

nadakbar · February 10, 2022, 3:35pm

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 http://bruno17.github.io/MIGX/configcmp/#tab-db-filters

halftrainedharry · February 10, 2022, 3:45pm

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

github.com

Bruno17/MIGX/blob/1d5a036c637b4867effe62be981157e8a74224bf/core/components/migx/processors/mgr/default/getlist.php#L157


      
                  if (!empty($filter['getlistwhere'])) {
          
                      $requestvalue = $modx->getOption($filter['name'], $scriptProperties, 'all');
          
                      if (isset($scriptProperties[$filter['name']]) && !empty($requestvalue) && $requestvalue != 'all') {
          
                          $chunk = $modx->newObject('modChunk');
                          $chunk->setCacheable(false);
                          $chunk->setContent($filter['getlistwhere']);
                          $fwhere = $chunk->process($scriptProperties);
                          $fwhere = strpos($fwhere, '{') === 0 ? $modx->fromJson($fwhere) : $fwhere;
                          $c->where($fwhere);
                      }
                  }
              }
          }
          
          if ($modx->migx->checkForConnectedResource($resource_id, $config)) {
          
              if (!empty($joinalias)) {
                  $joinvalue = $resource_id;

nadakbar · February 10, 2022, 3:56pm

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
.

halftrainedharry · February 10, 2022, 5:29pm

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

system · February 12, 2022, 5:29pm

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