MIGXdb grid listbox - display name of related item instead of its id

chris.dempsey78 · October 15, 2022, 3:24pm

Can anyone help me figure how to make MIGX a Column listbox display the friendly name of a linked item instead of its ID on a MIGX CMP page?

There are two database tables: faqs and buckets, each faq is associated with a bucket from the second table.

------------
faqs
----
id
faq_title
faq_content
rel_bucket_id

buckets
----
id
bucket_title
------------

The grid view initially shows rel_bucket_id as its ID number, not the firendly bucket_title name (see screenshot above).

Updating an faq in grid view by double clicking allows the friendly bucket_title name to be selected.

Adding or updating an faq from the popup window shows a listbox with the friendly bucket_title name.

The rel_bucket_id MIGX grid field has Input TV Type set to listbox and input options set to @EVAL which queries the buckets class

@EVAL return 'select==||'.$modx->runSnippet('migxLoopCollection',array('packageName'=>'buckets','classname'=>'bucket','tpl'=>'@CODE:[[+bucket_title]]==[[+id]]','outputSeparator'=>'||','sortConfig'=>'[{"sortby":"bucket_title"}]'));

I know MIGX Columns can have a Custom Renderer. Is this what I need to look at?

The discussion linked below appears to describe the same issue but I don’t follow how to implement a solution.

https://forums.modx.com/thread/100403/how-to-render-id-into-its-listbox-value-in-grid-columns

Can anyone point me in the right direction please?

Thanks,

Chris

bruno17 · October 16, 2022, 7:27am

you can define joins, in the MIGXdb settings tab

halftrainedharry · October 16, 2022, 8:14am

Assuming the alias of the aggregate-relationship in your schema is called “Bucket”

<object class="Faq" table="buckets_faq" extends="xPDOSimpleObject">
	...
	<aggregate alias="Bucket" class="Bucket" local="rel_bucket_id" foreign="id" cardinality="one" owner="foreign" />
</object>

you can set the “Joins” fields in the tab “MIGXdb-Settings” to [{"alias":"Bucket"}]
and then add a new column (Field = Bucket_bucket_title) to show the title.

To show the title in the same column as the rel_bucket_id, you probably need a custom renderer.
Create a file grid.config.inc.php in the directory core/components/buckets/migxconfigs/grid with content like this:

<?php
$renderer['this.myRender'] = "
myRender : function(val, md, rec, row, col, s) {
    return rec.data['Bucket_bucket_title'];
}
";
?>

In the MIGX config → tab “Columns”, set the renderer of the rel_bucket_id column to “this.myRender” (should appear in the dropdown) and set “Show in Grid” of the Bucket_bucket_title column to “No”.

chris.dempsey78 · October 16, 2022, 11:07am

Noted. I actually thought I had examples of this from previous work but in that case it was for a nested MIGXdb grid to assign multiple child Options to parent Products and I don’t have a firm enough grasp of the concepts to apply them here.

Your instruction up to this point worked perfectly, thank you.

I can’t get the custom renderer to work though.

The directory /core/components/buckets didn’t contain subdirectories /migxconfigs/grid so I created them and added file grid.config.inc.php with the $renderer... content you provided.

But it doesn’t appear in the Renderer dropdown for the rel_bucket_id Column.

I cleared /core/cache manually but this had no effect. Any further suggestions please?

Also, I think that once working, the new Renderer will simply display the friendly bucket_title in the Column instead of the rel_bucket_id? This is useful however I would really like if the Bucket could be set via a listbox, directly in the column. Can you help me with the steps to achieve this please?

Thanks.

halftrainedharry · October 16, 2022, 12:35pm

Judging from the screenshot you posted above, haven’t you already a working listbox in your column?
The workaround with the custom renderer I posted is just to display the title (instead of the id) when the column is not in editing mode.

I don’t know why the custom renderer doesn’t work.
Maybe you can post the schema and the JSON of your config?

chris.dempsey78 · October 16, 2022, 1:17pm

I’m unsure, however adding bucket_bucket_title to the Columns does display the friendly bucket name I’m looking for, it’s just not a listbox so can’t be edited inline.

Adding rel_bucket_id in the columns works except it initially displays the id number. Double clicking reveals a listbox with the friendly bucket names (the column Cell Editor is set to this.listboxEditor. Selecting a friendly bucket name updates the id number to that of the corresponding friendly bucket name selected.

The Fields configuration to achieve this for rel_bucket_id is

Field (tab) > Input TV type = listbox
Input Options (tab) > Input Option Values = @EVAL return 'select==||'.$modx->runSnippet('migxLoopCollection',array('packageName'=>'buckets','classname'=>'bucket','tpl'=>'@CODE:[[+bucket_title]]==[[+id]]','outputSeparator'=>'||','sortConfig'=>'[{"sortby":"bucket_title"}]'));

While this works I don’t think it is best practice to use @EVAL when the faqs Package has the relationship defined with

<aggregate alias="bucket" class="bucket" local="rel_bucket_id" foreign="id" cardinality="one" owner="foreign" />

To give you the full detail…

faqs-schema is

<?xml version="1.0" encoding="UTF-8"?>
<model package="faqs" baseClass="xPDOObject" platform="mysql" defaultEngine="INNODB" version="1.1">
    <!-- define faq object
        - prefix tables with package name to group and separate them from modx core tables or tables created by other packages
    -->
    <object class="faq" table="migxdb_faqs" extends="xPDOSimpleObject" >
        <!-- available fields
            - note 'id' is created automatically

            rel_bucket_id text # tv.listbox-multiple of related venue bucket name>id
            faq_title varchar(75)
            faq_content text
            faq_featured text # embedded tv.listbox-multiple of feature areas eg. top 6 on experience detail page
        -->

        <!-- faq object elements -->
        <field key="rel_bucket_id" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
        <field key="faq_title" dbtype="varchar" precision="150" phptype="string" null="false" default="" />
        <field key="faq_content" dbtype="text" phptype="string" null="false" default="" />
        <field key="faq_featured" dbtype="text" 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"/>

        <!-- join the migxdb_faqs table to the migxdb_buckets table which is defined in the bucket class under the buckets package defined in buckets-schema.xml -->
        <aggregate alias="bucket" class="bucket" local="rel_bucket_id" foreign="id" cardinality="one" owner="foreign" />
    </object>
</model>

migxdb_faqs JSON is

{
  "formtabs":[
    {
      "MIGX_id":12,
      "caption":"FAQs",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":69,
          "field":"rel_bucket_id",
          "caption":"rel_bucket_id",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"listbox",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"@EVAL return 'select==||'.$modx->runSnippet('migxLoopCollection',array('packageName'=>'buckets','classname'=>'bucket','tpl'=>'@CODE:[[+bucket_title]]==[[+id]]','outputSeparator'=>'||','sortConfig'=>'[{\"sortby\":\"bucket_title\"}]'));",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        },
        {
          "MIGX_id":70,
          "field":"bucket_bucket_title",
          "caption":"Bucket Title (from bucket_bucket_title)",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"listbox",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":2
        },
        {
          "MIGX_id":62,
          "field":"faq_title",
          "caption":"faq_title",
          "pos":3
        },
        {
          "MIGX_id":63,
          "field":"faq_content",
          "caption":"faq_content",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"richtext",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":4
        },
        {
          "MIGX_id":64,
          "field":"faq_featured",
          "caption":"faq_featured",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"listbox-multiple",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"==||Experience Detail==experience||FAQs List==faqslist",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":5
        },
        {
          "MIGX_id":65,
          "field":"published",
          "caption":"published",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"checkbox",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"yes==1",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":6
        }
      ],
      "pos":1
    }
  ],
  "contextmenus":"update||duplicate||publish||unpublish||remove",
  "actionbuttons":"addItem",
  "columnbuttons":"",
  "filters":"",
  "extended":{
    "migx_add":"",
    "disable_add_item":"",
    "add_items_directly":"",
    "formcaption":"",
    "update_win_title":"",
    "win_id":"",
    "maxRecords":"",
    "addNewItemAt":"bottom",
    "media_source_id":"",
    "multiple_formtabs":"",
    "multiple_formtabs_label":"",
    "multiple_formtabs_field":"",
    "multiple_formtabs_optionstext":"",
    "multiple_formtabs_optionsvalue":"",
    "actionbuttonsperrow":4,
    "winbuttonslist":"",
    "extrahandlers":"",
    "filtersperrow":4,
    "packageName":"faqs",
    "classname":"faq",
    "task":"",
    "getlistsort":"",
    "getlistsortdir":"",
    "sortconfig":"",
    "gridpagesize":"",
    "use_custom_prefix":"0",
    "prefix":"",
    "grid":"",
    "gridload_mode":1,
    "check_resid":1,
    "check_resid_TV":"",
    "join_alias":"",
    "has_jointable":"yes",
    "getlistwhere":"",
    "joins":[
      {
        "alias":"bucket"
      }
    ],
    "hooksnippets":"",
    "cmpmaincaption":"FAQs Main Caption",
    "cmptabcaption":"FAQs Tab Caption ",
    "cmptabdescription":"FAQs Tab Description",
    "cmptabcontroller":"",
    "winbuttons":"",
    "onsubmitsuccess":"",
    "submitparams":""
  },
  "permissions":{
    "apiaccess":"",
    "view":"",
    "list":"",
    "save":"",
    "create":"",
    "remove":"",
    "delete":"",
    "publish":"",
    "unpublish":"",
    "viewdeleted":"",
    "viewunpublished":""
  },
  "fieldpermissions":"",
  "columns":[
    {
      "MIGX_id":6,
      "dataIndex":"id",
      "header":"id"
    },
    {
      "MIGX_id":3,
      "header":"faq_title",
      "dataIndex":"faq_title",
      "width":"",
      "sortable":true,
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":"this.textEditor"
    },
    {
      "MIGX_id":4,
      "header":"published",
      "dataIndex":"published",
      "width":"",
      "sortable":true,
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"this.renderClickCrossTick",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":8,
      "header":"rel_bucket_id",
      "dataIndex":"rel_bucket_id",
      "width":"",
      "sortable":true,
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":"this.listboxEditor"
    },
    {
      "MIGX_id":9,
      "header":"joined bucket title",
      "dataIndex":"bucket_bucket_title",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    }
  ],
  "category":""
}

buckets-schema is

<?xml version="1.0" encoding="UTF-8"?>
<model package="buckets" baseClass="xPDOObject" platform="mysql" defaultEngine="INNODB" version="1.1">
    <!-- define faq object
        - prefix tables with package name to group and separate them from modx core tables or tables created by other packages
    -->
    <object class="bucket" table="migxdb_buckets" extends="xPDOSimpleObject" >
        <!-- available fields
            - note 'id' is created automatically

            bucket_title varchar(75)
        -->

        <!-- event object elements -->
        <field key="bucket_title" dbtype="varchar" precision="75" 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"/>
    </object>
</model>

migxdb_buckets JSON is

{
  "formtabs":[
    {
      "MIGX_id":13,
      "caption":"Buckets",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":67,
          "field":"bucket_title",
          "caption":"bucket_title",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        }
      ],
      "pos":1
    }
  ],
  "contextmenus":"update||duplicate||remove",
  "actionbuttons":"addItem",
  "columnbuttons":"",
  "filters":"",
  "extended":{
    "migx_add":"",
    "disable_add_item":"",
    "add_items_directly":1,
    "formcaption":"",
    "update_win_title":"",
    "win_id":"",
    "maxRecords":"",
    "addNewItemAt":"bottom",
    "media_source_id":"",
    "multiple_formtabs":"",
    "multiple_formtabs_label":"",
    "multiple_formtabs_field":"",
    "multiple_formtabs_optionstext":"",
    "multiple_formtabs_optionsvalue":"",
    "actionbuttonsperrow":4,
    "winbuttonslist":"",
    "extrahandlers":"",
    "filtersperrow":4,
    "packageName":"buckets",
    "classname":"bucket",
    "task":"",
    "getlistsort":"",
    "getlistsortdir":"",
    "sortconfig":"",
    "gridpagesize":"",
    "use_custom_prefix":"0",
    "prefix":"",
    "grid":"",
    "gridload_mode":1,
    "check_resid":1,
    "check_resid_TV":"",
    "join_alias":"",
    "has_jointable":"yes",
    "getlistwhere":"",
    "joins":"",
    "hooksnippets":"",
    "cmpmaincaption":"Bucket Management",
    "cmptabcaption":"Buckets",
    "cmptabdescription":"Bucket description",
    "cmptabcontroller":"",
    "winbuttons":"",
    "onsubmitsuccess":"",
    "submitparams":""
  },
  "permissions":{
    "apiaccess":"",
    "view":"",
    "list":"",
    "save":"",
    "create":"",
    "remove":"",
    "delete":"",
    "publish":"",
    "unpublish":"",
    "viewdeleted":"",
    "viewunpublished":""
  },
  "fieldpermissions":"",
  "columns":[
    {
      "MIGX_id":2,
      "dataIndex":"id",
      "header":"id"
    },
    {
      "MIGX_id":3,
      "header":"bucket_title",
      "dataIndex":"bucket_title",
      "width":"",
      "sortable":true,
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":"this.textEditor"
    },
    {
      "MIGX_id":5,
      "header":"pos",
      "dataIndex":"pos",
      "width":"",
      "sortable":true,
      "show_in_grid":"0",
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    }
  ],
  "category":""
}

chris.dempsey78 · October 16, 2022, 1:24pm

Fore completeness, this is the custom renderer path, filename and content.

halftrainedharry · October 16, 2022, 1:33pm

Is there a reason why you use 2 different schemas for your 2 tables?
The common way is to create 1 schema and then add an <object> to the schema for every database table.

With these two separate schemas you probably have to add the file with the custom renderer to core/components/faqs/migxconfigs/grid instead, to see the renderer in the dropdown.

chris.dempsey78 · October 16, 2022, 2:10pm

There are two schemas for the two tables because buckets are not exclusive to faqs.

There are additional schemas for testimonials, galleries etc. Each of these is a separate table and entries in any of these tables can be assigned to a bucket.

I’m struggling with the database structure on this one because I don’t fully understand the business model yet, my contact is on a two week break and a working draft of this system is meant to be delivered on his return. For example Experiences each have a Gallery but Packages (which are children of Experiences) can also have a Gallery which overrides the parent gallery.

Image below is incomplete and the relationships are incorrect but this might give you an idea of how the current state of the system.

This works - thank you.

halftrainedharry · October 16, 2022, 4:26pm

I would probably still put all these tables into one schema/package.

Also, with such a complex database structure it may be worth the time to create proper Ext.js components and controller/processor files instead of using MIGXdb.

chris.dempsey78 · October 16, 2022, 7:16pm

I’ll get a look at framing it that way for the final version. Appreciate the nudge in that direction.

I’m not sure the database structure needs to be this complex, the current state is a consequence of iterating piece by piece towards the end state which I can’t see clearly at this time due to a mix of both inexperience with migx/xpdo and my client being unavailable to explain their business model in such a way that it can be defined programatically rather than this is how we do it on paper.

Interesting to learn where you think the boundaries of MIGXdb lie.

Your input thus far has been invaluable - thank you.

halftrainedharry · October 17, 2022, 8:20am

I don’t think this can be answered in general. It depends highly on personal preference and programming experience in MODX.

MIGXdb is a great tool to quickly create a custom database table and to manage the data without the need to touch any code. But it also adds more complexity (if something doesn’t work and you have to debug it) and limitations.

If a project gets very complex, I would personally feel more comfortable, having complete control over the code that gets executed. And to have more freedom in how you present the data and how you navigate between different CMPs (or inside the CMP).

chris.dempsey78 · October 17, 2022, 10:36am

I think my earlier comment re. the limits of MODX was poorly worded.

You’re right though, the best approach and tools for the job will always be dependent on experience, circumstance and other factors.

Thinking further on this particular job I have figured a way to reduce the complexity and relationship between the tables. And will think further to make this as future proof as possible. This is Phase One - and I haven’t seen anything about Phase Two yet.