I am getting the above error when using getcolloectiongraph caused by line 33 in my snippet,
My snippet is:
<?php
$path = MODX_CORE_PATH . 'components/honours/';
$result = $modx->addPackage('honours',$path .
'model/','agc_');
if (! $result) {
return 'failed to add package';
} else {
$output = '<p>Trophy Results</p>';
}
if (isset($_POST['compid'])) {
$comp_id = intval($_POST['compid']);
/* Get the existing Competition */
$compget = $comp_id;
$c = $modx->newQuery('Compresultagc');
$c->where(array('compid'=>$compget));
$c->sortby('compyear','ASC');
//$comps = $modx->getCollection('Compresultagc',$c);
$comps = $modx->getCollectionGraph('Compresultagc','{"Playerdetails":{}}',$c);
/* Show error message if competition is not found */
if (empty($comps)) {
return ('Could not find comp with ID: ' . $compget);
}
$output .= '<p>Year</p>';
foreach($comps as $comp) {
$output .= '<p> ' . $comp->get('compyear');
$output .= '<p> ' . $comp->get('winner1');
$output .= '<p> ' . $comp->get('winner2');
$output .= ' ' . $comp->Playerdetails->get('fullname') . '<br/></p>';
}
}
return $output;
The schema is:
<?xml version="1.0" encoding="UTF-8"?>
<model package="honours" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
<object class="Playerdetails" table="playerdetails" extends="xPDOSimpleObject">
<field key="id" dbtype="int" phptype="integer" precision="10" null="false" default="" />
<field key="title" dbtype="varchar" phptype="string" precision="10" null="false" default="" />
<field key="initial1" dbtype="varchar" phptype="string" precision="10" null="false" default="" />
<field key="initial2" dbtype="varchar" phptype="string" precision="10" null="false" default="" />
<field key="initial3" dbtype="varchar" phptype="string" precision="10" null="false" default="" />
<field key="surname" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
<field key="fullname" dbtype="varchar" phptype="string" precision="50" null="false" default="" />
<field key="gender" dbtype="varchar" phptype="string" precision="6" null="false" default="" />
<index alias="id" name="id" primary="true" unique="true">
<column key="id" collation="A" null="false" />
</index>
<composite alias="Positionagc" class="Positionagc" local="id" foreign="memberid" cardinality="many"
owner="local"/>
<composite alias="Compresultagc" class="Compresultagc" local="id" foreign="id" cardinality="many"
owner="local"/>
</object>
<object class="Positionagc" table="positionagc" extends="xPDOSimpleObject">
<field key="memberid" dbtype="int" phptype="integer" precision="10" null="false" default="0" />
<field key="position" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
<field key="yearwhen" dbtype="int" phptype="integer" precision="10" null="false" default="0" />
<index alias="memberid" name="memberid" primary="true" unique="true">
<column key="memberid" collation="A" null="false" />
</index>
<aggregate alias="Playerdetails" class="Playerdetails" local="memberid" foreign="id" cardinality="one" owner="foreign" />
</object>
<object class="Competitionagc" table="competitionagc" extends="xPDOSimpleObject">
<field key="id" dbtype="int" phptype="integer" precision="10" null="false" default="" />
<field key="compname" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
<field key="comptype" dbtype="varchar" phptype="string" precision="2" null="false" default="" />
<field key="compnametype" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
<field key="compdesc" dbtype="text" phptype="string" precision="500" null="false" default="0" />
<field key="compimage" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
<index alias="id" name="id" primary="true" unique="true">
<column key="id" collation="A" null="false"/>
</index>
<composite alias="Compresultagc" class="Compresultagc" local="id" foreign="compid" cardinality="many" owner="local" />
</object>
<object class="Compresultagc" table="compresultagc" extends="xPDOSimpleObject">
<field key="id" dbtype="int" phptype="integer" precision="10" null="false" default="" />
<field key="compid" dbtype="int" phptype="integer" precision="10" null="false" default="0q" />
<field key="compyear" dbtype="int" phptype="integer" precision="10" null="false" default="0" />
<field key="winner1" dbtype="int" phptype="integer" precision="10" null="false" default="" />
<field key="winner2" dbtype="int" phptype="integer" precision="10" null="false" default="" />
<index alias="compid" name="compid" primary="true" unique="true">
<column key="compid" collation="A" null="false" />
</index>
<aggregate alias="Competitionagc" class="Competitionagc" local="compid" foreign="id" cardinality="one" owner="foreign" />
<composite alias="Playerdetails" class="Playerdetails" local="compid" foreign="id" cardinality="one" owner="foreign" />
</object>
</model>
Any help would be appreciated.
Try $comp->getOne('Playerdetails')->get('fullname') instead of $comp->Playerdetails->get('fullname').
Maybe also check the relationships in the schema again.local="id" foreign="id") seems wrong:
<composite alias="Compresultagc" class="Compresultagc" local="id" foreign="id" cardinality="many"
owner="local"/>
Thanks halftrainedharry.
markh
December 9, 2021, 5:28pm
4
I’m also wondering if this is right as both relations have the same local compid:
<aggregate alias="Competitionagc" class="Competitionagc" local="compid" foreign="id" cardinality="one" owner="foreign" />
<composite alias="Playerdetails" class="Playerdetails" local="compid" foreign="id" cardinality="one" owner="foreign" />
I have tried the suggestion by halftrainedharry but it made no difference.
It is probably the schema that is wrong and I shall have to explore and understand the relationships better.
You probably have to add a new field “memberid” to “Compresultagc” and change the relationship
<object class="Compresultagc" table="compresultagc" extends="xPDOSimpleObject">
<field key="compid" dbtype="int" phptype="integer" precision="10" null="false" default="0" />
<field key="memberid" dbtype="int" phptype="integer" precision="10" null="false" default="0" />
...
<aggregate alias="Competitionagc" class="Competitionagc" local="compid" foreign="id" cardinality="one" owner="foreign" />
<aggregate alias="Playerdetails" class="Playerdetails" local="memberid" foreign="id" cardinality="one" owner="foreign" />
</object>
and also change the relationship in “Playerdetails”
<object class="Playerdetails" table="playerdetails" extends="xPDOSimpleObject">
...
<composite alias="Compresultagc" class="Compresultagc" local="id" foreign="memberid" cardinality="many" owner="local"/>
</object>
Also, you define a field “id” in most of your objects. This is not necessary. When you extend “xPDOSimpleObject” (extends="xPDOSimpleObject") there is already an “id” column.
<field key="id" dbtype="int" phptype="integer" precision="10" null="false" default="" />
And also take a look at those primary indices .
<object class="Compresultagc" table="compresultagc" extends="xPDOSimpleObject">
<field key="id" dbtype="int" phptype="integer" precision="10" null="false" default="" />
<field key="compid" dbtype="int" phptype="integer" precision="10" null="false" default="0q" />
...
<index alias="compid" name="compid" primary="true" unique="true">
<column key="compid" collation="A" null="false" />
</index>
</object>
When you extend “xPDOSimpleObject” you already have a field “id” with a primary index.
If a “Competitionagc” can have many “Compresultagc” (as the relationship implies), then a unique primary key on the field “compid” doesn’t make sense.
And the same is true for “Positionagc”. If a “Playerdetails” can have many “Positionagc” then a unique primary key on “memberid” is pointless.
Great feedback halftrainedharry.
Perhaps it may be useful to explain what I am aiming to do.
Each competition have will have a result each year and there can be either only one winner or, if a doubles competition, then two winners. In the compresultagc table there are two fields, winner1 and winner2. If the competition is a singles competition then winner1 field holds the player id from the playerdetails table and winner2 field would have the integer 0.
When presenting the list of results for a competition I want to show, for each year, the winners’ fullnames and not the winners’ id’s. I assume I can do this using getcollectiongraph as long as the schema is correct.
Referring to your reply that I may need a memberid field in the compresultagc table, the fields winner1 and winner2 hold the players’ ids in the compresultagc table.
I shall look into the suggestions you have made and try to get the schema sorted.
Once again thanks for the feedback.
In that case, I think the right way would be to add two tables,
A team can have one, two or more teammembers.
Then, in the competition you will have a field with the id of the winner team.
bruno17
December 9, 2021, 8:41pm
10
this all could easyli be listed with (nested) migxLoopCollection - calls. No need for custom snippets, usually.
If you want to keep the current database design, then define two relationships between these two tables:
<object class="Playerdetails" table="playerdetails" extends="xPDOSimpleObject">
<field key="fullname" dbtype="varchar" phptype="string" precision="50" null="false" default="" />
...
<composite alias="CompresultagcWinner1" class="Compresultagc" local="id" foreign="winner1" cardinality="many" owner="local"/>
<composite alias="CompresultagcWinner2" class="Compresultagc" local="id" foreign="winner2" cardinality="many" owner="local"/>
</object>
<object class="Compresultagc" table="compresultagc" extends="xPDOSimpleObject">
<field key="winner1" dbtype="int" phptype="integer" precision="10" null="false" default="0" />
<field key="winner2" dbtype="int" phptype="integer" precision="10" null="false" default="0" />
...
<aggregate alias="PlayerdetailsWinner1" class="Playerdetails" local="winner1" foreign="id" cardinality="one" owner="foreign" />
<aggregate alias="PlayerdetailsWinner2" class="Playerdetails" local="winner2" foreign="id" cardinality="one" owner="foreign" />
</object>
Then in your snippet use this for getCollectionGraph
$comps = $modx->getCollectionGraph('Compresultagc','{"PlayerdetailsWinner1":{},"PlayerdetailsWinner2":{}}',$c);
and when you try to access the fullname of winner2 make sure to check first if one exists.
$winner2 = $comp->getOne('PlayerdetailsWinner2');
if ($winner2){
$output .= $winner2->get('fullname');
}
2 Likes
mpbouch
December 10, 2021, 2:23pm
12
Thanks bruno17 and halftrainedharry.
I have decided to stay with my current table structure and used halftrainedharry’s suggestion and it seems to work fine.
Thanks again to you both for your expert input.