modxcms
object graph nodes do not filter by foreign criteria
Summary
getObjectGraph() and getCollectionGraph() return results not filtered by foreign criteria and deepest related objects are are lost
Step to reproduce
<object #class="prefixRoom" table="prefix_room" extends="xPDOSimpleObject">
<aggregate alias="substRoomTeacher" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="one"><criteria target="foreign"><![CDATA[{"isstudent":0,"rank:!=":0}]]></criteria></aggregate>
$uid = 1; //some user id
$dataGraph = '{"RoomStudent" : {"Room" : {"substRoomTeacher" : {"Teacher" : {"RoomTeachers" : {}}}}}}';
$result = $modx->getObjectGraph('prefixStudent', $dataGraph, $uid)
->RoomStudent->Room->substRoomTeacher->Teacher;
Observed behavior
substRoomTeacher contains all membership records
Expected behavior
substRoomTeacher should contain one record in accordance to {"isstudent":0,"rank":0}
Environment
MODX revo 2.5.5, MySQL 5.7.18, Apache/2.4.18 (Ubuntu) Server
Your code:
$result = $modx->getObjectGraph('prefixStudent', $dataGraph, $uid) ->RoomStudent->Room->substRoomTeacher->Teacher;
My first thought was that you are attempting to use a compound relation where it may not be supported. You may also have your relationships misdefined. Also the criteria is isstudent = false and rank not 0 (probably greater than 0). Your expected behavior is rank being equal to 0 (or true).
Your prefixRoom object has the relation so you should probably be coming in from prefixRoom->substRoomTeacher which should probably have a cardinality of many due to many substitute teachers possibly teaching in the same room. The would change it to $modx->getCollectionGraph() or $xpdo->getCollectionGraph(). Try the query without relying on the relation, then move on to refining the schema.
I have already built a school board management system with MODX/ xPDO, so I hope this will help you.
OK, I will try to clarify and show more debug info.
schema fragment:
<object class="prefixRoom" table="prefix_room" extends="xPDOSimpleObject">
<!--here some fields, indexes, validation-->
<!--here some other relations-->
<aggregate alias="RoomTeacher" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="one"><criteria target="foreign"><![CDATA[{"isstudent":0}]]></criteria></aggregate>
<aggregate alias="RoomTeachers" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="many"><criteria target="foreign"><![CDATA[{"isstudent":0}]]></criteria></aggregate>
<aggregate alias="RoomStudent" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="one"><criteria target="foreign"><![CDATA[{"isstudent":1}]]></criteria></aggregate>
<aggregate alias="RoomStudents" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="many"><criteria target="foreign"><![CDATA[{"isstudent":1}]]></criteria></aggregate>
<aggregate alias="mainRoomTeacher" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="one"><criteria target="foreign"><![CDATA[{"isstudent":0,"rank":0}]]></criteria></aggregate>
<aggregate alias="substRoomTeacher" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="one"><criteria target="foreign"><![CDATA[{"isstudent":0,"rank:!=":0}]]></criteria></aggregate>
<aggregate alias="substRoomTeachers" class="prefixRoomUser" local="id" foreign="room_id" owner="local" cardinality="many"><criteria target="foreign"><![CDATA[{"isstudent":0,"rank:!=":0}]]></criteria></aggregate>
</object>
simple data fetch by hydrating fields
//just for example
$dataGraph = '{"RoomStudent" : {"Room" : {"substRoomTeacher" : {"Teacher" : {}}}}}';
$result = $modx->getObject($studentClass, 303);
$allRoomTeachers = $result->RoomStudent->Room->RoomTeachers; //then toArray() for each
$mainRoomTeacher = $result->RoomStudent->Room->mainRoomTeacher->toArray();
//here is another opened xPDO issue: If getOne() or getMany() hits the same criteria-hash-named cache file, you will get maximum nesting level error
$modx->cacheManager->refresh(array('db' => array())); //prevent fatal error when cached collection results
$substRoomTeacher = $result->RoomStudent->Room->substRoomTeacher->toArray();
$modx->cacheManager->refresh(array('db' => array())); //prevent fatal error when cached single object result
$substRoomTeachers = $result->RoomStudent->Room->substRoomTeachers; //then toArray() for each
results (it's OK):
optimized data fetch with graphs
debug code fragment (only for substitutes for short):
$substsDataGraph = '{"RoomStudent" : {"Room" : {"substRoomTeachers" : {}}}}';
$substRoomTeachersGraph = $modx->getObjectGraph($studentClass, $substsDataGraph, 303);
results (too many sql result rows and missing deepest related data):
/* observed query -- no additional criteria*/
SELECT
`substRoomTeachers`.`user_id` AS `substRoomTeachers_user_id`,
`substRoomTeachers`.`room_id` AS `substRoomTeachers_room_id`,
`substRoomTeachers`.`rank` AS `substRoomTeachers_rank`,
`substRoomTeachers`.`isstudent` AS `substRoomTeachers_isstudent`
FROM `tp_users` AS `PrefixStudent`
LEFT JOIN `tp_prefix_room_user` `RoomStudent` ON `PrefixStudent`.`id` = `RoomStudent`.`user_id`
LEFT JOIN `tp_prefix_room` `Room` ON `RoomStudent`.`room_id` = `Room`.`id`
LEFT JOIN `tp_prefix_room_user` `substRoomTeachers` ON `Room`.`id` = `substRoomTeachers`.`room_id`
WHERE (`PrefixStudent`.`id` = 303 AND `PrefixStudent`.`class_key` = 'prefixStudent')
ORDER BY `PrefixStudent`.`id` ASC;
/* desired query example -- got it after some test fixes*/
SELECT
`substRoomTeachers`.`user_id` AS `substRoomTeachers_user_id`,
`substRoomTeachers`.`room_id` AS `substRoomTeachers_room_id`,
`substRoomTeachers`.`rank` AS `substRoomTeachers_rank`,
`substRoomTeachers`.`isstudent` AS `substRoomTeachers_isstudent`
FROM `tp_users` AS `PrefixStudent`
LEFT JOIN `tp_prefix_room_user` `RoomStudent` ON (`RoomStudent`.`isstudent` = '1' AND `PrefixStudent`.`id` = `RoomStudent`.`user_id`)
LEFT JOIN `tp_prefix_room` `Room` ON `RoomStudent`.`room_id` = `Room`.`id`
LEFT JOIN `tp_prefix_room_user` `substRoomTeachers`
ON ((`substRoomTeachers`.`isstudent` = '0' AND `substRoomTeachers`.`rank` != '0') AND `Room`.`id` = `substRoomTeachers`.`room_id`)
WHERE (`PrefixStudent`.`id` = 303 AND `PrefixStudent`.`class_key` = 'prefixStudent')
ORDER BY `PrefixStudent`.`id` ASC;
I found at least three problems while I was looking for details:
- xPDOQuery::bindGraphNode() : foreign criteria not applies (if no local criteria)
- xPDOQuery::hydrateGraphNode() : using foreach in recursive call makes empty last related object as on screenshot
- xPDOQuery::hydrateGraphNode() : addOne() overwrites already linked related object (so can not get more than 1 item from array of substRoomTeachers)
- core/xpdo/xpdo.class.php:1065 log action will be never executed
after apply some of test fixes it seems to works correctly:
- (found later) xPDOQuery::hydrateGraphNode()::addMany() "many" relations overwrite because recurcive calls get new instantiated object by _loadInstance(), but not a link to already existing one
I tried to apply more test fixes and it seems I got all the data but since I'm not an expert in xPDO and similar fixes I can not offer as pull requests I would like to discuss with the developers the problems and solutions that I found.
deep nesting of data previews:
for this test example db results are 177columns x 55 rows, 530 hydrateGraphNode() calls and ~500 fromArray() calls. I will try to test some fromArray() optimisation because fromArray() walks through every one field of 177 for every call in this test case
@opengeek do you mind paying attention to my problem and fixing it or showing me the right way please