MIGX DB: How to display value from other table in column

Development
1

Hi, I created 2 packages with tables from migxdb, the modx_channels_lists and modx_programs_lists with relationship(one channel, many programs). These are the schemas of my two tables:

-modx_channels_lists

   ` <?xml version="1.0" encoding="UTF-8"?>
<model package="channels_lists" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.0">
    <object class="Channel_List" table="channels_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_name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="document_type_name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="published" dbtype="int" precision="1" phptype="integer" null="false" default="1" attributes="unsigned"/>      
    </object>
</model>`

-modx_programs_lists

    `<?xml version="1.0" encoding="UTF-8"?>
<model package="programs_lists" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.0">
    <object class="Program_List" table="programs_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="channel_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"/>      
    </object>
</model>`

In the table modx_programs_lists I have the channel_id column and in the CMP grid for programs you can select the channel from the listbox. In the ‘Input Option Values’ I have the query for showing the values ("@SELECT name_gr,id FROM [[+PREFIX]]channels_lists"). this its ok, its showing the name_gr from the channels table but in the table content when I add new program, in the column ‘channel_id’ is showing the id and I want to display the ‘name_gr’.

I googled for a solution, but I couldn’t find any. I was hoping maybe someone here can help me.

2

I’m afraid, I don’t clearly understand what you are trying to achieve. Maybe you can make it clearer by posting a screenshot or by providing the json of your migx-configuration.

Btw: It is usually advantageous to declare all your tables in 1 package. You can then also define the relationships between the tables in your schema.

3

Capture
Capture1332×673 8.01 KB

In the column “Channel” i want to display the name_gr from the table of channels. I tried to do but nothing, its not working

4

put all objects of your project into one package

<?xml version="1.0" encoding="UTF-8"?>
<model package="channels" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.0">
    <object class="Channel_List" table="channels_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_name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="document_type_name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="published" dbtype="int" precision="1" phptype="integer" null="false" default="1" attributes="unsigned"/> 
        <composite alias="Programs" class="Program_List" local="id" foreign="channel_id" cardinality="many" owner="local" />             
    </object>
    <object class="Program_List" table="programs_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="channel_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="Channel" class="Channel_List" local="channel_id" foreign="id" cardinality="one" owner="foreign" />            
    </object>    
</model>

then in the joins-setting in MIGXdb-settings put

[{"alias":"Channel"}]

then you can get the name in columns with Channel_name_gr

5

and channel_id should be of type int and ideally be indexed

<field key="channel_id" dbtype="int" precision="10" phptype="integer" null="false" default="0" index="index" />
6

Thank you very much Bruno. I will try to do this and I will see if its work.