Join 3 tables

265 Posts

thapriest86 Reply #1, 13 years ago

Hi there,

I am trying to JOIN 3 tables as they are related with different services and options etc.
I keep getting errors like these:

[2013-10-08 10:11:43]  [2013-10-08 10:43:24] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: VeraServices using relation alias: VeraSubServices
[2013-10-08 10:43:24] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: VeraServices using relation alias: VeraSubservicesOptions
[2013-10-08 10:43:28] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: VeraServices using relation alias: VeraSubServices
[2013-10-08 10:43:28] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: VeraServices using relation alias: VeraSubservicesOptions
[2013-10-08 10:43:29] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: VeraSubservices using relation alias: VeraServices
[2013-10-08 10:43:29] (ERROR @ /devabc/assets/components/migx/connector.php) Error 42S22 executing statement: 
Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'VeraSubservices.resource_id' in 'where clause'
)

[2013-10-08 10:43:30] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: VeraSubservicesOptions using relation alias: VeraServices
[2013-10-08 10:43:30] (ERROR @ /devabc/assets/components/migx/connector.php) Error 42S22 executing statement: 
Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'VeraSubservicesOptions.resource_id' in 'where clause'
)

My Schema:

<object class="VeraServices" table="vera_services" extends="xPDOSimpleObject">
		<field key="service_type_id" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="code" dbtype="varchar" precision="100" phptype="string" null="false" />
		<field key="name" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="description" dbtype="text" phptype="string" null="false" />
		<field key="sort" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
		<field key="is_optional" dbtype="tinyint" precision="1" phptype="integer" null="false" default="1" />
		<field key="is_default" dbtype="tinyint" precision="1" phptype="integer" null="false" default="0" />
	 <composite alias="VeraSubservices" class="VeraSubservices" local="id" foreign="service_id" cardinality="many" owner="local" />
	 
	</object>
	<object class="VeraSubservices" table="vera_subservices" extends="xPDOSimpleObject">
		<field key="service_id" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="code" dbtype="varchar" precision="10" phptype="string" null="false" />
		<field key="name" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="sort" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
	<composite alias="VeraSubservicesOptions" class="VeraSubservicesOptions" local="id" foreign="subservice_id" cardinality="many" owner="local" />
	<aggregate alias="VeraServices" class="VeraServices" local="service_id" foreign="id" cardinality="one" owner="foreign" />
	</object>
	<object class="VeraSubservicesOptions" table="vera_subservices_options" extends="xPDOSimpleObject">
		<field key="subservice_id" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="code" dbtype="varchar" precision="100" phptype="string" null="false" />
		<field key="name" dbtype="varchar" precision="100" phptype="string" null="false" />
		<field key="cnt_person" dbtype="int" precision="2" attributes="unsigned" phptype="integer" null="false" default="1" />
	<aggregate alias="VeraSubServices" class="VeraSubServices" local="subservice_id" foreign="id" cardinality="one" owner="foreign" />
	</object>

What am i overlooking?

5,888 Posts

Bruno17 Reply #2, 13 years ago

try to set the migxdb-setting 'check Resource' to 'no'

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

265 Posts

thapriest86 Reply #3, 13 years ago

That makes the column error dissappear, but not the foreign key error.

 [2013-10-08 11:31:33] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: Accomodations using relation alias: Countries
[2013-10-08 11:31:34] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: VeraServices using relation alias: VeraSubServices
[2013-10-08 11:31:34] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: VeraServices using relation alias: VeraSubservicesOptions
[2013-10-08 11:31:35] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: Accomodations using relation alias: Countries

[ed. note: thapriest86 last edited this post 13 years ago.]

265 Posts

thapriest86 Reply #4, 13 years ago

Hmm, tried to simplify first for a better unbderstanding of what happens, and treid to join different tables in wich i actaually need one field.

Schema:

<object class="Accomodations" table="accomodations" extends="xPDOSimpleObject">
		<field key="code" dbtype="varchar" precision="100" phptype="string" null="false" />
		<field key="land_id" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="alias" dbtype="varchar" precision="250" phptype="string" null="false" />
		<field key="naam" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="plaats" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="intro" dbtype="varchar" precision="250" phptype="string" null="false" />
		<field key="vanafPrijs" dbtype="decimal" precision="9,2" phptype="float" null="false" />
		<field key="numberDays" dbtype="int" precision="10" phptype="integer" null="false" />
		<field key="infoAlgemeen" dbtype="text" phptype="string" null="false" />
		<field key="infoFaciliteiten" dbtype="text" phptype="string" null="false" />
		<field key="infoAccomodatie" dbtype="text" phptype="string" null="false" />
		<field key="klasse" dbtype="decimal" precision="5,1" phptype="float" null="false" />
		<field key="soort" dbtype="varchar" precision="100" phptype="string" null="false" />
		<field key="type" dbtype="varchar" precision="250" phptype="string" null="false" />
		<field key="details" dbtype="text" phptype="string" null="false" />
		<field key="thumbnail" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="active" dbtype="int" precision="1" phptype="integer" null="false" />
		<field key="lastminute" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="aangemaakt" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="gewijzigd" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="reisSoorten" dbtype="text" phptype="string" null="false" />
		<field key="accomodatieTypes" dbtype="text" phptype="string" null="false" />
		<field key="offer_price" dbtype="decimal" precision="9,2" phptype="float" null="false" />
		<field key="video" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="losse_prijs" dbtype="decimal" precision="9,2" phptype="float" null="false" />
		<field key="meta_beschrijving" dbtype="text" phptype="string" null="true" />
		<field key="meta_zoekwoorden" dbtype="varchar" precision="500" phptype="string" null="true" />
		<field key="zoover" dbtype="int" precision="1" phptype="integer" null="true" />
		<field key="dag_prijs" dbtype="decimal" precision="9,2" phptype="float" null="false" />
		<field key="klm" dbtype="tinyint" precision="1" phptype="integer" null="false" />
	<aggregate alias="Countries" class="Countries" local="land_id" foreign="id" cardinality="one" owner="foreign" />
	</object>

	<object class="Countries" table="countries" extends="xPDOSimpleObject">
	<field key="id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" generated="native"/>
		<field key="naam" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="vluchtprijs" dbtype="varchar" precision="5" phptype="string" null="false" />
		<field key="alias" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="image" dbtype="varchar" precision="200" phptype="string" null="false" />
		<field key="showInMenu" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
		<field key="meta_keywords" dbtype="text" phptype="string" null="false" />
		<field key="meta_description" dbtype="text" phptype="string" null="false" />
		<field key="vluchtinformatie" dbtype="text" phptype="string" null="false" />
		<field key="button1_value" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="button1_title" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="button1_content" dbtype="text" phptype="string" null="false" />
		<field key="button2_value" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="button2_title" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="button2_content" dbtype="text" phptype="string" null="false" />
		<field key="button3_value" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="button3_title" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="button3_content" dbtype="text" phptype="string" null="false" />
		<field key="button4_value" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="button4_title" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="button4_content" dbtype="text" phptype="string" null="false" />
		<field key="half_ticket" dbtype="decimal" precision="9,2" phptype="float" null="false" />
		<field key="aangemaakt" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="sort_order" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="gewijzigd" dbtype="int" precision="11" phptype="integer" null="false" />
	<composite alias="Accomodations" class="Accomodations" local="id" foreign="land_id" cardinality="many" owner="local" />
	
	</object>

In wich i simply want the field 'naam' in "Countries" to be displayed in MIGX when i edit or make a new "Accomodations" value. The corresponding field in there is land_id. From what i've read in the docs, this schema should be right, however i'm still getting this error:

[2013-10-09 10:30:31] (ERROR @ /devabc/assets/components/migx/connector.php) No foreign key definition for parentClass: Accomodations using relation alias: Countries

And i have to admit, i just don't fully grasp what is going on when you define this relationship. Can someine point me in the reight direction?

Cheers

5,888 Posts

Bruno17 Reply #5, 13 years ago

Did you run 'Parse schema' after modifying the schema?

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

265 Posts

thapriest86 Reply #6, 13 years ago

I edited the file from the folder, not in MIGX. (opened with notepad++ via TCMD).
The schema file is not loading when i press load schema, though.

I did press parse schema just now, but when the schema doesnt load up, that's not gonna work, is it?

5,888 Posts

Bruno17 Reply #7, 13 years ago

sounds like a file-permission-issue

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

265 Posts

thapriest86 Reply #8, 13 years ago

permissions are set to 0777. It does fetch results, just not the JOIN results.

265 Posts

thapriest86 Reply #9, 13 years ago

Can anyone please confirm if i have my schema setup correctly like above? (i'm kinda new to setting up aliases)
Just for me to know wich direction i must look to resolve, if i have any left.

I even tried upgrading to 2.2.10, still same things happen. (package is not loading up in migx when pressieng "load schema")

175 Posts

stalemate resolution associate Reply #10, 13 years ago

Hi thapriest86,

I'm no xPDO expert, and I thought your question was interesting, so I did a bit of searching and found another post where it was suggested, among other things, to change

xPDOSimpleObject

to

xPDOObject

OR

remove the generated="native" from the id field and keep it as XPDOSimpleObject.

Here is the post where I found out about this:
https://forums.modx.com/thread/?thread=72652&page=1

and here is Sir Bob's write-up on the difference between xPDOObject and xPDOSimpleObject:

http://bobsguides.com/blog.html/2013/06/12/xpdoobject-versus-xpdosimpleobject/

Hope this helps.

Regards,
Tom