object graph nodes do not filter by foreign criteria

[spectrum48k]

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

[wshawn]

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.

[spectrum48k]

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:

1. xPDOQuery::bindGraphNode() : foreign criteria not applies (if no local criteria)
2. xPDOQuery::hydrateGraphNode() : using foreach in recursive call makes empty last related object as on screenshot
3. xPDOQuery::hydrateGraphNode() : addOne() overwrites already linked related object (so can not get more than 1 item from array of substRoomTeachers)
4. core/xpdo/xpdo.class.php:1065 log action will be never executed

after apply some of test fixes it seems to works correctly:

5. (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