No foreign key definition for parentClass

70 Posts

jentree Reply #1, 14 years, 10 months ago

Hello,
I know this question has come up numerous times as I have looked through them here. I'll be honest, I'm sure the problem is that I don't fully grasp what is exactly happening.

I am trying to join 2 tables. It is a 1:many relationship. A report can have many animals, but each animal can only belong to a single report.

I have started developing this extra by following the guide in rtfm. I have everything working great - then I wanted to start working on expanding from there.

Here is the error I am getting:

No foreign key definition for parentClass: CitizenReport using relation alias: AnimalsInvolved.report_number=CitizenReport.report_number

My schema looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<model package="citizenreports" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" tablePrefix="ifac_" version="1.1">
    <object class="CitizenReport" table="citizen_reports" extends="xPDOSimpleObject">
        <field key="id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" default=""/>
		<field key="report_number" dbtype="varchar" precision="6" phptype="string" null="false" default=""/>
        <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="phone" dbtype="varchar" precision="14" phptype="string" null="false" default=""/>
        <field key="address" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="email" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="careprovider_name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="farm_name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="careprovider_phone" dbtype="varchar" precision="14" phptype="string" null="false" default="" />
        <field key="careprovider_address" dbtype="varchar" precision="255" 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" />
		
		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
        	<column key="id" length="11" collation="A" null="false" />
    	</index>
   	
        <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" />
        <aggregate alias="AnimalsInvolved" class="AnimalsInvolved" local="report_number" foreign="report_number" cardinality="one" owner="local" />
    </object>
	
	<object class="AnimalsInvolved" table="animals_involved" extends="xPDOSimpleObject">
	    <field key="report_number" dbtype="varchar" precision="6" phptype="string" null="false" default=""/>
		<field key="animal_type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/> 
 				
    	<aggregate alias="CitizenReport" class="CitizenReport" local="report_number" foreign="report_number" cardinality="one" owner="foreign" />   
	</object>	
</model>

I'm not sure how I can write out the SQL MODx thinks I am trying to execute. That might be helpful...dunno.

Here is the code I am using for my join:

$sql = $modx->newQuery('CitizenReport');
	$sql->select('CitizenReport.*');
	$sql->leftJoin('AnimalsInvolved', 'AnimalsInvolved.report_number=CitizenReport.report_number');
	$sql->select('AnimalsInvolved.report_number AS report_number');
	$sql->sortby($sort, $dir);
	
	$citizenreports = $modx->getCollection('CitizenReport',$sql);
	
	error_log( print_r($citizenreports, true) );

Of course the array is empty - and I get the error from modx. Is there another place I need to define the CitizenReport? It is working great until I add the join in so I know its something there.

Thank you for your time! [ed. note: jentree last edited this post 14 years, 10 months ago.]

☆ A M B ☆
756 Posts

CharlesMx Reply #2, 14 years, 10 months ago

The schema XML looks ok, did you manually delete any prior schema class files and rebuild the schema to regenerate the class object files? After making any changes to the XML schema file in order to use those changes you'll have to regenerate the class object files using with the build schema script.

Cheers

Evo Revo // Ubuntu, CentOS, Win // Apache 2x, Lighttp (Lighty)
Visit CharlesMx.com for latest news and status updates.

70 Posts

jentree Reply #3, 14 years, 10 months ago

Thanks Charles,

I probably have deleted the files. I am using the build script that is provided in the "Developing an Extra" guide. http://rtfm.modx.com/display/revolution20/Developing+an+Extra+in+MODX+Revolution

Is that an acceptable method or is there a better way I should generate the files?

Thank you!

☆ A M B ☆
756 Posts

CharlesMx Reply #4, 14 years, 10 months ago

Just noticed I did overlook something in your XML schema definition:

    
<object class="CitizenReport" table="citizen_reports" extends="xPDOSimpleObject">
        <field key="id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" default=""/>

In this case it looks like you are manually defining the ID field, which is auto generated when you set the xPDOSimpleObject. You can either remove that field declaration or change xPDOSimpleObject to just "xPDOObject", add a generated="native" property, and remove the default property.

    <object class="CitizenReport" table="citizen_reports" extends="xPDOObject">
        <field key="id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" generated="native"/>

*Also in the newer version of xPDO you do not need to define the index portion as it is set in the field definition

Final note about the relationship of the AnimalsInvolved, assuming that more than one can be assigned to a given CitizenReport, note the line for the relationship is now a composite and set with a cardinality of many.

Final XPO Schema:

<?xml version="1.0" encoding="UTF-8"?>
<model package="citizenreports" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" tablePrefix="ifac_" version="1.1">
    <object class="CitizenReport" table="citizen_reports" extends="xPDOObject">
        <field key="id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" generated="native"/>
        <field key="report_number" dbtype="varchar" precision="6" phptype="string" null="false" default=""/>
        <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="phone" dbtype="varchar" precision="14" phptype="string" null="false" default=""/>
        <field key="address" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="email" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="careprovider_name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="farm_name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="careprovider_phone" dbtype="varchar" precision="14" phptype="string" null="false" default="" />
        <field key="careprovider_address" dbtype="varchar" precision="255" 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" />
     
        <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" />
        <composite alias="AnimalsInvolved" class="AnimalsInvolved" local="report_number" foreign="report_number" cardinality="many" owner="local" />
    </object>
     
    <object class="AnimalsInvolved" table="animals_involved" extends="xPDOSimpleObject">
        <field key="report_number" dbtype="varchar" precision="6" phptype="string" null="false" default=""/>
        <field key="animal_type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/> 
                 
        <aggregate alias="CitizenReport" class="CitizenReport" local="report_number" foreign="report_number" cardinality="one" owner="foreign" />   
    </object> 
</model>

then in your join you could simplify this a bit:

$sql = $modx->newQuery('CitizenReport');
$sql->leftJoin('AnimalsInvolved', 'AnimalsInvolved');
$sql->select(array('CitizenReport.*','AnimalsInvolved.report_number AS report_number'));
$sql->sortby($sort, $dir);
 
$citizenreports = $modx->getCollection('CitizenReport',$sql);
     
error_log( print_r($citizenreports, true) );

[ed. note: charless last edited this post 14 years, 10 months ago.]

Evo Revo // Ubuntu, CentOS, Win // Apache 2x, Lighttp (Lighty)
Visit CharlesMx.com for latest news and status updates.

☆ A M B ☆
756 Posts

CharlesMx Reply #5, 14 years, 10 months ago

For fun here is a way to return results using the getCollectionGraph:

$c = $modx->getCollectionGraph('CitizenReport', '{"AnimalsInvolved":{}}');
foreach($c as $report){
    
    if(is_object($report)){
        
        echo '<h2>['.$report->get('report_number').'] '.$report->get('name').'</h2>';
        
        if(count($report->getMany('AnimalsInvolved'))){
            echo '<h3>Animals Involved</h3>';
            echo '<ul>';
            foreach($report->getMany('AnimalsInvolved') as $a){
                if(is_object($a)){
                    echo '<li>'.$a->get('animal_type').'</li>';
                }
            }
            echo '</ul>';
        } else {
            echo 'No associated animals found for '.$report->get('name').'<br />';
        }

    }
    
}

Evo Revo // Ubuntu, CentOS, Win // Apache 2x, Lighttp (Lighty)
Visit CharlesMx.com for latest news and status updates.

70 Posts

jentree Reply #6, 14 years, 10 months ago

Quote from: charless at Dec 19, 2011, 11:37 PM

Final note about the relationship of the AnimalsInvolved, assuming that more than one can be assigned to a given CitizenReport, note the line for the relationship is now a composite and set with a cardinality of many.

I think I had used cardinality at one point, but reading through the extra xpdo samples I talked myself out of it. I haven't had "many" yet.

Thank you very much for your time Charles, you have been a huge help. I'm slowly grasping this!

70 Posts

jentree Reply #7, 14 years, 9 months ago

I am getting results Yay!
I wanted to experiment with your last post using getCollectionGraph as that looks way cool.

I am getting an empty array right out of the gates. It seems that I should be getting something back though.

$c = $modx->getCollectionGraph( 'CitizenReport', '{ "AnimalsInvolved":{} }' );

CitizenReport is the main class, AnimalsInvolved is the secondary.

The schema is:

<object class="CitizenReport" table="citizen_reports" extends="xPDOSimpleObject">
<composite alias="AnimalsInvolved" class="AnimalsInvolved" local="report_number" foreign="report_number" cardinality="many" owner="local" />
...

and

<object class="AnimalsInvolved" table="animals_involved" extends="xPDOSimpleObject">
<aggregate alias="CitizenReport" class="CitizenReport" local="report_number" foreign="report_number" cardinality="one" owner="foreign" /> 
...

As a shot in the dark I tried changing both relationships to composite but no luck.
The actual tables are MyISAM, and have no defined relationship. I would need to change to InnoDB and define it.

But, isn't that what the schema file does for me? Defines the relationships? Just kind of confused why I am getting the empty array back. Which, is exactly what it's superposed to if nothing comes back.

70 Posts

jentree Reply #8, 14 years, 9 months ago

Ok, slowly getting somewhere. I'm using this thread as a help to others as well as myself.

Using another thread, I found I wasn't including the actual query - which makes sense as to why I would get zero results back. (duh)

$criteria = $modx->newQuery('CitizenReport');
$criteria->sortby('CitizenReport.name');

$c = $modx->getCollectionGraph( 'CitizenReport', '{ "AnimalsInvolved":{} }', $criteria );
print_r($c);
exit;

I only have 1 record in my db, and I was getting memory errors which I thought was strange. But, looking at the large amount of data that is returned I understand how that could happen.

I would like to really understand what is all in the many arrays & objects that is returned. As well as how to access only the parts I want. I can see now why there is a few threads about minimizing the query. I was expecting just my table results but it looks like there is a lot more in there.

☆ A M B ☆
2,475 Posts

Everett Reply #9, 14 years, 9 months ago

Compare your results from getCollectionGraph to the simpler getCollection, and also try to use the toArray() method on your results -- since a lot of the objects in xPDO are passed by reference, you can end up with a result set that looks a lot bigger than it really is.

E.g. try this:


$reports = $modx->getCollection( 'CitizenReport');
foreach ($reports as $r) {
    print_r($r->toArray());
    exit;
}

Only when you have getCollection working should you jump into the deeper waters of getCollectionGraph. You're doing really well to have documented this stuff in this amount of detail -- it's not easy, but you'll get it figured out.

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

70 Posts

jentree Reply #10, 14 years, 9 months ago

Thanks Everettg_99 for your response. That's really interesting what "toArray()" does to the result set.
Without, its a very large set. Using "toArray()" it only returns the data that I'm after. I've only quickly read the docs about toArray...

Get the values of the object in array format:

So the system is looking at the object I currently have and trimming off everything else? That's how I go from such a huge result set down to only my object?

Pretty cool.