How to learn advanced MIGX capabilities?

snowcreative · August 3, 2021, 7:44pm

I have trying to develop some CMPs using MIGX DB, and keep hitting up against what seems to be a lack of documentation about using all its features and settings. I really hope at some point a complete user guide for MIGX and MIGX DB gets created. It’s such a wonderful tool, but I’m spend hours trying to find out how to do certain things with it. It would be great to have full details about all the fields, in all 13 of the tabs in the initial MIGX configs screen, about what they are, what they do, and how to use them.

For example, I have a custom package going that uses custom tables. Basic editing works fine; I can set up the CMPs for each of the custom tables, and pull data from joined tables to populate popup values in a second table, for instance. But, I can’t solve the following:

I can’t get the DB filter to work at all, except for a plain old text field. Is that because this is a custom package, or is it because of how the listbox outputs the values?
There doesn’t seem to be a filter option for a listbox that selects only single values. There is only listbox-multiple and combobox.
The listbox doesn’t seem to take input values the same way as TVs, even when it is set to fetch the values from a TV, which outputs a string like “First thing==1||Second thing==2”. Only the stored value (1, 2) shows up in the list, not the labels (First thing, Second thing) like TVs do.
Each MIGX setup seems to let you edit just one class of your custom package. I would really like to do something like this (Company and Staff are separate tables, linked by the company_id in the Staff table):

Is that completely impossible in MIGX? I know I could make the staff table a MIGX TV, but that would store those values in a JSON string, rather than being dynamic links to individual Staff bios. Staff could be in a different formTab, but still that would require inserting new rows in the Staff table while editing the Company table.

Is there a way to have multiple tabs in a single CMP, which link to the configs for each individual table to edit them? I’d like to have this:

With more tabs to the right of “Companies”, like “Staff”, “Locations”, etc.

halftrainedharry · August 3, 2021, 8:24pm

For your 5th question: multiple tabs in a single CMP
In the configuration of the menu, field “Parameters:” add the names of additional configs with double pipes.

&configs=myfirstconfig||mysecondconfig

halftrainedharry · August 3, 2021, 8:36pm

For your 4th question: Nested MIGXdb

You need 2 configs and the relationships in your schema have to be set correctly.

In your config for Company add a field of “Input TV type” = migxdb and “Configs” = name_of_your_staff_config

In your config for Staff in the tab “MIGXdb-Settings”, set “Join Alias” to the alias of the relationship in your schema and “Check Resource” to yes. Maybe also set “Load Grid” to auto.

snowcreative · August 3, 2021, 9:01pm

Sorry, that’s much too easy!

snowcreative · August 3, 2021, 9:02pm

Yes, I have the relations set. I’ll give this a try.

snowcreative · August 3, 2021, 9:39pm

OK, I’ve done that, and the grid shows up, but no related records are shown. In Companies, I have this in the Joins:

[{“alias”:“staff”,“classname”:“CompanyStaff”,“on”:“staff.company_id = CompanyCompany.id”}]

and this in the schema for the Company table:

< composite alias=“staff” class=“CompanyStaff” local=“id” foreign=“company_id” cardinality=“many” owner=“local” />

halftrainedharry · August 3, 2021, 9:50pm

I don’t think you have to fill in anything in the Company config in the field “Joins”.
It should be sufficient just to use “Join Alias” in your Staff config.

<object class="CompanyStaff" table="..." extends="xPDOSimpleObject">
    ...
    <aggregate alias="company" class="..." local="company_id" foreign="id" cardinality="one" owner="foreign" />
</object>

snowcreative · August 3, 2021, 10:09pm

Yeah, I already had that in the schema, but the don’t show up.

halftrainedharry · August 3, 2021, 10:16pm

Can you post the schema for these two tables?
Maybe also the json of the configs (right click → “Export/Import”).

snowcreative · August 3, 2021, 10:21pm

Schema:

<object class="CompanyCompany" table="company_company" extends="xPDOObject">
	<field key="id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" />
	<field key="name" dbtype="varchar" precision="100" phptype="string" null="false" />
	<field key="description" dbtype="varchar" precision="1000" phptype="string" null="true" />
	<field key="updated" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP"  extra="on update current_timestamp" />

	<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
		<column key="id" length="" collation="A" null="false" />
	</index>
	<composite alias="staff" class="CompanyStaff" local="id" foreign="company_id" cardinality="many" owner="local" />

</object>

<object class="CompanyStaff" table="company_staff" extends="xPDOObject">
	<field key="id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" />
	<field key="first_name" dbtype="varchar" precision="100" phptype="string" null="false" />
	<field key="last_name" dbtype="varchar" precision="100" phptype="string" null="false" />
	<field key="company_type_id" dbtype="mediumint" precision="100" phptype="integer" null="false" />
	<field key="company_id" dbtype="int" precision="1000" phptype="integer" null="false" index="companyid" />
	<field key="updated" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP"  extra="on update current_timestamp" />

	<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
		<column key="id" length="" collation="A" null="false" />
	</index>
	<aggregate alias="stafftype" class="CompanyStaffType" local="staff_type_id" foreign="id" cardinality="one" owner="foreign" />
	<aggregate alias="company" class="CompanyCompany" local="company_id" foreign="id" cardinality="one" owner="foreign" />

</object>

snowcreative · August 3, 2021, 10:27pm

Configs for Company. FYI, I’ve tried this both with and without the Joins.

{
  "formtabs":[
    {
      "MIGX_id":2,
      "caption":"Basic Info",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":2,
          "field":"name",
          "caption":"Company Name",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        },
        {
          "MIGX_id":3,
          "field":"description",
          "caption":"Description",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"textarea",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":2
        }
      ],
      "pos":1
    },
    {
      "MIGX_id":5,
      "caption":"Staffs",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":9,
          "field":"stafflist",
          "caption":"Staffs",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"migxdb",
          "validation":"",
          "configs":"Company Staffs",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        }
      ],
      "pos":2
    }
  ],
  "contextmenus":"update||duplicate||remove",
  "actionbuttons":"addItem",
  "columnbuttons":"update||duplicate||remove",
  "filters":"",
  "extended":{
    "migx_add":"Add Company",
    "disable_add_item":"",
    "add_items_directly":"",
    "formcaption":"",
    "update_win_title":"",
    "win_id":"companys",
    "maxRecords":"",
    "addNewItemAt":"bottom",
    "media_source_id":"",
    "multiple_formtabs":"",
    "multiple_formtabs_label":"",
    "multiple_formtabs_field":"",
    "multiple_formtabs_optionstext":"",
    "multiple_formtabs_optionsvalue":"",
    "actionbuttonsperrow":4,
    "winbuttonslist":"cancel||done",
    "extrahandlers":"",
    "filtersperrow":4,
    "packageName":"company",
    "classname":"CompanyCompany",
    "task":"",
    "getlistsort":"",
    "getlistsortdir":"",
    "sortconfig":"",
    "gridpagesize":"",
    "use_custom_prefix":"0",
    "prefix":"",
    "grid":"",
    "gridload_mode":1,
    "check_resid":1,
    "check_resid_TV":"",
    "join_alias":"company",
    "has_jointable":"yes",
    "getlistwhere":"",
    "joins":[
      {
        "alias":"staff",
        "classname":"CompanyStaff",
        "on":"staff.company_id = CompanyCompany.id"
      }
    ],
    "hooksnippets":"",
    "cmpmaincaption":"Companys",
    "cmptabcaption":"Companys",
    "cmptabdescription":"Main list of companys.",
    "cmptabcontroller":"",
    "winbuttons":"",
    "onsubmitsuccess":"",
    "submitparams":""
  },
  "permissions":{
    "apiaccess":"",
    "view":"",
    "list":"",
    "save":"",
    "create":"",
    "remove":"",
    "delete":"",
    "publish":"",
    "unpublish":"",
    "viewdeleted":"",
    "viewunpublished":""
  },
  "fieldpermissions":"",
  "columns":[
    {
      "MIGX_id":1,
      "header":"ID",
      "dataIndex":"id",
      "width":"",
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
		{
      "MIGX_id":2,
      "header":"Name",
      "dataIndex":"name",
      "width":300,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":"this.textEditor"
    },
    {
      "MIGX_id":3,
      "header":"Description",
      "dataIndex":"description",
      "width":300,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":"this.textEditor"
    }
  ],
  "category":""
}

Configs for Staff:

{
  "formtabs":[
    {
      "MIGX_id":4,
      "caption":"Settings",
      "print_before_tabs":"0",
      "fields":[
        {
          "MIGX_id":5,
          "field":"text",
          "caption":"Staff info",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"textarea",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":1
        },
        {
          "MIGX_id":6,
          "field":"staff_type_id",
          "caption":"Staff Type",
          "description":"",
          "description_is_code":"0",
          "inputTV":"",
          "inputTVtype":"listbox",
          "validation":"",
          "configs":"",
          "restrictive_condition":"",
          "display":"",
          "sourceFrom":"config",
          "sources":"",
          "inputOptionValues":"@SELECT `staff_type`,`id` FROM `[[+PREFIX]]company_staff_type` ORDER BY staff_type",
          "default":"",
          "useDefaultIfEmpty":"0",
          "pos":2
        }
      ],
      "pos":1
    }
  ],
  "contextmenus":"update||duplicate||remove",
  "actionbuttons":"addItem",
  "columnbuttons":"update||duplicate||remove",
  "filters":[
    {
      "MIGX_id":1,
      "name":"companylist",
      "label":"Companys",
      "emptytext":"Enter a company name, or part of a name",
      "type":"textbox",
      "getlistwhere":{
        "company.name:LIKE":"%[[+companylist]]%"
      },
      "getcomboprocessor":"",
      "combotextfield":"",
      "comboidfield":"",
      "combowhere":"",
      "comboclassname":"",
      "combopackagename":"",
      "combo_use_custom_prefix":"0",
      "comboprefix":"",
      "combojoins":[
        {
          "alias":"company",
          "classname":"CompanyCompany",
          "on":"company.id = CompanyStaff.company_id"
        }
      ],
      "comboparent":"",
      "default":""
    },
    {
      "MIGX_id":2,
      "name":"clear",
      "label":"Clear",
      "emptytext":"",
      "type":"resetall",
      "getlistwhere":"",
      "getcomboprocessor":"",
      "combotextfield":"",
      "comboidfield":"",
      "combowhere":"",
      "comboclassname":"",
      "combopackagename":"",
      "combo_use_custom_prefix":"0",
      "comboprefix":"",
      "combojoins":"",
      "comboparent":"",
      "default":""
    }
  ],
  "extended":{
    "migx_add":"Add Staff",
    "disable_add_item":"",
    "add_items_directly":"",
    "formcaption":"",
    "update_win_title":"",
    "win_id":"companystaffs",
    "maxRecords":"",
    "addNewItemAt":"bottom",
    "media_source_id":"",
    "multiple_formtabs":"",
    "multiple_formtabs_label":"",
    "multiple_formtabs_field":"",
    "multiple_formtabs_optionstext":"",
    "multiple_formtabs_optionsvalue":"",
    "actionbuttonsperrow":4,
    "winbuttonslist":"cancel||done",
    "extrahandlers":"",
    "filtersperrow":4,
    "packageName":"company",
    "classname":"CompanyStaff",
    "task":"",
    "getlistsort":"",
    "getlistsortdir":"",
    "sortconfig":"",
    "gridpagesize":"",
    "use_custom_prefix":"0",
    "prefix":"",
    "grid":"",
    "gridload_mode":2,
    "check_resid":1,
    "check_resid_TV":"",
    "join_alias":"staff",
    "has_jointable":"yes",
    "getlistwhere":"",
    "joins":[
      {
        "alias":"stafftype",
        "classname":"CompanyStaffType",
        "on":"stafftype.id = CompanyStaff.staff_type_id"
      },
      {
        "alias":"company",
        "classname":"CompanyCompany",
        "on":"company.id = CompanyStaff.company_id"
      }
    ],
    "hooksnippets":"",
    "cmpmaincaption":"Company Staff",
    "cmptabcaption":"Staff",
    "cmptabdescription":"List of company staff.",
    "cmptabcontroller":"",
    "winbuttons":"",
    "onsubmitsuccess":"",
    "submitparams":""
  },
  "permissions":{
    "apiaccess":"",
    "view":"",
    "list":"",
    "save":"",
    "create":"",
    "remove":"",
    "delete":"",
    "publish":"",
    "unpublish":"",
    "viewdeleted":"",
    "viewunpublished":""
  },
  "fieldpermissions":"",
  "columns":[
    {
      "MIGX_id":1,
      "header":"ID",
      "dataIndex":"id",
      "width":60,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":2,
      "header":"Staff",
      "dataIndex":"text",
      "width":400,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":"this.textEditor"
    },
    {
      "MIGX_id":3,
      "header":"Type",
      "dataIndex":"stafftype_staff_type",
      "width":100,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    },
    {
      "MIGX_id":4,
      "header":"Company",
      "dataIndex":"company_name",
      "width":200,
      "sortable":"false",
      "show_in_grid":1,
      "customrenderer":"",
      "renderer":"",
      "clickaction":"",
      "selectorconfig":"",
      "renderchunktpl":"",
      "renderoptions":"",
      "editor":""
    }
  ],
  "category":""
}

halftrainedharry · August 3, 2021, 10:48pm

In your Company config: Delete both the values for “Join Alias” and “Joins”.

In your Staff config: Change the value of “Join Alias” to company! (The name of the alias you would use from the viewpoint of “CompanyStaff” to query the class “CompanyCompany”.)

<object class="CompanyStaff" table="company_staff" extends="xPDOObject">
	...
	<aggregate alias="company" class="CompanyCompany" local="company_id" foreign="id" cardinality="one" owner="foreign" />
</object>

snowcreative · August 3, 2021, 11:12pm

OK, I’m getting the hang of this now. Got it working!

Thanks so much.

halftrainedharry · August 4, 2021, 12:29pm

For your other questions about Db-Filters:
Unfortunately I don’t exactly understand what you are trying to do. Maybe you can be more specific.

To select a single value, you can use “Filter Type” = combobox.
For example, if you have a config that displays all items from CompanyStaff and you want to filter them by the column “company_id”, then these settings should work:

filter Name: companyfilter
Label: Company Filter
Empty Text: All companies
Filter Type: combobox
getlist-where: {"company_id":"[[+companyfilter]]"}
getcombo processor: getcombo
getcombo textfield: name
getcombo idfield: id
getcombo classname: CompanyCompany

The processor reads all the rows from CompanyCompany and displays the “name” column in the combobox. When you select a value in the combobox, the setting from getlist-where is used to filter the rows from CompanyStaff.

If you want to have a fix list for the values in the combobox (and not to query them from the database), you could write a custom processor similar to the getyesnocombo processor.

snowcreative · August 4, 2021, 1:54pm

OK, that works. When I tried combobox before, it actually got displayed as multiple listbox. Something I had entered in one of the other fields must have caused this, so it’s not at all obvious what over-rode the selected input type (combobox). Yes, looks like I need to make a custom processor to create the input text and values.

What is “getcombo where” used for? What is “getcombo joins” used for?

halftrainedharry · August 4, 2021, 2:56pm

With “getcombo where” you could filter the list of companies in the combobox. For example if you had a column “company_country” and only wanted to show the companies from a certain country.

“getcombo joins” could be used to join the database table company_company with additional database-tables to get the values for the combobox.

snowcreative · August 4, 2021, 7:05pm

Thanks again. You’re the best!

snowcreative · August 23, 2021, 8:17pm

I’m noticing this error in the log:

[2021-08-23 19:01:44] (ERROR @ /home/rainbowt/ebnhc/core/xpdo/xpdo.class.php : 1827) No foreign key definition for parentClass: CompanyStaff using relation alias: company

Not sure why that is. The MIGX screens are still working, but yet I get this error when accessing the screen that loads the list of staff in a tab inside the company editing screen. As far as I can see, the foreign key IS set up (see the schema above).