Get posts based multiple category ids

Development
1

Hello everyone,

I have 2 tables ‘modx_medium_categories’ and ‘modx_document_types_categories’. Also I created 3rd table for creating posts and in this table i have 2 columns ‘medium_id’ and ‘document_type_id’. I created the schemas and tables with migxdb and for the posts i use single listbox for medium categories and listbox-multiple for document types categories. These are fine and the values are saving in the database but in the front end i have filter search where you can select medium category and document type category and the posts will appear based on selection.

The problem its when i select document type category the posts are not displayed because i have multiple values in the database. The medium category its fine.

This is my table of posts

Capture

And this is my code for get data when you select medium category or document type category

   `$output = '';
$medium_name = $_POST['medium_name'];
$document_name = $_POST['document_name'];

$channel = empty($_GET["channel"]);

if($channel) {
    if(!empty($medium_name) && !empty($document_name)) {
        
        $sql="SELECT id, name_tr as name FROM modx_posts_lists WHERE medium_id='$medium_name' AND document_type_id='$document_name' AND published=1";
       
        foreach ($modx->query($sql) as $row) {
            $output .=  "<p>".$row["name"]."</p>";
        }
    } elseif(!empty($medium_name) && empty($document_name)) {
        
        $sql="SELECT id, name_tr as name FROM modx_posts_lists WHERE medium_id='$medium_name' AND published=1";
        
        foreach ($modx->query($sql) as $row) {
            $output .=  "<p>".$row["name"]."</p>";
        }
    } elseif(empty($medium_name) && !empty($document_name)) {
    
        $sql="SELECT id, name_tr as name FROM modx_posts_lists WHERE document_type_id='$document_name' AND published=1";
        
        foreach ($modx->query($sql) as $row) {
            $output .=  "<p>".$row["name"]."</p>";
        }
    }
}

echo $output;

return;`

How can i will do this? Please help me

2

The cleanest solution is to add another custom database table. A pivot table for the many-to-many relationship between the posts and the document types. This new table would have the fields post_id and document_type_id.

Take a look at this post for how to do this with migxdb.

3

Thanks halftrainedharry for your reply. If I created new table with fields post_id and document_type_id it would have and medium_id field?

4

The medium_id remains a column of the table modx_posts_lists. (As there is a one-to-many relationship between post and medium, that works fine.)
You delete the column document_type_id in the table modx_posts_lists and replace it with a new table to get rid of the multiple values separated by double pipes (1||2||4) that can’t be queried.

Maybe you can post your schema, if you are unsure how it should be.

5

This is the schema of posts

<?xml version="1.0" encoding="UTF-8"?>
<model package="posts_lists" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.0">
    <object class="posts_List" table="posts_lists" extends="xPDOSimpleObject">
        <field key="name_gr" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="name_en" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="name_tr" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="medium_id" dbtype="int" precision="11" phptype="integer" null="false"/>
        <field key="document_type_id" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="published" dbtype="int" precision="1" phptype="integer" null="false" default="1" attributes="unsigned"/>             
        <aggregate alias="Medium_Category" class="Medium_Category" local="medium_id" foreign="id" cardinality="one" owner="foreign" />
        <aggregate alias="Document_Type_Category" class="Document_Type_Category" local="document_type_id" foreign="id" cardinality="many" owner="foreign" />
    </object>
</model>
6

A schema like this should work: (I hope I didn’t make any mistakes!)

<?xml version="1.0" encoding="UTF-8"?>
<model package="posts_lists" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.0">
    <object class="posts_List" table="posts_lists" extends="xPDOSimpleObject">
        <field key="name_gr" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="name_en" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="name_tr" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="medium_id" dbtype="int" precision="11" phptype="integer" null="false"/>
        <field key="published" dbtype="int" precision="1" phptype="integer" null="false" default="1" attributes="unsigned"/>             
        <aggregate alias="Medium_Category" class="Medium_Category" local="medium_id" foreign="id" cardinality="one" owner="foreign" />
		<composite alias="Document_Type_Categories" class="post_document_type" local="id" foreign="post_id" cardinality="many" owner="local" />
    </object>
	
	<object class="post_document_type" table="post_document_type" extends="xPDOSimpleObject">
		<field key="post_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="document_type_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
		
		<aggregate alias="Post" class="posts_List" local="post_id" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="Document_Type_Category" class="Document_Type_Category" local="document_type_id" foreign="id" cardinality="one" owner="foreign" />
    </object>
	
	<object class="Document_Type_Category" table="document_types_categories" extends="xPDOSimpleObject">
       ...
	   <composite alias="Posts" class="post_document_type" local="id" foreign="document_type_id" cardinality="many" owner="local" />
    </object>	
</model>
7

Is there a reason you went the route of a custom table for posts rather than using resources and the Collections Extra? I try and avoid creating custom tables unless the data really is unique enough to justify the work of additional tables.

Also, you may be more comfortable with SQL, but it looks like everything you’re doing could also be accomplished with either getResources, or pdoTools.

Anyway, just a thought. I like to keep it simple when you can.

-Jared

1 Like
8

@jaredfhealy
It has many advantages to have data within custom tables with proper designed relationships between them. Keeping the site - structure and data-stuff separate is not a bad idea.

9

@bruno17
I definitely agree there are advantages. But it’s also true that there are disadvantages :slight_smile:

I’m just curious as to the specific reasoning since there are multiple factors that go into the decision. We’re only seeing a small piece of the puzzle in a community question so hard to say.

1 Like
10

Be aware that when you use user input directly in an SQL query like this

$medium_name = $_POST['medium_name'];
...
$sql="SELECT id, name_tr as name FROM modx_posts_lists WHERE medium_id='$medium_name' AND published=1";

without escaping or anything, you make your site vulnerable to SQL injections!

11

@halftrainedharry,

I created the schema as you showed me and then i created the snippets “post_aftergetfields” and “post_aftersave” but i have problem in the backend of migxdb. The multiple document types values of the posts are showing but when i select the values are not saving in the database.

This is my CMP of "document_types_categories"

    `{
  "formtabs":[
    {
      "MIGX_id":2,
      "caption":"Info",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":6,
          "field":"name_gr",
          "caption":"Name (Greek Language)",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        },
        {
          "MIGX_id":7,
          "field":"name_en",
          "caption":"Name (English Language)",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":2
        },
        {
          "MIGX_id":8,
          "field":"name_tr",
          "caption":"Name (Turkish Language)",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":3
        },
        {
          "MIGX_id":9,
          "field":"published",
          "caption":"Published",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"checkbox",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"Yes==1",
          "default":1,
          "useDefaultIfEmpty":1,
          "pos":4
        }
      ],
      "pos":1
    }
  ],
  "contextmenus":"update||duplicate||publish||unpublish||remove",
  "actionbuttons":"addItem",
  "columnbuttons":"",
  "filters":"",
  "extended":{
    "migx_add":"Add Document Type",
    "disable_add_item":"",
    "add_items_directly":"",
    "formcaption":"",
    "update_win_title":"",
    "win_id":"document_types_categories",
    "maxRecords":"",
    "addNewItemAt":"top",
    "media_source_id":"",
    "multiple_formtabs":"",
    "multiple_formtabs_label":"",
    "multiple_formtabs_field":"",
    "multiple_formtabs_optionstext":"",
    "multiple_formtabs_optionsvalue":"",
    "actionbuttonsperrow":4,
    "winbuttonslist":"",
    "extrahandlers":"",
    "filtersperrow":4,
    "packageName":"posts_lists",
    "classname":"Document_Type_Category",
    "task":"",
    "getlistsort":"",
    "getlistsortdir":"",
    "sortconfig":"",
    "gridpagesize":25,
    "use_custom_prefix":"0",
    "prefix":"",
    "grid":"",
    "gridload_mode":1,
    "check_resid":1,
    "check_resid_TV":"",
    "join_alias":"",
    "has_jointable":"yes",
    "getlistwhere":"",
    "joins":"",
    "hooksnippets":"",
    "cmpmaincaption":"Document Types",
    "cmptabcaption":"Table",
    "cmptabdescription":"Manage your Document Types here. You can edit them by right-clicking on the respective row.",
    "cmptabcontroller":"",
    "winbuttons":"",
    "onsubmitsuccess":"",
    "submitparams":""
  },
  "columns":[
    {
      "MIGX_id":1,
      "header":"ID",
      "dataIndex":"id",
      "width":"",
      "sortable":true,
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":2,
      "header":"Name GR",
      "dataIndex":"name_gr",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":3,
      "header":"Name EN",
      "dataIndex":"name_en",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":4,
      "header":"Name TR",
      "dataIndex":"name_tr",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":5,
      "header":"Published",
      "dataIndex":"published",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"this.renderCrossTick",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    }
  ],
  "category":""
}`

And this is my CMP of "posts_lists"

    `{
  "formtabs":[
    {
      "MIGX_id":20,
      "caption":"Info",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":101,
          "field":"name_gr",
          "caption":"Name (Greek Language)",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        },
        {
          "MIGX_id":102,
          "field":"name_en",
          "caption":"Name (English Language)",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":2
        },
        {
          "MIGX_id":103,
          "field":"name_tr",
          "caption":"Name (Turkish Language)",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":3
        },
        {
          "MIGX_id":104,
          "field":"medium_id",
          "caption":"Medium Category",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"listbox",
          "validation":"required",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"@EVAL return $modx->runSnippet('migxLoopCollection',array('packageName'=>'medium_categories','classname'=>'Medium_Category','tpl'=>'@CODE:[[+name_gr]]==[[+id]]','outputSeparator'=>'||'));",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":4
        },
        {
          "MIGX_id":105,
          "field":"Document_Type_Categories",
          "caption":"Document Type",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"listbox-multiple",
          "validation":"required",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"@EVAL return $modx->runSnippet('migxLoopCollection',array('packageName'=>'posts_lists','classname'=>'Document_Type_Category','tpl'=>'@CODE:[[+name_gr]]==[[+id]]','outputSeparator'=>'||'));",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":5
        },
        {
          "MIGX_id":106,
          "field":"published",
          "caption":"Published",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"checkbox",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"Yes==1",
          "default":1,
          "useDefaultIfEmpty":1,
          "pos":6
        }
      ],
      "pos":1
    }
  ],
  "contextmenus":"update||duplicate||publish||unpublish||remove",
  "actionbuttons":"addItem",
  "columnbuttons":"",
  "filters":"",
  "extended":{
    "migx_add":"Add Post",
    "disable_add_item":"",
    "add_items_directly":"",
    "formcaption":"",
    "update_win_title":"",
    "win_id":"posts_lists",
    "maxRecords":"",
    "addNewItemAt":"top",
    "media_source_id":"",
    "multiple_formtabs":"",
    "multiple_formtabs_label":"",
    "multiple_formtabs_field":"",
    "multiple_formtabs_optionstext":"",
    "multiple_formtabs_optionsvalue":"",
    "actionbuttonsperrow":4,
    "winbuttonslist":"",
    "extrahandlers":"",
    "filtersperrow":4,
    "packageName":"posts_lists",
    "classname":"posts_List",
    "task":"",
    "getlistsort":"",
    "getlistsortdir":"",
    "sortconfig":"",
    "gridpagesize":25,
    "use_custom_prefix":"0",
    "prefix":"",
    "grid":"",
    "gridload_mode":1,
    "check_resid":1,
    "check_resid_TV":"",
    "join_alias":"",
    "has_jointable":"yes",
    "getlistwhere":"",
    "joins":"",
    "hooksnippets":{
      "aftergetfields":"post_aftergetfields",
      "aftersave":"post_aftersave"
    },
    "cmpmaincaption":"Posts",
    "cmptabcaption":"Table",
    "cmptabdescription":"Manage your Posts here. You can edit them by right-clicking on the respective row.",
    "cmptabcontroller":"",
    "winbuttons":"",
    "onsubmitsuccess":"",
    "submitparams":""
  },
  "columns":[
    {
      "MIGX_id":1,
      "header":"ID",
      "dataIndex":"id",
      "width":"",
      "sortable":true,
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":2,
      "header":"Name GR",
      "dataIndex":"name_gr",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":3,
      "header":"Name EN",
      "dataIndex":"name_en",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":4,
      "header":"Name TR",
      "dataIndex":"name_tr",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":6,
      "header":"Medium",
      "dataIndex":"medium_id",
      "width":"",
      "sortable":1,
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"this.renderChunk",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"[[migxLoopCollection? &packageName=`medium_categories` &classname=`Medium_Category` &tpl=`@CODE:{{+name_gr}}` &where=`{\"id:=\":\"[[+medium_id]]\"}`]]",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":7,
      "header":"Document Type",
      "dataIndex":"Document_Type_Categories",
      "width":"",
      "sortable":1,
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"this.renderChunk",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"[[migxLoopCollection? &packageName=`posts_lists` &classname=`Document_Type_Category` &tpl=`@CODE:{{+name_gr}}` &where=`{\"id:=\":\"[[+Document_Type_Categories]]\"}` &outputSeparator=`||`]]",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":5,
      "header":"Published",
      "dataIndex":"published",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"this.renderCrossTick",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    }
  ],
  "category":""
}`

And these are my snippets

“post_aftergetfields”

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

if ($object) {
    $record_fields = $object->get('record_fields');
    $post_id = $record_fields['object_id'];
    
    $team_ids = array();
    $result = $modx->getCollection('post_document_type', array('post_id' => $post_id));
    foreach($result as $res){
        $team_ids[] = $res->get('document_type_id');
    }
    
    $record_fields['posten'] = implode("||",$team_ids);
    $object->set('record_fields',$record_fields);
}

return '';`

“post_aftersave”

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

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

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

$str_teams = $object->get('posten');
$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('post_document_type');
        $new_t->set('document_type_id',$team_id);
        $new_t->set('post_id ',$post_id );
        $new_t->save();
    }
}

//delete remaining team-ids from the database
foreach($teams_old as $team_id){
    $entry = $modx->getObject('post_document_type',array('post_id ' => $post_id, 'document_type_id' => $team_id));
	$entry->remove();
}`
12

In the snippet “post_aftergetfields” make sure to safe the values to the field-name you used for your listbox-multiple field.

$record_fields['Document_Type_Categories'] = implode("||",$team_ids);
//instead of $record_fields['posten'] = implode("||",$team_ids);

{
	"MIGX_id":105,
	"field":"Document_Type_Categories",
	"caption":"Document Type",
	...
	"inputTVtype":"listbox-multiple",
	...
}

In the snippet “post_aftersave” make the same change.

$str_teams = $object->get('Document_Type_Categories');
//instead of $str_teams = $object->get('posten');

And also there are 3 places where you use 'post_id ' instead of 'post_id'. Make sure to delete the space-character before the closing apostrophe!

Alternatively use this simpler solution that should work too:

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

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

$config = array();
$config['link_classname'] = 'post_document_type';
$config['link_alias'] = 'Document_Type_Categories';
$config['postfield'] = 'Document_Type_Categories';
$config['id_field'] = 'post_id';
$config['link_field'] = 'document_type_id';

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