Multiple tables in MIGxDb and Joins.

463 Posts

Tyreal2012 Reply #1, 12 years, 6 months ago

Hi All,

Up to now i've used MIGxDb exclusively with a single table, however i now have a requirement to start building extra tables into the MIGxDb. Whilst i understand the basics of setting up multiple tables in the XML schema, however im not quite sure how to tie the two tables (eventually 4 tables) together or how to differentiate the fields inside the CMP itself (i.e i don't know how to specify the field for table 2 when setting up the form tabs).

My schema is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<model package="vsis_categories" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
    <object class="category_one" table="category_one" extends="xPDOSimpleObject">
	  <field key="id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
        <field key="type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="cat_one_name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="cat_one_description" dbtype="text" phptype="string" null="false" default=""/>
        <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" />
        <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" />  
        <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>
	
	 <object class="category_two" table="category_two" extends="xPDOSimpleObject">
	  <field key="id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
  <field key="type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="cat_two_name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="cat_two_description" dbtype="text" phptype="string" null="false" default=""/>
        <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" />
        <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" />  
        <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>

The type, is the unique key for the two tables and is what the join will be based on, with a Rowboat statement based on the cat_one/two_name.

Does that make sense? =]

thanks

This question has been answered by Bruno17. See the first response.

5,888 Posts

Bruno17 Reply #2, 12 years, 6 months ago

what is the schema of your table, where you want to connect the categories with?

Is there a reason for having a new table for each category-connecction and not one category-table and one table which connects your table with one or many categories?

And what is the reason for having a extra db-schema for this category - tables and have them not together with your other table(s)

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

463 Posts

Tyreal2012 Reply #3, 12 years, 6 months ago

Hi Bruno,

I realised i hadn't provided the full schema (below)

<?xml version="1.0" encoding="UTF-8"?>
<model package="vsis_categories" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
    <object class="category_one" table="category_one" extends="xPDOSimpleObject">
	  <field key="id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
        <field key="type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="cat_one_name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="cat_one_description" dbtype="text" phptype="string" null="false" default=""/>
        <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" />
        <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" />  
        <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>
	
	 <object class="category_two" table="category_two" extends="xPDOSimpleObject">
	  <field key="id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
  <field key="type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="cat_two_name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="cat_two_description" dbtype="text" phptype="string" null="false" default=""/>
        <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" />
        <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" />  
        <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>

 <object class="category_three" table="category_three" extends="xPDOSimpleObject">
	  <field key="id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
  <field key="type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="cat_three_name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="cat_three_description" dbtype="text" phptype="string" null="false" default=""/>
        <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" />
        <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" />  
        <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>

<object class="product_type" table="product_type" extends="xPDOSimpleObject">
	  <field key="id" dbtype="int" precision="10" phptype="integer" null="false" default="0" />
  <field key="type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="product_name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="product_description" dbtype="text" phptype="string" null="false" default=""/>
        <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" />
        <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" />  
        <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>

There are in total 3 categories which relate to a product.

The plan is to provide the product_namee based on the category selections.

So, you pick the first category, which then displays the second category, which then displays the third category, on selection of the 3rd category it then displays all of the product types that match that category - however i thought it sensible to use the 'type' as the item to link all of them together - or am i approaching this incorrectly?

EDIT:

Wait a minute, i've just thought if all i am doing is displaying the product, I dont need to actually join the tables together? I can just have a field in the type database that is identified from the other tables for the individual categories or do i need the links in the tables to enable rowboat or migxloopcollection to read all of the data? [ed. note: Tyreal2012 last edited this post 12 years, 6 months ago.]

5,888 Posts

Bruno17 Reply #4, 12 years, 6 months ago

do you have an example, for what a product-type and its categories is?

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

463 Posts

Tyreal2012 Reply #5, 12 years, 6 months ago

Sure,

The type would be T420

The categories would be:

Category 1 >> Category 2 >> Category 3

Conical Spring Seal >> Inserted Face Head >> Diaphragm Fit Face

discuss.answer

5,888 Posts

Bruno17 Reply #6, 12 years, 6 months ago

How about using MODX - resources to build the category - trees?

and just have a resource_id - field in your product_type - table, which connects to the category (lowest childnode)

you could use some connected dynamic-dropdown-TVs to select the category-nodes and store the lowest selected node to resource_id within a custom update-processor.

Using the MODX - resources-tree for categories would have many benefits

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

463 Posts

Tyreal2012 Reply #7, 12 years, 6 months ago

Ah, that would work but i think i neglected to mention that ALL the data will be coming from an oracle database and imported into MIGxDb

EDIT: actually, that might work...i dont need all the data just the product data.

5,888 Posts

Bruno17 Reply #8, 12 years, 6 months ago

This shouldn't be a big problem importing the categories into the MODX Resource-Tree.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

Answered Multiple tables in MIGxDb and Joins.